MySQL中怎樣設(shè)置字符集

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)MySQL中怎樣設(shè)置字符集，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

一、內(nèi)容概述

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

本文由淺入深，分別介紹了如下內(nèi)容：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 字符集、字符序的基本概念及聯(lián)系

3. MySQL 支持的字符集、字符序設(shè)置級，各設(shè)置級別之間的聯(lián)系

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

5. 應(yīng)該何時設(shè)置字符集、字符序

二、字符集、字符序的概念與聯(lián)系

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

MySQL 提供了不同級別的設(shè)置，包括 server 級、database 級、table 級、column 級，可以提供非常精準(zhǔn)的設(shè)置。

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

舉個例子：

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

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

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

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

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

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

4. 每個字符集都有默認(rèn)的字符序。

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

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 |  ...省略

當(dāng)使用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進(jìn)行查看。

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

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、字符序的命名規(guī)范

字符序的命名，以其對應(yīng)的字符集作為前綴，如下所示。比如字符序utf8_general_ci，標(biāo)明它是字符集utf8的字符序。

更多規(guī)則可以參考 官方文檔。

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 的字符集、字符序

用途：當(dāng)你創(chuàng)建數(shù)據(jù)庫，且沒有指定字符集、字符序時，server 字符集、server 字符序就會作為該數(shù)據(jù)庫的默認(rèn)字符集、排序規(guī)則。

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

server 默認(rèn)字符集、字符序：在 MySQL 編譯的時候，通過編譯參數(shù)指定。

character_set_server、collation_ser    ver分別對應(yīng) server 字符集、server 字符序。

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

分別對應(yīng)character_set_server、collation_server兩個系統(tǒng)變量。

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

2、啟動服務(wù)時指定

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

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

單獨指定 server 字符集，此時，server 字符序為latin1的默認(rèn)字符序

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

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、運行時修改

例子：運行時修改（重啟后會失效，如果想要重啟后保持不變，需要寫進(jìn)配置文件里）

mysql> SET character_set_server = utf8 ;

5、編譯時指定默認(rèn)字符集、字符序

character_set_server、collation_server的默認(rèn)值，可以在 MySQL 編譯時，通過編譯選項指定：

cmake . -DDEFAULT_CHARSET=latin1 \           -DDEFAULT_COLLATION=latin1_german1_ci

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

1、設(shè)置數(shù)據(jù)的字符集 / 字符序

可以在創(chuàng)建、修改數(shù)據(jù)庫的時候，通過CHARACTER SET、COLLATE指定數(shù)據(jù)庫的字符集、排序規(guī)則。

創(chuàng)建數(shù)據(jù)庫：

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

修改數(shù)據(jù)庫：

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

例子：創(chuàng)建數(shù)據(jù)庫test_schema，字符集設(shè)置為utf8，此時默認(rèn)的排序規(guī)則為utf8_general_ci。

CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;

2、查看數(shù)據(jù)庫的字符集 / 字符序

有 3 種方式可以查看數(shù)據(jù)庫的字符集 / 字符序。

例子一：查看test_schema的字符集、排序規(guī)則。（需要切換默認(rèn)數(shù)據(jù)庫）

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的字符集、數(shù)據(jù)庫（不需要切換默認(rèn)數(shù)據(jù)庫）

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)

例子三：也可以通過查看創(chuàng)建數(shù)據(jù)庫的語句，來查看字符集。

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 字符集、字符序是怎么確定的

創(chuàng)建數(shù)據(jù)庫時，指定了CHARACTER SET或COLLATE，則以對應(yīng)的字符集、排序規(guī)則為準(zhǔn)。

創(chuàng)建數(shù)據(jù)庫時，如果沒有指定字符集、排序規(guī)則，則以character_set_server、collation_server為準(zhǔn)。

六、table 的字符集、字符序

創(chuàng)建表、修改表的語法如下，可通過CHARACTER SET、COLLATE設(shè)置字符集、字符序。

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，字符序則采用默認(rèn)的。

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，對應(yīng)的字符集為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確認(rèn)。

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 字符集、字符序如何確定

假設(shè)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對應(yīng)的默認(rèn)字符序。

只明確了collation_name，但charset_name未明確，則字符序采用collation_name，字符集采用collation_name關(guān)聯(lián)的字符集。

charset_name、collation_name均未明確，則采用數(shù)據(jù)庫的字符集、字符序設(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ī)則確定

假設(shè)CHARACTER SET、COLLATE的值分別是charset_name、collation_name：

如果charset_name、collation_name均明確，則字符集、字符序以charset_name、collation_name為準(zhǔn)。

只明確了charset_name，collation_name未明確，則字符集為charset_name，字符序為charset_name的默認(rèn)字符序。

只明確了collation_name，charset_name未明確，則字符序為collation_name，字符集為collation_name關(guān)聯(lián)的字符集。

charset_name、collation_name均未明確，則以 table 的字符集、字符序為準(zhǔn)。

八、選擇：何時設(shè)置字符集、字符序

一般來說，可以在三個地方進(jìn)行配置：

創(chuàng)建數(shù)據(jù)庫的時候進(jìn)行配置。

mysql server 啟動的時候進(jìn)行配置。

從源碼編譯 mysql 的時候，通過編譯參數(shù)進(jìn)行配置

1、方式一：創(chuàng)建數(shù)據(jù)庫的時候進(jìn)行配置

這種方式比較靈活，也比較保險，它不依賴于默認(rèn)的字符集 / 字符序。當(dāng)你創(chuàng)建數(shù)據(jù)庫的時候指定字符集 / 字符序，后續(xù)創(chuàng)建 table、column 的時候，如果不特殊指定，會繼承對應(yīng)數(shù)據(jù)庫的字符集 / 字符序。

CREATE DATABASE mydb  DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;

2、方式二：mysql server 啟動的時候進(jìn)行配置

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

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

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

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

3、方式三：從源碼編譯 mysql 的時候，通過編譯參數(shù)進(jìn)行設(shè)置

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

創(chuàng)建 database、table 時，會將其作為默認(rèn)的字符集 / 字符序。

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

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

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