前言
在设计数据库中，我们对字符串类型并不陌生。MySQl的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的业务对应不同的类型，本博文主要是讲述了CHAR和VACHAR两种类型在使用过程中的一些注意事项。
定义
CHAR(N)表示保存定长字符，VARCHAR(N)表示保存变长字符。但是需要注意的是，无论是定长还是变长，N代表的是字符，而不是字节。CHAR的取值范围是0~255，VARCHAR为0~65536。在绝大部分场景下，VARCHAR完全满足我们的需求。但是如果超出了VARCHAR的范围，我们就需要考虑更大的字符类型 TEXT 或 BLOB。两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储。
字符集
我们对于字符集这个名词并不陌生。字符集就是我们对于字符的编码格式。我们在设计数据库的时候，推荐将MySQL数据库的字符集设置为UTF8MB4。这是因为UTF8MB4可以存储emoji表情，并且对于中文有更好的支持。原因就是UTF8MB4最大的字符编码长度为4字节，而UTF8是三个字节。也可以说UTF8MB4是UTF8的一个超集。因为不同版本默认字符集的不同，你要显式地在配置文件中进行相关参数的配置：character-set-server = utf8mb4。在UTF8MB4的编码格式下，CHAR可以存储一字节的字符，也可以存储4个字节的字符，所以原理上来讲，也是变长存储。所以在UTF8MB4的编码格式下，我们可以把CHAR全部用VARCHAR替换，是没有问题的。
排序规则
每一种字符集都有其默认的排序规则。查看默认排序规则的SQl为：SHOW CHARSET LIKE 'utf8%';
排序规则以 _ci 结尾，表示不区分大小写（Case Insentive），_cs 表示大小写敏感，_bin 表示通过存储字符的二进制进行比较。需要注意的是，比较 MySQL 字符串，默认采用不区分大小的排序规则。所以我们在存放像用户名等等需要区分大小写的字段的时候，需要单独设置。
修改字符集
修改表中已有列的字符集，使用命令 ALTER TABLE ... CONVERT TO ....;
不要使用 ALERT TABLE emoji_test CHARSET utf8mb4;
原因是这种写法只会影响修改之后的数据的字符集，不会修改老数据的字符集。
设计枚举字段
我们在设计枚举字段（比如用户性别、订单状态等等）的时候，习惯用TINYINT来表示。这种设计的不足表现在，不能很直观的看出每个数字的含义，而且可能出现预期之外的数据（比如性别0代表男，1代表女，2代表未知，有存入3等等非枚举项数据的风险）。
那么，对于枚举值我们应该怎么设计比较好呢？分为两种情况。
一是MySQL8.0.16之后，我们可以使用数据库原生提供 CHECK 约束功能来限制我们的枚举项。写法如下：
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。同时，当 15 行插入非法数据 Z 时，你可以看到 MySQL 显式地抛出了违法约束的提示。
二是在MySQL8.0.16之前，我们可以通过ENUM 字符串枚举类型并将参数 SQL_MODE 设置为严格模式来保证枚举值的正确定，代码如下：
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 标准的数据类型，而是 MySQL 所独有的一种字符串类型。抛出的错误提示也并不直观，这样的实现总有一些遗憾，主要是因为MySQL 8.0 之前的版本并没有提供约束功能。
文章评论