網際網路‎ > ‎MYSQL‎ > ‎

第一次設定就上手,mysql5.0初設定

張貼者:2012年8月16日 上午12:48Wei-Chang Lin   [ 已更新 2012年8月16日 上午12:48 ]

以下設定實做於Mysql5.0.84

一、my.cnf設定:
1.設定伺服器使用的編碼[mysqld]
#當創設的資料庫沒有指定編碼時,系統預設的編碼
character-set-server=utf8
#校對驗證,校對文字用
collation-server=utf8_unicode_ci
#default這一行預設是註解掉的,因為官方建議改用上面這兩個選項囉
#default-character-set
#設定連線時資料庫連結的編碼,只適用普通user的連線,root存取時此項無效喔!
init-connect='SET NAMES utf8'

*特別說明:character-set-server影響character-set-database,因此該選項同時影響的是:當資料表建立未指定編碼時,會參照此database預設的編碼。

2.設定連線程式使用的編碼[client],例如php連結到此資料庫如果未指定編碼,則依照此參數所設定。
character_set_client=utf8

*再次說明,這個選項之外,很重要的是上面的校對驗證,即character_set_connection這個項目,從client的資料進入後再經過connection轉換,這樣兩層的輸入後至result才做輸出,這就是很多亂碼的由來.......

3.增加上傳檔案的大小,預設值為1MB,所以請在[mysqld]這邊加上一行設定就可以打破這種限制囉!
max_allowed_packet=xxMB

二、檢查資料庫編碼的語法:
1.整個mysql
SHOW VARIABLES LIKE 'character\_set\_%'
2.單一資料庫
SHOW CREATE DATABASE databasename\G
\G要記得加上才會顯示輸出在螢幕
三、導入導出資料庫語法:

1.
mysqldump -u username -pyourpassword --default-character-set=your-coding--opt  databasename > filename.sql
,--default- character-set這個選項建議指明,不然到時導出編碼不合一堆亂碼就出現啦~可先用上面的SHOW CREATE DATABASE這個語法檢查。然後--opt這參數(相當於--add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset合體,太棒了!),加上去除了導入導出更快之外,因為大家使用mysqldump時通常不會關掉服務,本選項也自動加入了 INSERT等語法,因此個人認為會減少很多導入回來的問題,因為我遇過無法導入的問題...囧。


這個設定導出之後,將導出的filename.sql檔案當中關於編碼的部份如SET NAMES,DEFAULT CHARSET之類語句後面=的編碼改成之後導入要用的編碼。對了,另外還要把原本資料庫引擎的編碼TYPE=MyISAM;改為
ENGINE=MyISAM DEFAULT CHARSET=utf8;


最後,記得用iconv,以下是big5檔案轉為utf8的例子:
iconv -f big5 -t utf8 filename.sql > 轉編碼後轉存檔名


如 果不幸有錯誤,可以加個-c的參數忽略過去;iconv這東西好用是好用,不過可惜的是因為大五碼當初許功蓋亂碼的問題,所以轉檔後會出現"\"這一個脫 逸字元(還有本來衝突的字都會多加一個/,以及空行用的與斜線r與斜線n。)請使用好用的文字編輯器把這個錯誤都一一校正,否則導入資料庫還是會有錯誤產 生。

(如果用vi來改,指令很重要,比如說整篇替代的指令為
%s/word1/word2/g             <------直接取代
%s/word1/word2/gc            <------會問要不要取代,比較保險
對了,這裡還有個脫逸字元的問題,在vi當中\要寫兩次才能代表一條,例如說\\r 就相當於常常見到的\r 。)

2.上面的東西都搞定,導入的指令就簡單了,建議直接使用而不要進入mysql的指令行除錯比較方便:
mysql -u 使用者帳號 -p使用者密碼 導入資料庫名稱 < 導入的sql檔名


*導出可以配合一些簡單的參數作為定期排程的備份,例如:
mysqldump -u root -p databasename > /directory/databasename_date +%d%H%M%S.sql
再加入到crontab當中就完成了。

四、建立資料庫時指定編碼:
CREATE DATABASE XXX CHARACTER SET UTF8
五、重要概念:

1.mysql版本5的編碼相關項目有八項
Variable_name: character_set_client
Variable_name: character_set_connection
Variable_name: character_set_database
Variable_name: character_set_filesystem
Variable_name: character_set_results
Variable_name: character_set_server
Variable_name: character_set_system
Variable_name: character_sets_dir

收到query指令連線時先執行character_set_client進行客戶端的輸入,然後執行 character_set_connection轉換後,最後server這邊轉為character_set_results送回客戶端(不一定相 同)輸出。常常見到資料庫.sql檔中SET NAME這一個指令和my.cnf設定裡init-connect='SET NAMES utf8'就是將這三項的編碼做調整。

mysql設定編碼觀念上值得參考的二篇:
(1)internal charset conversion of MySQL
(2)MySQL Big5轉utf8及亂碼問題解決


2.如果連接資料庫的程式本身編寫時編碼支援相容性不確定時,記得到程式當中config.php之類負責連結資料庫的語句後面(就是mysql_connect這東西啦~)那行修改加入

mysql_query("SET character_set_client='utf8'");
mysql_query("SET character_set_connection='utf8'");
mysql_query("SET character_set_results='utf8'");

這東西就是建基於上面所述的概念而來,搞定了資料庫前台後台就都會得到一致的編碼囉。
如果這三個項目編碼都相同,那就可以用簡省的
mysql_query("SET NAMES 'utf8'");
一行搞定。

*做得很棒的資料庫的引擎類別整理表格MySQL 資料庫儲存引擎的選用

與資料庫相關的PHP設定參見SQL-Injection?

有人說不同版本的dump可能會有字元長度的問題,先把答案寫下備查:
而 MySQL 預設遇到長度不足的時候,並不會產生錯誤,而是會捨棄掉不足的部份,只保留可以放入的部份資料,所以才會造成 primary key 重複的問題


參考資料:
1.PHP 連結MySQL資料庫語法以及從程式碼解決中文???亂碼問題
2.釐清phpmyadmin之mysql連線校對正確的設定方式
3.台灣PHP聯盟底下的MySQL5.1手冊
4.經驗分享] MySQL 4.0.x 升級至 MySQL 4.1
5.mysql字符即亂碼

6.一大堆程式都是假的utf8或big5,卻使用latin1格式存資料,爛透了!怎麼辦?
解決辦法請看:mysql從latin1轉utf-8的經驗

http://linuxnews.idv.tw/?p=36
http://mis.mcps.tp.edu.tw/2009/04/30/mysql-big5轉utf8及亂碼問題解決/
http://fanqiang.chinaunix.net/a2/b1/20010514/01280140_b.html
http://ria.richtechmedia.com/2005/12/19/【保証成功】的-mysql-中文亂碼解決方案/

Comments