MySQL字元集詳解

MySQL字元集詳解

在資料庫中，字元亂碼屬於常見、多發問題。鑒於本人水平頂多隻能歸於不入流之類，寫這篇文章時內心誠惶誠恐，實在擔心誤導大家。內容僅供參考，若有錯誤，請各位及時指出，我也好學習提高！

MySQL的字元集有4種層級的設定，分別是：伺服器級、資料庫級、表級、欄位級。

一、伺服器級字元集

(1)、可以在my.cnf中設定

[mysqld]default-character-set=gbk (5.1)character-set-server=gbk (5.5)

(2)、可以在啟動選項中設定

mysqld --default-character-set=gbk

(3)、可以在編譯的時候設定

./configure --with-charset=gbk          或cmake . -default-charset=gbk

如果沒有指定伺服器的字元集，預設使用latin1為伺服器的字元集。

(4)、查看當前伺服器的字元集

mysql> show variables like '%char%';+--------------------------+-----------------------------------------+| Variable_name            | Value                                   |+--------------------------+-----------------------------------------+| character_set_client     | latin1                                  || character_set_connection | latin1                                  || character_set_database   | latin1                                  || character_set_filesystem | binary                                  || character_set_results    | latin1                                  || character_set_server     | latin1                                  || character_set_system     | utf8                                    || character_sets_dir       | /usr/local/mysql-5.6.28/share/charsets/ |+--------------------------+-----------------------------------------+

二、資料庫字元集

資料庫的字元集在建立資料庫的時候指定，也可以在建立完資料庫之後通過alter database語句修改。如果資料庫中已經存在資料，修改資料庫字元集並不能將已有的資料按新字元集存放。所以無法通過修改資料庫字元集修改資料的內容。

設定資料庫字元集的規則：

(1)、如果指定了字元集和校對規則，則使用指定的規則；

(2)、如果僅指定字元集而沒有指定校對規則，則使用指定的字元集和預設的校對規則；

(3)、如果沒有指定字元集和校對規則，則使用伺服器的字元集和校對規；

三、表字元集

表的字元集是在建表的時候指定的，可以通過alter table語句進行修改。同樣，對於表中已經存在的資料，修改字元集不會影響原有的記錄，仍將使用原有的字元集。

設定表的字元集的規則同設定資料庫的字元集規則。

四、列字元集

列的字元集和校對規則可以在建表的時候指定，也可以在修改表的時候調整。（這個不常用，僅記錄一下）

五、SET NAMES命令

除了上述的四種字元集外，對實際的應用訪問來說，還存在用戶端和服務端之間互動的字元集，如下：

(1)、character_set_client：用戶端字元集
(2)、character_set_connection：串連字元集
(3)、character_set_resluts：結果字元集

通常情況下，這3個字元集都應該是相同的，才能保證使用者寫入的資料被正確的讀出，特別是對於中文字元。

set names命令則用於同時修改這3個參數的值。

六、關於中文字元集插入的實驗

字元集不一致是導致資料庫內中文內容亂碼的罪魁禍首。

實驗環境：

Server version: 5.6.28 （在此說明實驗環境是由於在學習過程中，從網上參考了部分資料，實驗過程與資料描述稍有出入，未查出原因，只能暫歸結為版本不同所致。）

實驗對象：

mysql> show create table char_test\G *************************** 1. row ***************************       Table: char_testCreate Table: CREATE TABLE `char_test` (  `id` smallint(6) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin11 row in set (0.00 sec)

由上可知，char_test表的字元集是latin1，如果不設定正確的字元集，插入中文字元時，必然會出現如下錯誤：

mysql> insert into char_test (name) values ('小王');ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x8F\xE7\x8E\x8B' for column 'name' at row 1

 

解決方案

(1)、先set names latin1，然後再插入資料。

mysql> set names latin1;Query OK, 0 rows affected (0.00 sec)mysql> insert into char_test (name) values ('小王');Query OK, 1 row affected (0.01 sec)mysql> select * from char_test;+----+--------+| id | name   |+----+--------+|  1 | Tom    ||  2 | 小明   ||  3 | 小王   |+----+--------+3 rows in set (0.00 sec)

(2)、在data.sql檔案中指定set names latin1,然後通過source命令匯入data.sql。

# vi data.sqlset names latin1;insert into char_test (name) values ('小李');mysql> source data.sqlQuery OK, 1 row affected (0.00 sec)mysql> select * from char_test;+----+--------+| id | name   |+----+--------+|  1 | Tom    ||  2 | 小明   ||  3 | 小王   ||  4 | 小李   |+----+--------+4 rows in set (0.00 sec)

(3)、在data.sql檔案中指定set names latin1,然後通過mysql命令匯入

# vi data.sqlset names latin1;insert into char_test (name) values ('小張');# mysql -uroot -p test1 < data.sql# mysql -uroot -p -e "set names latin1;select * from test1.char_test;"

(4)、通過指定mysql命令的字元集參數實現 --default-charset-set=字元集

# vi data.sqlinsert into char_test (name) values ('小張');# 錯誤方法# mysql -uroot -p  test1 < data.sql                      Enter password: ******ERROR 1366 (HY000) at line 1: Incorrect string value: '\xE5\xB0\x8F\xE8\xB5\xB5' for column 'name' at row 1# 正確方法# mysql -uroot -p  --default-character-set=latin1 test1 < data.sql Enter password: ******# mysql -uroot -p -e "set names latin1;select * from test1.char_test;"Enter password: ******+----+--------+| id | name   |+----+--------+|  1 | Tom    ||  2 | 小明   ||  3 | 小王   ||  4 | 小李   ||  5 | 小張   ||  6 | 小趙   |+----+--------+

(5)、在設定檔中指定用戶端的字元集

vi my.cnf[client]default-character-set=latin1mysql> insert into char_test (name) values ('小馬');Query OK, 1 row affected (0.00 sec)mysql> select * from char_test;+----+--------+| id | name   |+----+--------+|  1 | Tom    ||  2 | 小明   ||  3 | 小王   ||  4 | 小李   ||  5 | 小張   ||  6 | 小趙   ||  7 | 小馬   |+----+--------+7 rows in set (0.00 sec)

本文永久更新連結地址：