MySQL中怎樣設置字符集

這期內容當中小編將會給大家帶來有關MySQL中怎樣設置字符集，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

一、內容概述

在 MySQL 的使用過程中，了解字符集、字符序的概念，以及不同設置對數據存儲、比較的影響非常重要。不少同學在日常工作中遇到的 “亂碼” 問題，很有可能就是因為對字符集與字符序的理解不到位、設置錯誤造成的。

本文由淺入深，分別介紹了如下內容：

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. 字符集、字符序的基本概念及聯系

3. MySQL 支持的字符集、字符序設置級，各設置級別之間的聯系

4. server、database、table、column 級字符集、字符序的查看及設置

5. 應該何時設置字符集、字符序

二、字符集、字符序的概念與聯系

在數據的存儲上，MySQL 提供了不同的字符集支持。而在數據的對比操作上，則提供了不同的字符序支持。

MySQL 提供了不同級別的設置，包括 server 級、database 級、table 級、column 級，可以提供非常精準的設置。

什么是字符集、字符序？簡單的來說：

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. 字符集（character set）：定義了字符以及字符的編碼。

3. 字符序（collation）：定義了字符的比較規則。

舉個例子：

有四個字符：A、B、a、b，這四個字符的編碼分別是 A = 0, B = 1, a = 2, b = 3。這里的字符 + 編碼就構成了字符集（character set）。

如果我們想比較兩個字符的大小呢？比如 A、B，或者 a、b，最直觀的比較方式是采用它們的編碼，比如因為 0 < 1，所以 A < B。

另外，對于 A、a，雖然它們編碼不同，但我們覺得大小寫字符應該是相等的，也就是說 A == a。

這上面定義了兩條比較規則，這些比較規則的集合就是 collation。

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. 同樣是大寫字符、小寫字符，則比較他們的編碼大小；

3. 如果兩個字符為大小寫關系，則它們相等。

三、MySQL 支持的字符集、字符序

MySQL 支持多種字符集 與 字符序。

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. 一個字符集對應至少一種字符序（一般是 1 對多）。

3. 兩個不同的字符集不能有相同的字符序。

4. 每個字符集都有默認的字符序。

上面說的比較抽象，我們看下后面幾個小節就知道怎么回事了。

1、查看支持的字符集

可以通過以下方式查看 MYSQL 支持的字符集。

方式一：

mysql> SHOW CHARACTER SET;  +----------+-----------------------------+---------------------+--------+  | Charset  | Description                 | Default collation   | Maxlen |  +----------+-----------------------------+---------------------+--------+  | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |  | dec8     | DEC West European           | dec8_swedish_ci     |      1 |  ...省略

方式二：

mysql> use information_schema;  mysql> select * from CHARACTER_SETS;  +--------------------+----------------------+-----------------------------+--------+  | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                 | MAXLEN |  +--------------------+----------------------+-----------------------------+--------+  | big5               | big5_chinese_ci      | Big5 Traditional Chinese    |      2 |  | dec8               | dec8_swedish_ci      | DEC West European           |      1 |  ...省略

當使用SHOW CHARACTER SET查看時，也可以加上WHERE或LIKE限定條件。

例子一：使用WHERE限定條件。

mysql> SHOW CHARACTER SET WHERE Charset="utf8";  +---------+---------------+-------------------+--------+  | Charset | Description   | Default collation | Maxlen |  +---------+---------------+-------------------+--------+  | utf8    | UTF-8 Unicode | utf8_general_ci   |      3 |  +---------+---------------+-------------------+--------+  1 row in set (0.00 sec)

例子二：使用LIKE限定條件。

mysql> SHOW CHARACTER SET LIKE "utf8%";  +---------+---------------+--------------------+--------+  | Charset | Description   | Default collation  | Maxlen |  +---------+---------------+--------------------+--------+  | utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |  | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |  +---------+---------------+--------------------+--------+  2 rows in set (0.00 sec)

2、查看支持的字符序

類似的，可以通過如下方式查看 MYSQL 支持的字符序。

方式一：通過SHOW COLLATION進行查看。

可以看到，utf8字符集有超過 10 種字符序。通過Default的值是否為Yes，判斷是否默認的字符序。

mysql> SHOW COLLATION WHERE Charset = 'utf8';  +--------------------------+---------+-----+---------+----------+---------+  | Collation                | Charset | Id  | Default | Compiled | Sortlen |  +--------------------------+---------+-----+---------+----------+---------+  | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |  | utf8_bin                 | utf8    |  83 |         | Yes      |       1 |  ...略

方式二：查詢information_schema.COLLATIONS。

mysql> USE information_schema;  mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";  +--------------------------+--------------------+-----+------------+-------------+---------+  | COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |  +--------------------------+--------------------+-----+------------+-------------+---------+  | utf8_general_ci          | utf8               |  33 | Yes        | Yes         |       1 |  | utf8_bin                 | utf8               |  83 |            | Yes         |       1 |  | utf8_unicode_ci          | utf8               | 192 |            | Yes         |       8 |

3、字符序的命名規范

字符序的命名，以其對應的字符集作為前綴，如下所示。比如字符序utf8_general_ci，標明它是字符集utf8的字符序。

更多規則可以參考 官方文檔。

MariaDB [information_schema]> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8" limit 2;   +--------------------+-----------------+  | CHARACTER_SET_NAME | COLLATION_NAME  |  +--------------------+-----------------+  | utf8               | utf8_general_ci |  | utf8               | utf8_bin        |  +--------------------+-----------------+  2 rows in set (0.00 sec)

四、server 的字符集、字符序

用途：當你創建數據庫，且沒有指定字符集、字符序時，server 字符集、server 字符序就會作為該數據庫的默認字符集、排序規則。

如何指定：MySQL 服務啟動時，可通過命令行參數指定。也可以通過配置文件的變量指定。

server 默認字符集、字符序：在 MySQL 編譯的時候，通過編譯參數指定。

character_set_server、collation_ser    ver分別對應 server 字符集、server 字符序。

1、查看 server 字符集、字符序

分別對應character_set_server、collation_server兩個系統變量。

mysql> SHOW VARIABLES LIKE "character_set_server";  mysql> SHOW VARIABLES LIKE "collation_server";

2、啟動服務時指定

可以在 MySQL 服務啟動時，指定 server 字符集、字符序。如不指定，默認的字符序分別為latin1、latin1_swedish_ci

mysqld --character-set-server=latin1 \        --collation-server=latin1_swedish_ci

單獨指定 server 字符集，此時，server 字符序為latin1的默認字符序

latin1_swedish_ci。  mysqld --character-set-server=latin1

3、配置文件指定

除了在命令行參數里指定，也可以在配置文件里指定，如下所示。

[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

用途：指定數據庫級別的字符集、字符序。同一個 MySQL 服務下的數據庫，可以分別指定不同的字符集 / 字符序。

1、設置數據的字符集 / 字符序

可以在創建、修改數據庫的時候，通過CHARACTER SET、COLLATE指定數據庫的字符集、排序規則。

創建數據庫：

CREATE DATABASE db_name    [[DEFAULT] CHARACTER SET charset_name]    [[DEFAULT] COLLATE collation_name]

修改數據庫：

ALTER DATABASE db_name    [[DEFAULT] CHARACTER SET charset_name]    [[DEFAULT] COLLATE collation_name]

例子：創建數據庫test_schema，字符集設置為utf8，此時默認的排序規則為utf8_general_ci。

CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;

2、查看數據庫的字符集 / 字符序

有 3 種方式可以查看數據庫的字符集 / 字符序。

例子一：查看test_schema的字符集、排序規則。（需要切換默認數據庫）

mysql> use test_schema;  Database changed  mysql> SELECT @@character_set_database, @@collation_database;  +--------------------------+----------------------+  | @@character_set_database | @@collation_database |  +--------------------------+----------------------+  | utf8                     | utf8_general_ci      |  +--------------------------+----------------------+  1 row in set (0.00 sec)

例子二：也可以通過下面命令查看test_schema的字符集、數據庫（不需要切換默認數據庫）

mysql> use test_schema;  Database changed  mysql> SELECT @@character_set_database, @@collation_database;  +--------------------------+----------------------+  | @@character_set_database | @@collation_database |  +--------------------------+----------------------+  | utf8                     | utf8_general_ci      |  +--------------------------+----------------------+  1 row in set (0.00 sec)

例子三：也可以通過查看創建數據庫的語句，來查看字符集。

mysql> SHOW CREATE DATABASE test_schema;  +-------------+----------------------------------------------------------------------+  | Database    | Create Database                                                      |  +-------------+----------------------------------------------------------------------+  | test_schema | CREATE DATABASE `test_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |  +-------------+----------------------------------------------------------------------+  1 row in set (0.00 sec)

3、database 字符集、字符序是怎么確定的

創建數據庫時，指定了CHARACTER SET或COLLATE，則以對應的字符集、排序規則為準。

創建數據庫時，如果沒有指定字符集、排序規則，則以character_set_server、collation_server為準。

六、table 的字符集、字符序

創建表、修改表的語法如下，可通過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、創建 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 狀態，注意Collation為utf8_general_ci，對應的字符集為utf8。

MariaDB [blog]> SHOW TABLE STATUS FROM test_schema \G;  *************************** 1. row ***************************           Name: test_table         Engine: InnoDB        Version: 10     Row_format: Compact           Rows: 0  Avg_row_length: 0    Data_length: 16384  Max_data_length: 0   Index_length: 0      Data_free: 11534336  Auto_increment: NULL    Create_time: 2018-01-09 16:10:42    Update_time: NULL     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL  Create_options:         Comment:   1 row in set (0.00 sec)

方式二：查看information_schema.TABLES的信息。

mysql> USE test_schema;  mysql> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";  +-----------------+  | TABLE_COLLATION |  +-----------------+  | utf8_general_ci |  +-----------------+

方式三：通過SHOW CREATE TABLE確認。

mysql> SHOW CREATE TABLE test_table;  +------------+----------------------------------------------------------------------------------------------------------------+  | Table      | Create Table                                                                                                   |  +------------+----------------------------------------------------------------------------------------------------------------+  | test_table | CREATE TABLE `test_table` (  `id` int(11) NOT NULL,  PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  +------------+----------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)

3、table 字符集、字符序如何確定

假設CHARACTER SET、COLLATE的值分別是charset_name、collation_name。如果創建 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關聯的字符集。

charset_name、collation_name均未明確，則采用數據庫的字符集、字符序設置。

七、column 的字符集、排序

類型為 CHAR、VARCHAR、TEXT 的列，可以指定字符集 / 字符序，語法如下：

col_name {CHAR | VARCHAR | TEXT} (col_length)    [CHARACTER SET charset_name]    [COLLATE collation_name]

1、新增 column 并指定字符集 / 排序規則

例子如下：（創建 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 字符集 / 排序規則確定

假設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關聯的字符集。

charset_name、collation_name均未明確，則以 table 的字符集、字符序為準。

八、選擇：何時設置字符集、字符序

一般來說，可以在三個地方進行配置：

創建數據庫的時候進行配置。

mysql server 啟動的時候進行配置。

從源碼編譯 mysql 的時候，通過編譯參數進行配置

1、方式一：創建數據庫的時候進行配置

這種方式比較靈活，也比較保險，它不依賴于默認的字符集 / 字符序。當你創建數據庫的時候指定字符集 / 字符序，后續創建 table、column 的時候，如果不特殊指定，會繼承對應數據庫的字符集 / 字符序。

CREATE DATABASE mydb  DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;

2、方式二：mysql server 啟動的時候進行配置

可以添加以下配置，這樣 mysql server 啟動的時候，會對 character-set-server、collation-server 進行配置。

當你通過 mysql client 創建 database/table/column，且沒有顯示聲明字符集 / 字符序，那么就會用 character-set-server/collation-server 作為默認的字符集 / 字符序。

另外，client、server 連接時的字符集 / 字符序，還是需要通過 SET NAMES 進行設置。

[mysqld]  character-set-server=utf8  collation-server=utf8_general_ci

3、方式三：從源碼編譯 mysql 的時候，通過編譯參數進行設置

編譯的時候如果指定了-DDEFAULT_CHARSET和-DDEFAULT_COLLATION，那么：

創建 database、table 時，會將其作為默認的字符集 / 字符序。

client 連接 server 時，會將其作為默認的字符集 / 字符序。（不用單獨 SET NAMES）

shell> cmake . -DDEFAULT_CHARSET=utf8 \           -DDEFAULT_COLLATION=utf8_general_ci

上述就是小編為大家分享的MySQL中怎樣設置字符集了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。