前言

MySQL 數(shù)據(jù)庫(kù)的字符串類型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的類型在業(yè)務(wù)設(shè)計(jì)、數(shù)據(jù)庫(kù)性能方面的表現(xiàn)完全不同，其中最常使用的是 CHAR、VARCHAR。今天我就帶你深入了解字符串類型 CHAR、VARCHAR 的應(yīng)用。

CHAR 和 VARCHAR 的定義

CHAR(N) 用來(lái)保存固定長(zhǎng)度的字符，N 的范圍是 0 ~ 255，請(qǐng)牢記，N 表示的是字符，而不是字節(jié)。VARCHAR(N) 用來(lái)保存變長(zhǎng)字符，N 的范圍為 0 ~ 65536， N 同樣表示字符。

在超出 65536 個(gè)字節(jié)的情況下，可以考慮使用更大的字符類型 TEXT 或 BLOB，兩者最大存儲(chǔ)長(zhǎng)度為 4G，其區(qū)別是 BLOB 沒(méi)有字符集屬性，純屬二進(jìn)制存儲(chǔ)。

和 Oracle、SQL Server 等傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)不同的是，MySQL 數(shù)據(jù)庫(kù)的 VARCHAR 字符類型，最大能夠存儲(chǔ) 65536 個(gè)字節(jié)，所以在 MySQL 數(shù)據(jù)庫(kù)下，絕大部分場(chǎng)景使用類型 VARCHAR 就足夠了。

字符集

在表結(jié)構(gòu)設(shè)計(jì)中，除了將列定義為 CHAR 和 VARCHAR 用以存儲(chǔ)字符以外，還需要額外定義字符對(duì)應(yīng)的字符集，因?yàn)槊糠N字符在不同字符集編碼下，對(duì)應(yīng)著不同的二進(jìn)制值。常見(jiàn)的字符集有 GBK、UTF8，通常推薦把默認(rèn)字符集設(shè)置為 UTF8。

而且隨著移動(dòng)互聯(lián)網(wǎng)的飛速發(fā)展，推薦把 MySQL 的默認(rèn)字符集設(shè)置為 UTF8MB4，否則，某些 emoji 表情字符無(wú)法在 UTF8 字符集下存儲(chǔ)，比如 emoji 笑臉表情，對(duì)應(yīng)的字符編碼為 0xF09F988E：

若強(qiáng)行在字符集為 UTF8 的列上插入 emoji 表情字符， MySQL 會(huì)拋出如下錯(cuò)誤信息：

mysql> SHOW CREATE TABLE emoji_test\G

*************************** 1. row ***************************

       Table: emoji_test

Create Table: CREATE TABLE `emoji_test` (

  `a` varchar(100) CHARACTER SET utf8,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8



1 row in set (0.01 sec)

mysql> INSERT INTO emoji_test VALUES (0xF09F988E);

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x8E' for column 'a' at row 1

包括 MySQL 8.0 版本在內(nèi)，字符集默認(rèn)設(shè)置成 UTF8MB4，8.0 版本之前默認(rèn)的字符集為 Latin1。因?yàn)椴煌姹灸J(rèn)字符集的不同，你要顯式地在配置文件中進(jìn)行相關(guān)參數(shù)的配置：

[mysqld]

character-set-server = utf8mb4

...

另外，不同的字符集，CHAR(N)、VARCHAR(N) 對(duì)應(yīng)最長(zhǎng)的字節(jié)也不相同。比如 GBK 字符集，1 個(gè)字符最大存儲(chǔ) 2 個(gè)字節(jié)，UTF8MB4 字符集 1 個(gè)字符最大存儲(chǔ) 4 個(gè)字節(jié)。所以從底層存儲(chǔ)內(nèi)核看，在多字節(jié)字符集下，CHAR 和 VARCHAR 底層的實(shí)現(xiàn)完全相同，都是變長(zhǎng)存儲(chǔ)！

從上面的例子可以看到，CHAR(1) 既可以存儲(chǔ) 1 個(gè) 'a' 字節(jié)，也可以存儲(chǔ) 4 個(gè)字節(jié)的 emoji 笑臉表情，因此 CHAR 本質(zhì)也是變長(zhǎng)的。

鑒于目前默認(rèn)字符集推薦設(shè)置為 UTF8MB4，所以在表結(jié)構(gòu)設(shè)計(jì)時(shí)，可以把 CHAR 全部用 VARCHAR 替換，底層存儲(chǔ)的本質(zhì)實(shí)現(xiàn)一模一樣。

排序規(guī)則

排序規(guī)則（Collation）是比較和排序字符串的一種規(guī)則，每個(gè)字符集都會(huì)有默認(rèn)的排序規(guī)則，你可以用命令 SHOW CHARSET 來(lái)查看：

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     |

......

排序規(guī)則以 _ci 結(jié)尾，表示不區(qū)分大小寫(xiě)（Case Insentive），_cs 表示大小寫(xiě)敏感，_bin 表示通過(guò)存儲(chǔ)字符的二進(jìn)制進(jìn)行比較。需要注意的是，比較 MySQL 字符串，默認(rèn)采用不區(qū)分大小的排序規(guī)則：

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)

牢記，絕大部分業(yè)務(wù)的表結(jié)構(gòu)設(shè)計(jì)無(wú)須設(shè)置排序規(guī)則為大小寫(xiě)敏感！除非你能明白你的業(yè)務(wù)真正需要。

正確修改字符集

當(dāng)然，相信不少業(yè)務(wù)在設(shè)計(jì)時(shí)沒(méi)有考慮到字符集對(duì)于業(yè)務(wù)數(shù)據(jù)存儲(chǔ)的影響，所以后期需要進(jìn)行字符集轉(zhuǎn)換，但很多同學(xué)會(huì)發(fā)現(xiàn)執(zhí)行如下操作后，依然無(wú)法插入 emoji 這類 UTF8MB4 字符：

ALTER TABLE emoji_test CHARSET utf8mb4;

其實(shí)，上述修改只是將表的字符集修改為 UTF8MB4，下次新增列時(shí)，若不顯式地指定字符集，新列的字符集會(huì)變更為 UTF8MB4，但對(duì)于已經(jīng)存在的列，其默認(rèn)字符集并不做修改，你可以通過(guò)命令 SHOW CREATE TABLE 確認(rèn)：

mysql> SHOW CREATE TABLE emoji_test\G

*************************** 1. row ***************************

       Table: emoji_test

Create 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_ci

1 row in set (0.00 sec)

可以看到，列 a 的字符集依然是 UTF8，而不是 UTF8MB4。因此，正確修改列字符集的命令應(yīng)該使用 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: 0



mysql> SHOW CREATE TABLE emoji_test\G

*************************** 1. row ***************************

       Table: emoji_test

Create 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_ci

1 row in set (0.00 sec)

業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)

用戶性別設(shè)計(jì)

設(shè)計(jì)表結(jié)構(gòu)時(shí)，你會(huì)遇到一些固定選項(xiàng)值的字段。例如，性別字段（Sex），只有男或女；又或者狀態(tài)字段（State），有效的值為運(yùn)行、停止、重啟等有限狀態(tài)。
我觀察后發(fā)現(xiàn)，大多數(shù)開(kāi)發(fā)人員喜歡用 INT 的數(shù)字類型去存儲(chǔ)性別字段，比如：

CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sex` tinyint DEFAULT NULL,

  ......

  PRIMARY KEY (`id`)

) ENGINE=InnoDB；

其中，tinyint 列 sex 表示用戶性別，但這樣設(shè)計(jì)問(wèn)題比較明顯。

• 表達(dá)不清：在具體存儲(chǔ)時(shí)，0 表示女，還是 1 表示女呢？每個(gè)業(yè)務(wù)可能有不同的潛規(guī)則；
• 臟數(shù)據(jù)：因?yàn)槭?tinyint，因此除了 0 和 1，用戶完全可以插入 2、3、4 這樣的數(shù)值，最終表中存在無(wú)效數(shù)據(jù)的可能，后期再進(jìn)行清理，代價(jià)就非常大了。

在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚舉類型，只允許有限的定義值插入。如果將參數(shù) SQL_MODE 設(shè)置為嚴(yán)格模式，插入非定義數(shù)據(jù)就會(huì)報(bào)錯(cuò)：

mysql> SHOW CREATE TABLE User\G

*************************** 1. row ***************************

       Table: User

Create Table: CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

1 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 標(biāo)準(zhǔn)的數(shù)據(jù)類型，而是 MySQL 所獨(dú)有的一種字符串類型。拋出的錯(cuò)誤提示也并不直觀，這樣的實(shí)現(xiàn)總有一些遺憾，主要是因?yàn)镸ySQL 8.0 之前的版本并沒(méi)有提供約束功能。自 MySQL 8.0.16 版本開(kāi)始，數(shù)據(jù)庫(kù)原生提供 CHECK 約束功能，可以方便地進(jìn)行有限狀態(tài)列類型的設(shè)計(jì)：

mysql> SHOW CREATE TABLE User\G

*************************** 1. row ***************************

       Table: User

Create 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=InnoDB

1 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。同時(shí)，當(dāng) 15 行插入非法數(shù)據(jù) Z 時(shí)，你可以看到 MySQL 顯式地拋出了違法約束的提示。

賬戶密碼存儲(chǔ)設(shè)計(jì)

切記，在數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)時(shí)，千萬(wàn)不要直接在數(shù)據(jù)庫(kù)表中直接存儲(chǔ)密碼，一旦有惡意用戶進(jìn)入到系統(tǒng)，則面臨用戶數(shù)據(jù)泄露的極大風(fēng)險(xiǎn)。比如金融行業(yè)，從合規(guī)性角度看，所有用戶隱私字段都需要加密，甚至業(yè)務(wù)自己都無(wú)法知道用戶存儲(chǔ)的信息（隱私數(shù)據(jù)如登錄密碼、手機(jī)、信用卡信息等）。

相信不少開(kāi)發(fā)開(kāi)發(fā)同學(xué)會(huì)通過(guò)函數(shù) MD5 加密存儲(chǔ)隱私數(shù)據(jù)，這沒(méi)有錯(cuò)，因?yàn)?MD5 算法并不可逆。然而，MD5 加密后的值是固定的，如密碼 12345678，它對(duì)應(yīng)的 MD5 固定值即為 25d55ad283aa400af464c76d713c07ad。

因此，可以對(duì) MD5 進(jìn)行暴力破解，計(jì)算出所有可能的字符串對(duì)應(yīng)的 MD5 值。若無(wú)法枚舉所有的字符串組合，那可以計(jì)算一些常見(jiàn)的密碼，如111111、12345678 等。我放在文稿中的這個(gè)網(wǎng)站，可用于在線解密 MD5 加密后的字符串。

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

password = MD5（‘psalt12345678'）

這樣的密碼存儲(chǔ)設(shè)計(jì)是一種固定鹽值的加密算法，其中存在三個(gè)主要問(wèn)題：

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

對(duì)于相同密碼，其密碼存儲(chǔ)值相同，一旦一個(gè)用戶密碼泄漏，其他相同密碼的用戶的密碼也將被泄漏；

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

所以一個(gè)真正好的密碼存儲(chǔ)設(shè)計(jì)，應(yīng)該是：動(dòng)態(tài)鹽 + 非固定加密算法。

我比較推薦這么設(shè)計(jì)密碼，列 password 存儲(chǔ)的格式如下：

$salt$cryption_algorithm$value

其中：

• $salt：表示動(dòng)態(tài)鹽，每次用戶注冊(cè)時(shí)業(yè)務(wù)產(chǎn)生不同的鹽值，并存儲(chǔ)在數(shù)據(jù)庫(kù)中。若做得再精細(xì)一點(diǎn)，可以動(dòng)態(tài)鹽值 + 用戶注冊(cè)日期合并為一個(gè)更為動(dòng)態(tài)的鹽值。
• $cryption_algorithm：表示加密的算法，如 v1 表示 MD5 加密算法，v2 表示 AES256 加密算法，v3 表示 AES512 加密算法等。
• $value：表示加密后的字符串。

這時(shí)表 User 的結(jié)構(gòu)設(shè)計(jì)如下所示：

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 User\G

*************************** 1. row ***************************

      id: 1

    name: David

     sex: M

password: $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074

 regDate: 2020-09-07 15:30:00

*************************** 2. row ***************************

      id: 2

    name: Amy

     sex: F

password: $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882

 regDate: 2020-09-07 17:28:00

在上面的例子中，用戶 David 和 Amy 密碼都是 12345678，然而由于使用了動(dòng)態(tài)鹽和動(dòng)態(tài)加密算法，兩者存儲(chǔ)的內(nèi)容完全不同。

即便別有用心的用戶拿到當(dāng)前密碼加密算法，則通過(guò)加密算法 $cryption_algorithm 版本，可以對(duì)用戶存儲(chǔ)的密碼進(jìn)行升級(jí)，進(jìn)一步做好對(duì)于惡意數(shù)據(jù)攻擊的防范。

總結(jié)

到此這篇關(guān)于MySQL中你可能忽略的COLLATION的文章就介紹到這了,更多相關(guān)MySQL中COLLATION內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！