MySQL字符編碼及亂碼解決方案

1、MySQL字符集 MySQL5.1能夠做這些事情： 使用多種字符集來(lái)存儲(chǔ)字符串 使用多種校對(duì)規(guī)則來(lái)比較字符串 在同一臺(tái)服務(wù)器, 同一個(gè)數(shù)據(jù)庫(kù)或甚至在同一個(gè)表中使用不同字符集或校對(duì)規(guī)則來(lái)混合字符串 允許定義任何級(jí)別的字符集和校對(duì)規(guī)則 以下命令顯示mysql支持的字符集: mysql SHOW CHARACTER SET; utf8 (UTF-8 Unicode校對(duì)規(guī)則: mysql SHOW COLLATION LIKE utf8%; 校對(duì)規(guī)則一般有這些特征: 兩個(gè)不同的字符集不能有相同的校對(duì)規(guī)則. 每個(gè)字符集有一個(gè)默認(rèn)校對(duì)規(guī)則. 例如, latin1默認(rèn)校對(duì)規(guī)則是latin1_swedish_

2、ci. 存在校對(duì)規(guī)則命名約定: 它們以其相關(guān)的字符集名開(kāi)始, 通常包括一個(gè)語(yǔ)言名, 并且以_ci(大小寫不敏感, _cs(大小寫敏感或_bin(二元結(jié)束. 字符集和校對(duì)規(guī)則有4個(gè)級(jí)別的默認(rèn)設(shè)置: 服務(wù)器級(jí), 數(shù)據(jù)庫(kù)級(jí), 表級(jí)和連接級(jí). 服務(wù)器級(jí): 1. 設(shè)置不能為空 2. 要么根據(jù)有效的選項(xiàng)設(shè)置來(lái)確定, 要么根據(jù)運(yùn)行時(shí)的設(shè)定值 3. 以上設(shè)置改變character_set_server和collation_server系統(tǒng)變量的值. 數(shù)據(jù)庫(kù)級(jí): 1. 設(shè)置不能為空 2. 確定規(guī)則: 如果指定了CHARACTER SET X和COLLATE Y, 那么采用字符集X和校對(duì)規(guī)則Y. 如果指定了CHA

3、RACTER SET X而沒(méi)有指定COLLATE Y, 那么采用CHARACTER SET X和CHARACTER SET X的默認(rèn)校對(duì)規(guī)則. 否則, 采用服務(wù)器字符集和服務(wù)器校對(duì)規(guī)則. 3. 建議采用如下形式建立數(shù)據(jù)庫(kù), 以保證字符集的一致性 CREATE DATABASE db_name DEFAULT CHARACTER SET charset_name DEFAULT COLLATE collation_name 4. 以上設(shè)置用作character_set_database和collation_database系統(tǒng)變量. 無(wú)論何時(shí)默認(rèn)數(shù)據(jù)庫(kù)更改了, 服務(wù)器都設(shè)置這兩個(gè)變量的值. 如果

4、沒(méi)有默認(rèn)數(shù)據(jù)庫(kù), 這兩個(gè)變量與相應(yīng)的服務(wù)器級(jí)別的變量(character_set_server和collation_server具有相同的值. 表級(jí): 1. 設(shè)置不能為空 2. 確定規(guī)則: 與數(shù)據(jù)庫(kù)級(jí)相同. 3. 建議采用如下形式建表, 以保證字符集的一致性 CREATE TABLE tbl_name (column_list DEFAULT CHARACTER SET charset_name COLLATE collation_name 4. 如果在列定義中沒(méi)有指定列字符集和校對(duì)規(guī)則, 則默認(rèn)使用表字符集和校對(duì)規(guī)則. 表字符集和校對(duì)規(guī)則是MySQL的擴(kuò)展; 在標(biāo)準(zhǔn)SQL中沒(méi)有. 列字符集和

5、校對(duì): 1. 設(shè)置不能為空 2. 確定規(guī)則: 與數(shù)據(jù)庫(kù)級(jí)相同. 3. 建議采用如下形式定義列, 以保證字符集的一致性 col_name CHAR | VARCHAR | TEXT (col_length CHARACTER SET charset_name COLLATE collation_name 連接級(jí): 1. 服務(wù)器字符集和校對(duì)規(guī)則可以用作character_set_server和collation_server變量的值 2. 默認(rèn)數(shù) 據(jù)庫(kù)的字符集和校對(duì)規(guī)則可以用作character_set_database和collation_database變量的值 3. 服務(wù)器使用charact

6、er_set_client變量作為客戶端發(fā)送的查詢中使用的字符集 4. 轉(zhuǎn)換時(shí), 服務(wù)器使用character_set_connection和collation_connection系統(tǒng)變量. 它將客戶端發(fā)送的查詢從character_set_client系統(tǒng)變量轉(zhuǎn)換到character_set_connection. collation_connection對(duì)比較文字字符串是重要的. 5. character_set_results變量指示服務(wù)器返回查詢結(jié)果到客戶端使用的字符集. 包括結(jié)果數(shù)據(jù), 例如列值和結(jié)果元數(shù)據(jù). 使用以下兩個(gè)語(yǔ)句影響連接字符集: SET NAMES charset_n

7、ame SET CHARACTER SET charset_name SET NAMES x語(yǔ)句與這三個(gè)語(yǔ)句等價(jià): mysql SET character_set_client = x; mysql SET character_set_results = x; mysql SET character_set_connection = x; SET CHARACTER SET x語(yǔ)句與這三個(gè)語(yǔ)句等價(jià): mysql SET character_set_client = x; mysql SET character_set_results = x; mysql SET collation_conne

8、ction = collation_database; *在SQL語(yǔ)句中使用COLLATE能夠?yàn)橐粋€(gè)比較覆蓋任何默認(rèn)校對(duì)規(guī)則 *對(duì)于簡(jiǎn)單的函數(shù), 即接收字符串輸入然后返回一個(gè)字符串結(jié)果作為輸出的函數(shù), 輸出的字符集和校對(duì)規(guī)則與原始輸入的相同.(REPLACE(函數(shù)不同于其它函數(shù), 它總是忽略輸入字符串的校對(duì)規(guī)則, 并且進(jìn)行大小寫不敏感的比較 *CONVERT(提供一個(gè)在不同字符集之間轉(zhuǎn)換數(shù)據(jù)的方法. 語(yǔ)法是: CONVERT(expr USING transcoding_name *CAST(函數(shù)將一個(gè)字符串轉(zhuǎn)換到一個(gè)不同的字符集. 語(yǔ)法是: CAST(character_string AS

9、character_data_type CHARACTER SET charset_name mysql SHOW CHARACTER SET LIKE latin%; mysql SHOW COLLATION LIKE latin1%; mysql SHOW CREATE DATABASE test; mysql SHOW CREATE TABLE tbl_name; mysql SHOW FULL COLUMNS FROM person; *utf8_unicode_ci的最主要的特色是支持?jǐn)U展, 即當(dāng)把一個(gè)字母看作與其它字母組合相等時(shí). *utf8_general_ci是一個(gè)遺留的校對(duì)規(guī)

10、則, 不支持?jǐn)U展. 它僅能夠在字符之間進(jìn)行逐個(gè)比較. gb2312(GB2312簡(jiǎn)體中文校對(duì)規(guī)則: gb2312_bin, gb2312_chinese_ci(默認(rèn) gbk(GBK簡(jiǎn)體中文校對(duì)規(guī)則: gbk_bin, gbk_chinese_ci(默認(rèn) (1有效選項(xiàng)設(shè)置: Windows下 在mysql安裝目錄下找到my.ini, 該文件中有兩處這樣的配置: default-character-set=x, 將這兩處改成: default-character-set=y(如utf8, y表示你想要設(shè)置的字符集, 然后重新啟動(dòng)mysql服務(wù)即可. linux下 復(fù)制/usr/share/mysq

11、l/my-f 到 /etc/f, 修改f vi /etc/f 在client下添加default-character-set=utf8 在mysqld下添加default-character-set=utf8 然后重新啟動(dòng)mysql服務(wù)即可: /etc/rc.d/init.d/mysql restart (2運(yùn)行時(shí) 設(shè)定值的方法如下: 運(yùn)行mysqld命令時(shí)按如下格式: mysql -default-character-set=x -u root -p, x是字符集的名字, 如utf8. 亂碼情況: 終極解決方案: 1. 建立數(shù)據(jù)庫(kù)/表和進(jìn)行數(shù)據(jù)庫(kù)操作時(shí)盡量顯式指出使用的字符集, 而不是依賴于

12、MySQL的默認(rèn)設(shè)置, 否則MySQL升級(jí)時(shí)可能帶來(lái)很大困擾 2. 在連接數(shù)據(jù)庫(kù)時(shí)顯式指定字符集, 最好選用utf8 3. JSP頁(yè)面編程和Tomcat設(shè)置時(shí)最好顯式指定字符集, 以保證程序與數(shù)據(jù)庫(kù)的字符集獨(dú)立而不受影響 要點(diǎn): 統(tǒng)一字符集(JSP頁(yè)面編碼, mysql建庫(kù)時(shí)字符集選擇, 連接數(shù)據(jù)庫(kù)URL, request設(shè)定等 思路: 讓服務(wù)器端和客戶端的字符集保持一致 character_set_client 客戶端字符集 character_set_connection 客戶端與服務(wù)器端連接采用的字符集 character_set_results SELECT查詢返回?cái)?shù)據(jù)的字符集 cha

13、racter_set_database 數(shù)據(jù)庫(kù)采用的字符集 也就是說(shuō), 只要保證這幾個(gè)變量采用一致的字符集, 就不會(huì)出現(xiàn)亂碼問(wèn)題了 總結(jié)為以下四點(diǎn): 1. 要保證發(fā)送的數(shù)據(jù)與數(shù)據(jù)庫(kù)的字符集一致, 即 character_set_client, character_set_connection 與character_set_database 一致. 2. 要保證數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)與數(shù)據(jù)庫(kù)編碼一致, 即數(shù)據(jù)的編碼與character_set_database一致. 3. 要保證 SELECT 的返回與程序的編碼一致, 即 character_set_results 與程序(PHP、Java等編碼一

14、致. 4. 要保證程序編碼與瀏覽器編碼一致, 即兩者要一致 分析亂碼的情況 1.寫入數(shù)據(jù)庫(kù)時(shí)作為亂碼寫入 2.查詢結(jié)果以亂碼返回 究竟在發(fā)生亂碼時(shí)是哪一種情況呢? 鍵入如下命令: mysql show variables like character%; +-+-+ | Variable_name | Value | +-+-+ | character_set_client | gb2312 | | character_set_connection | gb2312 | | character_set_database | utf8 | | character_set_filesystem |

15、 binary | | character_set_results | gb2312 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | D:Program FilesMySQLMySQL Server 5.1sharecharsets | +-+-+ 8 rows in set (0.00 sec 可見(jiàn)mysql 數(shù)據(jù)庫(kù)系統(tǒng)中客戶端, 數(shù)據(jù)庫(kù)連接, 數(shù)據(jù)庫(kù), 文件系統(tǒng), 查詢結(jié)果, 服務(wù)器, 系統(tǒng)的字符集設(shè)置. 在 這里, 文件系統(tǒng)字符集是固定的, 系統(tǒng), 服務(wù)器的字

16、符集在安裝時(shí)確定, 與亂碼問(wèn)題無(wú)關(guān) 亂碼的問(wèn)題與客戶端(client, 數(shù)據(jù)庫(kù)連接(connection, 數(shù)據(jù)庫(kù)(database, 查詢結(jié)果(result的字符集設(shè)置有關(guān) 注: 客戶端是看訪問(wèn)mysql 數(shù)據(jù)庫(kù)的方式, 通過(guò)命令行訪問(wèn), 命令行窗口就是客戶端, 通過(guò)JDBC 等連接訪問(wèn), 程序就是客戶端. 我們?cè)谙騧ysql 寫入中文數(shù)據(jù)時(shí), 在客戶端, 數(shù)據(jù)庫(kù)連接, 寫入數(shù)據(jù)庫(kù)時(shí)分別要進(jìn)行編碼轉(zhuǎn)換, 在執(zhí)行查詢時(shí), 在返回結(jié)果, 數(shù)據(jù)庫(kù)連接, 客戶端分別進(jìn)行編碼轉(zhuǎn)換. 現(xiàn)在我們應(yīng)該清楚, 亂碼發(fā)生在數(shù)據(jù)庫(kù), 客戶端, 查詢結(jié)果以及數(shù)據(jù)庫(kù)連接這其中一個(gè)或多個(gè)環(huán)節(jié), 接下來(lái)我們來(lái)解決這個(gè)問(wèn)題

17、. 在登錄數(shù)據(jù)庫(kù)時(shí), 我們用mysql -default-character-set=x -u root -p(x為字符集進(jìn)行連接, 這時(shí)我們?cè)儆胹how variables like %char%命令查看字符集設(shè)置情況, 可以發(fā)現(xiàn)客戶端, 數(shù)據(jù)庫(kù)連接, 查詢結(jié)果的字符集已經(jīng)設(shè)置成登錄時(shí)選擇的字符集了, 如果是已經(jīng)登錄了, 可以使用set names x(x為字符集命令來(lái)實(shí)現(xiàn)上述效果 如果是通過(guò)JDBC 連接數(shù)據(jù)庫(kù), 可以這樣寫URL: URL=jdbc:mysql:/localhost:3306/abs?useUnicode=true&characterEncoding=x x為字符集, J

18、SP頁(yè)面等終端也要設(shè)置相應(yīng)的字符集x MySQL字符集設(shè)置 系統(tǒng)變量： - character_set_server: 默認(rèn)的內(nèi)部操作字符集 - character_set_client: 客戶端來(lái)源數(shù)據(jù)使用的字符集 - character_set_connection: 連接層字符集 - character_set_results: 查詢結(jié)果字符集 - character_set_database: 當(dāng)前選中數(shù)據(jù)庫(kù)的默認(rèn)字符集 - character_set_system: 系統(tǒng)元數(shù)據(jù)(字段名等字符集 - 還有以collation_開(kāi)頭的同上面對(duì)應(yīng)的變量, 用來(lái)描述字符序 MySQL中的字符

19、集轉(zhuǎn)換過(guò)程 1. MySQL Server收到請(qǐng)求時(shí)將請(qǐng)求數(shù)據(jù)從character_set_client轉(zhuǎn)換為character_set_connection; 2. 進(jìn)行內(nèi)部操作前將請(qǐng)求數(shù)據(jù)從character_set_connection轉(zhuǎn)換為內(nèi)部操作字符集, 其確定方法如下: 使用每個(gè)數(shù)據(jù)字段的CHARACTER SET設(shè)定值; 若上述值不存在, 則使用對(duì)應(yīng)數(shù)據(jù)表的DEFAULT CHARACTER SET設(shè)定值(MySQL擴(kuò)展，非SQL標(biāo)準(zhǔn); 若上述值不存在, 則使用對(duì)應(yīng)數(shù)據(jù)庫(kù)的DEFAULT CHARACTER SET設(shè)定值; 若上述值不存在, 則使用character_set_se

20、rver設(shè)定值. 3. 將操作結(jié)果從內(nèi)部操作字符集轉(zhuǎn)換為character_set_results. 編碼轉(zhuǎn)換的規(guī)則就是: 在輸入數(shù)據(jù)時(shí)將編碼由客戶端編碼轉(zhuǎn)換為服務(wù)器端編碼, 在輸出時(shí)將數(shù)據(jù)由服務(wù)器端編碼轉(zhuǎn)換為客戶端編碼 MySQL的字符集處理是這樣的: 1. 發(fā)送請(qǐng)求 (1客戶端發(fā)送請(qǐng)求到服務(wù)器端 (2服務(wù)器端會(huì)把請(qǐng)求的數(shù)據(jù)從客戶端字符集(character_set_client轉(zhuǎn)成服務(wù)器連接字符集(character_set_connection; (3然后服務(wù)器會(huì)檢測(cè)存儲(chǔ)區(qū)域(tabl e, column的字符集, 然后把數(shù)據(jù)從連接字符集(character_set_connection轉(zhuǎn)為存儲(chǔ)區(qū)域(table, column的字符集, 然后再存儲(chǔ)或者查詢; 2. 返回請(qǐng)求 (1服務(wù)器將存儲(chǔ)區(qū)域(table, column的字符集轉(zhuǎn)換成服務(wù)器連接字符集(character_set_connection; (2將服務(wù)器連接字符集(character_set_connection轉(zhuǎn)換成結(jié)果字符集(character_set_resu