前言

MySQL 數據庫的字符串類型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的類型在業務設計、數據庫性能方面的表現完全不同，其中最常使用的是 CHAR、VARCHAR。今天我就帶你深入了解字符串類型 CHAR、VARCHAR 的應用。

CHAR 和 VARCHAR 的定義

CHAR(N) 用來保存固定長度的字符，N 的范圍是 0 ~ 255，請牢記，N 表示的是字符，而不是字節。VARCHAR(N) 用來保存變長字符，N 的范圍為 0 ~ 65536， N 同樣表示字符。

在超出 65536 個字節的情況下，可以考慮使用更大的字符類型 TEXT 或 BLOB，兩者最大存儲長度為 4G，其區別是 BLOB 沒有字符集屬性，純屬二進制存儲。

和 Oracle、SQL Server 等傳統關系型數據庫不同的是，MySQL 數據庫的 VARCHAR 字符類型，最大能夠存儲 65536 個字節，所以在 MySQL 數據庫下，絕大部分場景使用類型 VARCHAR 就足夠了。

字符集

在表結構設計中，除了將列定義為 CHAR 和 VARCHAR 用以存儲字符以外，還需要額外定義字符對應的字符集，因為每種字符在不同字符集編碼下，對應著不同的二進制值。常見的字符集有 GBK、UTF8，通常推薦把默認字符集設置為 UTF8。

而且隨著移動互聯網的飛速發展，推薦把 MySQL 的默認字符集設置為 UTF8MB4，否則，某些 emoji 表情字符無法在 UTF8 字符集下存儲，比如 emoji 笑臉表情，對應的字符編碼為 0xF09F988E：

若強行在字符集為 UTF8 的列上插入 emoji 表情字符， MySQL 會拋出如下錯誤信息：

mysql> SHOW CREATE TABLE emoji_testG*************************** 1. row *************************** Table: emoji_testCreate Table: CREATE TABLE `emoji_test` ( `a` varchar(100) CHARACTER SET utf8, PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)mysql> INSERT INTO emoji_test VALUES (0xF09F988E);ERROR 1366 (HY000): Incorrect string value: ’xF0x9Fx98x8E’ for column ’a’ at row 1

包括 MySQL 8.0 版本在內，字符集默認設置成 UTF8MB4，8.0 版本之前默認的字符集為 Latin1。因為不同版本默認字符集的不同，你要顯式地在配置文件中進行相關參數的配置：

[mysqld]character-set-server = utf8mb4...

另外，不同的字符集，CHAR(N)、VARCHAR(N) 對應最長的字節也不相同。比如 GBK 字符集，1 個字符最大存儲 2 個字節，UTF8MB4 字符集 1 個字符最大存儲 4 個字節。所以從底層存儲內核看，在多字節字符集下，CHAR 和 VARCHAR 底層的實現完全相同，都是變長存儲！

從上面的例子可以看到，CHAR(1) 既可以存儲 1 個 ’a’ 字節，也可以存儲 4 個字節的 emoji 笑臉表情，因此 CHAR 本質也是變長的。

鑒于目前默認字符集推薦設置為 UTF8MB4，所以在表結構設計時，可以把 CHAR 全部用 VARCHAR 替換，底層存儲的本質實現一模一樣。

排序規則

排序規則（Collation）是比較和排序字符串的一種規則，每個字符集都會有默認的排序規則，你可以用命令 SHOW CHARSET 來查看：

mysql> SHOW CHARSET LIKE ’utf8%’;+---------+---------------+--------------------+--------+| Charset | Description | Default collation | Maxlen |+---------+---------------+--------------------+--------+| utf8 | UTF-8 Unicode | utf8_general_ci | 3 || utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |+---------+---------------+--------------------+--------+2 rows in set (0.01 sec)mysql> SHOW COLLATION LIKE ’utf8mb4%’;+----------------------------+---------+-----+---------+----------+---------+---------------+| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |+----------------------------+---------+-----+---------+----------+---------+---------------+| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD|| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD|| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD|| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD|| utf8mb4_bin| utf8mb4 | 46 | | Yes | 1 | PAD SPACE |......

排序規則以 _ci 結尾，表示不區分大小寫（Case Insentive），_cs 表示大小寫敏感，_bin 表示通過存儲字符的二進制進行比較。需要注意的是，比較 MySQL 字符串，默認采用不區分大小的排序規則：

mysql> SELECT ’a’ = ’A’;+-----------+| ’a’ = ’A’ |+-----------+| 1 |+-----------+1 row in set (0.00 sec)mysql> SELECT CAST(’a’ as char) COLLATE utf8mb4_0900_as_cs = CAST(’A’ as CHAR) COLLATE utf8mb4_0900_as_cs as result;+--------+| result |+--------+| 0 |+--------+1 row in set (0.00 sec)

牢記，絕大部分業務的表結構設計無須設置排序規則為大小寫敏感！除非你能明白你的業務真正需要。

正確修改字符集

當然，相信不少業務在設計時沒有考慮到字符集對于業務數據存儲的影響，所以后期需要進行字符集轉換，但很多同學會發現執行如下操作后，依然無法插入 emoji 這類 UTF8MB4 字符：

ALTER TABLE emoji_test CHARSET utf8mb4;

其實，上述修改只是將表的字符集修改為 UTF8MB4，下次新增列時，若不顯式地指定字符集，新列的字符集會變更為 UTF8MB4，但對于已經存在的列，其默認字符集并不做修改，你可以通過命令 SHOW CREATE TABLE 確認：

mysql> SHOW CREATE TABLE emoji_testG*************************** 1. row *************************** Table: emoji_testCreate Table: CREATE TABLE `emoji_test` ( `a` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

可以看到，列 a 的字符集依然是 UTF8，而不是 UTF8MB4。因此，正確修改列字符集的命令應該使用 ALTER TABLE ... CONVERT TO...這樣才能將之前的列 a 字符集從 UTF8 修改為 UTF8MB4：

mysql> ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4;Query OK, 0 rows affected (0.94 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE emoji_testG*************************** 1. row *************************** Table: emoji_testCreate Table: CREATE TABLE `emoji_test` ( `a` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)業務表結構設計實戰

用戶性別設計

設計表結構時，你會遇到一些固定選項值的字段。例如，性別字段（Sex），只有男或女；又或者狀態字段（State），有效的值為運行、停止、重啟等有限狀態。我觀察后發現，大多數開發人員喜歡用 INT 的數字類型去存儲性別字段，比如：

CREATE TABLE `User` ( `id` bigint NOT NULL AUTO_INCREMENT, `sex` tinyint DEFAULT NULL, ...... PRIMARY KEY (`id`)) ENGINE=InnoDB；

其中，tinyint 列 sex 表示用戶性別，但這樣設計問題比較明顯。

表達不清：在具體存儲時，0 表示女，還是 1 表示女呢？每個業務可能有不同的潛規則； 臟數據：因為是 tinyint，因此除了 0 和 1，用戶完全可以插入 2、3、4 這樣的數值，最終表中存在無效數據的可能，后期再進行清理，代價就非常大了。

在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚舉類型，只允許有限的定義值插入。如果將參數 SQL_MODE 設置為嚴格模式，插入非定義數據就會報錯：

mysql> SHOW CREATE TABLE UserG*************************** 1. row *************************** Table: UserCreate Table: CREATE TABLE `User` ( `id` bigint NOT NULL AUTO_INCREMENT, `sex` enum(’M’,’F’) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB1 row in set (0.00 sec)mysql> SET sql_mode = ’STRICT_TRANS_TABLES’;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> INSERT INTO User VALUES (NULL,’F’);Query OK, 1 row affected (0.08 sec)mysql> INSERT INTO User VALUES (NULL,’A’);ERROR 1265 (01000): Data truncated for column ’sex’ at row 1

由于類型 ENUM 并非 SQL 標準的數據類型，而是 MySQL 所獨有的一種字符串類型。拋出的錯誤提示也并不直觀，這樣的實現總有一些遺憾，主要是因為MySQL 8.0 之前的版本并沒有提供約束功能。自 MySQL 8.0.16 版本開始，數據庫原生提供 CHECK 約束功能，可以方便地進行有限狀態列類型的設計：

mysql> SHOW CREATE TABLE UserG*************************** 1. row *************************** Table: UserCreate Table: CREATE TABLE `User` ( `id` bigint NOT NULL AUTO_INCREMENT, `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4’M’) or (`sex` = _utf8mb4’F’)))) ENGINE=InnoDB1 row in set (0.00 sec)mysql> INSERT INTO User VALUES (NULL,’M’);Query OK, 1 row affected (0.07 sec)mysql> INSERT INTO User VALUES (NULL,’Z’);ERROR 3819 (HY000): Check constraint ’user_chk_1’ is violated.

從這段代碼中看到，第 8 行的約束定義 user_chk_1 表示列 sex 的取值范圍，只能是 M 或者 F。同時，當 15 行插入非法數據 Z 時，你可以看到 MySQL 顯式地拋出了違法約束的提示。

賬戶密碼存儲設計

切記，在數據庫表結構設計時，千萬不要直接在數據庫表中直接存儲密碼，一旦有惡意用戶進入到系統，則面臨用戶數據泄露的極大風險。比如金融行業，從合規性角度看，所有用戶隱私字段都需要加密，甚至業務自己都無法知道用戶存儲的信息（隱私數據如登錄密碼、手機、信用卡信息等）。

相信不少開發開發同學會通過函數 MD5 加密存儲隱私數據，這沒有錯，因為 MD5 算法并不可逆。然而，MD5 加密后的值是固定的，如密碼 12345678，它對應的 MD5 固定值即為 25d55ad283aa400af464c76d713c07ad。

因此，可以對 MD5 進行暴力破解，計算出所有可能的字符串對應的 MD5 值。若無法枚舉所有的字符串組合，那可以計算一些常見的密碼，如111111、12345678 等。我放在文稿中的這個網站，可用于在線解密 MD5 加密后的字符串。

所以，在設計密碼存儲使用，還需要加鹽（salt），每個公司的鹽值都是不同的，因此計算出的值也是不同的。若鹽值為 psalt，則密碼 12345678 在數據庫中的值為：

password = MD5（‘psalt12345678’）

這樣的密碼存儲設計是一種固定鹽值的加密算法，其中存在三個主要問題：

若 salt 值被（離職）員工泄漏，則外部黑客依然存在暴利破解的可能性；

對于相同密碼，其密碼存儲值相同，一旦一個用戶密碼泄漏，其他相同密碼的用戶的密碼也將被泄漏；

固定使用 MD5 加密算法，一旦 MD5 算法被破解，則影響很大。

所以一個真正好的密碼存儲設計，應該是：動態鹽 + 非固定加密算法。

我比較推薦這么設計密碼，列 password 存儲的格式如下：

$salt$cryption_algorithm$value

其中：

$salt：表示動態鹽，每次用戶注冊時業務產生不同的鹽值，并存儲在數據庫中。若做得再精細一點，可以動態鹽值 + 用戶注冊日期合并為一個更為動態的鹽值。 $cryption_algorithm：表示加密的算法，如 v1 表示 MD5 加密算法，v2 表示 AES256 加密算法，v3 表示 AES512 加密算法等。 $value：表示加密后的字符串。

這時表 User 的結構設計如下所示：

CREATE TABLE User ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex CHAR(1) NOT NULL, password VARCHAR(1024) NOT NULL, regDate DATETIME NOT NULL, CHECK (sex = ’M’ OR sex = ’F’), PRIMARY KEY(id));SELECT * FROM UserG*************************** 1. row *************************** id: 1 name: David sex: Mpassword: $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074 regDate: 2020-09-07 15:30:00*************************** 2. row *************************** id: 2 name: Amy sex: Fpassword: $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882 regDate: 2020-09-07 17:28:00

在上面的例子中，用戶 David 和 Amy 密碼都是 12345678，然而由于使用了動態鹽和動態加密算法，兩者存儲的內容完全不同。

即便別有用心的用戶拿到當前密碼加密算法，則通過加密算法 $cryption_algorithm 版本，可以對用戶存儲的密碼進行升級，進一步做好對于惡意數據攻擊的防范。

總結

到此這篇關于MySQL中你可能忽略的COLLATION的文章就介紹到這了,更多相關MySQL中COLLATION內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！