一、數據庫和字符集

1. 建庫時指定

創建數據庫時，顯式指定字符集和排序規則，同時，當切換到當前數據庫后，參數 character_set_database，collation_database 分別被覆蓋為當前顯式指定的字符集和排序規則。舉個簡單例子，創建數據庫 ytt_new2，顯式指定字符集為 latin1，同時排序規則為 latin1_bin。之后在切換到數據庫 ytt_new2 后，對應的系統參數也被修改。

MySQL> create database ytt_new2 default character set latin1 collate latin1_bin;
Query OK, 1 row affected (0.03 sec)

mysql> use ytt_new2
Database changed

mysql> select @@character_set_database,  @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_bin           |
+--------------------------+----------------------+
1 row in set (0.00 sec)

2. 改庫時指定

改庫類似建庫，效果一樣。但需要注意的一點是，修改庫字符集與排序規則后，之前基于這個庫創建的各種對象，還是沿用老的字符集與排序規則。

舉個例子，對存儲過程的影響：

-- 簡單寫個存儲過程
DELIMITER $$

USE `ytt_new2`$$

DROP PROCEDURE IF EXISTS `sp_demo`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_demo`(
    IN f1 VARCHAR(10),
    IN f2 VARCHAR(10)
    )
BEGIN
    DECLARE v1 VARCHAR(20);
    SET v1 = CONCAT(f1,f2);
    SELECT v1 AS result;
    END$$

DELIMITER ;

查看這個存儲過程的字符集，這里看到排序規則是 latin1_bin，對應的字符集是 latin1，和數據庫 ytt_new2 一致。

mysql> show create procedure sp_demoG
*************************** 1. row ***************************
           Procedure: sp_demo
...
  Database Collation: latin1_bin
1 row in set (0.00 sec)

-- 那接下來改掉數據庫的字符集為 UTF8
mysql> alter database ytt_new2 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected, 2 warning (0.02 sec)

mysql> select @@character_set_database,  @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.00 sec)

-- 再查看存儲過程 sp_demo 的字符集，還是之前的。
mysql> show create procedure sp_demoG
*************************** 1. row ***************************
           Procedure: sp_demo
...
  Database Collation: latin1_bin
1 row in set (0.00 sec)

-- 此時，調用存儲過程，字符集不對，報編碼錯誤。

mysql> call sp_demo('我','你');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x88\x91' for column 'f1' at row 1

-- 改變存儲過程字符集只能刪除重建，重新執行下之前的存儲過程代碼，再次調用，結果就正常了。

mysql> call sp_demo('我','你');
+--------+
| result |
+--------+
| 我你   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

3. 參數的指定

參數 character_set_database 和 collation_database 如果沒有指定，默認繼承服務器端對應參數 character_set_server 和 collation_server。

mysql> select @@character_set_server charset,  @@collation_server collation
    -> union all
    -> select @@character_set_database,  @@collation_database;
+---------+--------------------+
| charset | collation          |
+---------+--------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
| utf8mb4 | utf8mb4_0900_ai_ci |
+---------+--------------------+
2 rows in set (0.00 sec)

那這種情況下，建庫或者改庫時不指定具體的字符集和排序規則，默認繼承這兩個參數：

mysql> show create database ytt_new3G
*************************** 1. row ***************************
       Database: ytt_new3
Create Database: CREATE DATABASE `ytt_new3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

二、表和字符集

1. 建表時指定（顯式設置）

同建庫一樣，顯式指定字符集和排序規則，優先級最高，以指定的值為準。

-- 創建新庫 ytt_new4
mysql> create database ytt_new4;
Query OK, 1 row affected (0.02 sec)

mysql> use ytt_new4;
Database changed

-- 創建新表 t1, 字符集 latin1, 排序規則 latin1_bin
mysql> create table t1(a1 int) charset latin1 collate latin1_bin;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t1G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

2. 繼承設置（隱式轉換）

默認繼承所屬數據庫級別的字符集和排序規則。這里需要注意的是所屬數據庫，不是當前數據庫。

mysql> create database ytt_new2 default character set latin1 collate latin1_bin;Query OK, 1 row affected (0.03 sec)mysql> use ytt_new2Database changedmysql> select @@character_set_database,  @@collation_database;+--------------------------+----------------------+| @@character_set_database | @@collation_database |+--------------------------+----------------------+| latin1                   | latin1_bin           |+--------------------------+----------------------+1 row in set (0.00 sec)

3. 視圖

視圖其實就是虛擬的表，所以視圖的字符集也劃在表這塊簡單介紹下。視圖的字符集完全依賴客戶端的字符集設置。

比如：

-- 簡單寫個存儲過程DELIMITER $$USE `ytt_new2`$$DROP PROCEDURE IF EXISTS `sp_demo`$$CREATE DEFINER=`root`@`%` PROCEDURE `sp_demo`(    IN f1 VARCHAR(10),    IN f2 VARCHAR(10)    )BEGIN    DECLARE v1 VARCHAR(20);    SET v1 = CONCAT(f1,f2);    SELECT v1 AS result;    END$$DELIMITER ;

4. 觸發器

觸發器基于表，所以觸發器也歸類到表這塊。其實觸發器的編碼規則和視圖一樣。也是依賴客戶端的設定。

比如一個簡單的觸發器：

mysql> show create procedure sp_demoG*************************** 1. row ***************************           Procedure: sp_demo...  Database Collation: latin1_bin1 row in set (0.00 sec)-- 那接下來改掉數據庫的字符集為 UTF8mysql> alter database ytt_new2 character set utf8 collate utf8_general_ci;Query OK, 1 row affected, 2 warning (0.02 sec)mysql> select @@character_set_database,  @@collation_database;+--------------------------+----------------------+| @@character_set_database | @@collation_database |+--------------------------+----------------------+| utf8                     | utf8_general_ci      |+--------------------------+----------------------+1 row in set (0.00 sec)-- 再查看存儲過程 sp_demo 的字符集，還是之前的。mysql> show create procedure sp_demoG*************************** 1. row ***************************           Procedure: sp_demo...  Database Collation: latin1_bin1 row in set (0.00 sec)-- 此時，調用存儲過程，字符集不對，報編碼錯誤。mysql> call sp_demo('我','你');ERROR 1366 (HY000): Incorrect string value: '\xE6\x88\x91' for column 'f1' at row 1-- 改變存儲過程字符集只能刪除重建，重新執行下之前的存儲過程代碼，再次調用，結果就正常了。mysql> call sp_demo('我','你');+--------+| result |+--------+| 我你   |+--------+1 row in set (0.00 sec)Query OK, 0 rows affected, 2 warnings (0.01 sec)

三、列和字符集

1. 顯式指定

同數據庫和表一樣，列也可以顯式指定特定的字符集和排序規則。雖說是可以這樣做，但是非常不推薦，最主要原因是每個列字符集不一樣，導致寫入和檢索都得額外的編寫 SQL。

mysql> select @@character_set_server charset,  @@collation_server collation    -> union all    -> select @@character_set_database,  @@collation_database;+---------+--------------------+| charset | collation          |+---------+--------------------+| utf8mb4 | utf8mb4_0900_ai_ci || utf8mb4 | utf8mb4_0900_ai_ci |+---------+--------------------+2 rows in set (0.00 sec)

那接下來簡單插入一條記錄，需要為每列添加 introduer，否則報錯。

mysql> show create database ytt_new3G*************************** 1. row ***************************       Database: ytt_new3Create Database: CREATE DATABASE `ytt_new3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */1 row in set (0.00 sec)

查詢出來最麻煩，因為每次查詢出來的結果只能有一個字符集，字符集兼容的列可以一起檢索；不兼容的列得分開檢索。舉個例子，我想簡單的 SELECT * 拿出所有記錄，結果發現有一個字段據顯示不正常。

mysql> select * from t1;
+-----------+------------------+--------------+
| a1        | a2               | a3           |
+-----------+------------------+--------------+
| character | 瀛楃?闆嗗悎      | 字符集合     |
+-----------+------------------+--------------+
1 row in set (0.00 sec)

所以針對這種情形，該怎么檢索數據呢？必須得對單個字段檢索或者是對兼容的列一起檢索。

-- 以字符集 GBK 輸出列 a1 a2，由于 a1 是保存的是字母，所以兼容輸出。
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select a1,a2 from t1;
+-----------+--------------+
| a1        | a2           |
+-----------+--------------+
| character | 字符集合     |
+-----------+--------------+
1 row in set (0.00 sec)

-- 以字符集utf8mb4和gbk不兼容，得單獨輸出列a3。或者單獨輸出a2.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> select a3 from t1;
+--------------+
| a3           |
+--------------+
| 字符集合     |
+--------------+
1 row in set (0.00 sec)

2. 隱式轉換

這種方式，是最推薦的，也是最長的方式，所有列繼承表的字符集，不單獨指定。

-- 建表 t2，指定字符集為 gbk.
mysql> create table t2(a1 varchar(10),a2 varchar(10)) charset gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t2G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)

但是這里有一個需要注意的點，如果此時對表進行字符集變更，那表的列依然保留原來的字符集。

例如：

mysql> alter table t2 charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table t2G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` varchar(10) CHARACTER SET gbk DEFAULT NULL,
  `a2` varchar(10) CHARACTER SET gbk DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

如果想把表里字段的字符集也改了，應該用以下語句更改表字符集。

mysql> alter table t2 convert to character set utf8;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table t2G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

那到這兒，我們已經了解了字符集對數據庫，表以及列的使用以及相關影響。

大致總結下，這篇我詳細介紹了字符集在 MySQL 數據庫，表以及列相關對象處理時的注意事項，并且舉例說明。希望對大家有幫助。

第08期：有關 MySQL 字符集的注意事項