超詳細的MySQL數(shù)據庫字符集總結，值得收藏
MySQL支持多種字符集(character set)提供用戶存儲數(shù)據，同時允許用不同排序規(guī)則(collation)做比較。下面基于MySQL5.7介紹一下字符集相關變量的使用。
一、字符集、字符序的概念與聯(lián)系
在數(shù)據的存儲上，MySQL提供了不同的字符集支持。而在數(shù)據的對比操作上，則提供了不同的字符序支持。MySQL提供了不同級別的設置，包括server級、database級、table級、column級，可以提供非常精準的設置。
什么是字符集、字符序?簡單的來說：
- 字符集(character set)：定義了字符以及字符的編碼。
- 字符序(collation)：定義了字符的比較規(guī)則。
舉個例子：
有四個字符：A、B、a、b，這四個字符的編碼分別是A = 0, B = 1, a = 2, b = 3。這里的字符 + 編碼就構成了字符集(character set)。
二、MySQL支持的字符集、字符序
MySQL支持多種字符集 與 字符序。
- 一個字符集對應至少一種字符序(一般是1對多)。
- 兩個不同的字符集不能有相同的字符序。
- 每個字符集都有默認的字符序。
1. 查看支持的字符集
可以通過以下方式查看MYSQL支持的字符集。
- SHOW CHARACTER SET;
- select * from information_schema.HARACTER_SETS;
2. 查看支持的字符序
可以通過如下方式查看MYSQL支持的字符序。
- SHOW COLLATION WHERE Charset = 'utf8';
- SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME="utf8";
三、系統(tǒng)的字符集(character_set_system)
character_set_system為元數(shù)據的字符集，即所有的元數(shù)據都使用同一個字符集。試想如果元數(shù)據采用不同字符集，INFORMATION_SCHEMA中的相關信息在不同行之間就很難展示。同時該字符集要能夠支持多種語言，方便不同語言人群使用自己的語言命名database、table、column。MySQL選擇UTF-8作為元數(shù)據編碼，用源碼固定。
查看system字符集：
- select @@global.character_set_system;
因為很少設定，所以就不做指定介紹了。
四、server的字符集、字符序(character_set_server/collation_server)
當create database沒有指定charset/collation就會用character_set_server/collation_server，這兩個變量可以動態(tài)設置，有session/global級別。
在源碼中character_set_server/collation_server實際對應一個變量，因為一個collation對應著一個charset，所以源碼中只記錄CHARSET_INFO結構的collation_server即可。當修改character_set_server，會選擇對應charset的默認collation。對于其他同時有charset和collation的變量，源碼記錄也都是記錄collation。
character_set_server、collation_server分別對應server字符集、server字符序。
1. 查看server字符集、字符序
分別對應character_set_server、collation_server兩個系統(tǒng)變量。
- SET GLOBAL SHOW_COMPATIBILITY_56=ON;
- SHOW VARIABLES LIKE "character_set_server";
- SHOW VARIABLES LIKE "collation_server";
2. 啟動服務時指定
可以在MySQL服務啟動時，指定server字符集、字符序。如不指定，默認的字符序分別為latin1、latin1_swedish_ci
- mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci
3. 配置文件指定
除了在命令行參數(shù)里指定，也可以在配置文件里指定，如下所示。
- [client]
- default-character-set=utf8
- [mysql]
- default-character-set=utf8
- [mysqld]
- collation-server = utf8_unicode_ci
- init-connect='SET NAMES utf8'
- character-set-server = utf8
4. 運行時修改
例子：運行時修改(重啟后會失效，如果想要重啟后保持不變，需要寫進配置文件里)
- mysql> SET character_set_server = utf8 ;
5. 編譯時指定默認字符集、字符序
character_set_server、collation_server的默認值，可以在MySQL編譯時，通過編譯選項指定：
- cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci
6. 實例
通過設置session中不同的character_set_server使創(chuàng)建database的默認charset和collation不同。
- set character_set_server='utf8';
- create database d1;
- select * from information_schema.schemata where SCHEMA_NAME='d1';
- set character_set_server='latin1';
- create database d2;
- select * from SCHEMATA where SCHEMA_NAME='d2';
五、database的字符集、字符序(character_set_database/collation_database)
指定數(shù)據庫級別的字符集、字符序。同一個MySQL服務下的數(shù)據庫，可以分別指定不同的字符集/字符序。該變量值session級別表示當前database的charset/collation，在后面的源碼版本中該變量可能修正為只讀，不建議修改該值。其global級別變量后面也會移除。
1. 設置數(shù)據的字符集/字符序
可以在創(chuàng)建、修改數(shù)據庫的時候，通過CHARACTER SET、COLLATE指定數(shù)據庫的字符集、排序規(guī)則。
-創(chuàng)建數(shù)據庫：
- CREATE DATABASE db_name
- [[DEFAULT] CHARACTER SET charset_name]
- [[DEFAULT] COLLATE collation_name]
修改數(shù)據庫：
- ALTER DATABASE db_name
- [[DEFAULT] CHARACTER SET charset_name]
- [[DEFAULT] COLLATE collation_name]
例子：創(chuàng)建數(shù)據庫test_schema，字符集設置為utf8，此時默認的排序規(guī)則為utf8_general_ci。
- CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;
2. 查看數(shù)據庫的字符集/字符序
有3種方式可以查看數(shù)據庫的字符集/字符序。
查看test_schema的字符集、排序規(guī)則。(需要切換默認數(shù)據庫)：
- mysql> use test_schema;
- mysql> SELECT @@character_set_database, @@collation_database;
查看test_schema的字符集、數(shù)據庫(不需要切換默認數(shù)據庫)：
- mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
- FROM information_schema.SCHEMATA WHERE schema_name="test_schema";
查看創(chuàng)建數(shù)據庫的語句，來查看字符集：
- mysql> SHOW CREATE DATABASE test_schema;
六、table的字符集、字符序
創(chuàng)建表、修改表的語法如下，可通過CHARACTER SET、COLLATE設置字符集、字符序。
- CREATE TABLE tbl_name (column_list)
- [[DEFAULT] CHARACTER SET charset_name]
- [COLLATE collation_name]]
- ALTER TABLE tbl_name
- [[DEFAULT] CHARACTER SET charset_name]
- [COLLATE collation_name]
1. 創(chuàng)建table并指定字符集/字符序
指定字符集為utf8，字符序則采用默認的。
- CREATE TABLE `test_schema`.`test_table` (
- `id` INT NOT NULL COMMENT '',
- PRIMARY KEY (`id`) COMMENT '')
- DEFAULT CHARACTER SET = utf8;
2. 查看table的字符集/字符序
同樣，有3種方式可以查看table的字符集/字符序。
- 方式一：通過SHOW TABLE STATUS查看table狀態(tài)，注意Collation為utf8_general_ci，對應的字符集為utf8。
- SHOW TABLE STATUS FROM test_schema \G;
- USE test_schema;
- SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE
- SHOW CREATE TABLE test_table;
3. table字符集、字符序如何確定
假設CHARACTER SET、COLLATE的值分別是charset_name、collation_name。如果創(chuàng)建table時：
- 明確了charset_name、collation_name，則采用charset_name、collation_name。
- 只明確了charset_name，但collation_name未明確，則字符集采用charset_name，字符序采用charset_name對應的默認字符序。
- 只明確了collation_name，但charset_name未明確，則字符序采用collation_name，字符集采用collation_name關聯(lián)的字符集。
- charset_name、collation_name均未明確，則采用數(shù)據庫的字符集、字符序設置。
七、column的字符集、排序
類型為CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，語法如下：
- col_name {CHAR | VARCHAR | TEXT} (col_length)
- [CHARACTER SET charset_name]
- [COLLATE collation_name]
1. 新增column并指定字符集/排序規(guī)則
例子如下：(創(chuàng)建table類似)
- mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;
2. 查看column的字符集/字符序
- mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";
- +--------------------+-----------------+
- | CHARACTER_SET_NAME | COLLATION_NAME |
- +--------------------+-----------------+
- | utf8 | utf8_general_ci |
- +--------------------+-----------------+
- 1 row in set (0.00 sec)
3. column字符集/排序規(guī)則確定
假設CHARACTER SET、COLLATE的值分別是charset_name、collation_name：
- 如果charset_name、collation_name均明確，則字符集、字符序以charset_name、collation_name為準。
- 只明確了charset_name，collation_name未明確，則字符集為charset_name，字符序為charset_name的默認字符序。
- 只明確了collation_name，charset_name未明確，則字符序為collation_name，字符集為collation_name關聯(lián)的字符集。
- charset_name、collation_name均未明確，則以table的字符集、字符序為準。
