MySQL数据库的字符串类型由CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的类型在业务设计、数据库性能方面表现完全不同，其中最常用的是CHAR和VARCHAR。
CHAR和VARCHAR的定义
CHAR(N) 用来保存固定长度的字符，N的取值范围时0~255，N表示的是字符，而不是字节。
VARCHAR(N)用来保存变长字符，N的取值范围是0~65535，N表示的是字符，而不是字节。MySQL 数据库下，绝大部分场景使用类型 VARCHAR 就足够了。
在超出65535个字符的情况下，可以考虑使用更大的字符类型TEXT或BLOB，两者最大存储长度为4G，其区别是BLOB没有字符集属性，纯属二进制存储。
字符集
在表结构设计中，除了将列定义为CHAR或VARCHAR用以存储字符外，还需要额外定义字符对应的字符集，因为每种字符在不同的字符集编码下，对应着不同的二进制值。常见的字符集有GBK、UTF8，通常推荐把默认的字符集设置为UTF8。而随着移动互联网的飞速发展，推荐把MySQL的默认字符集设置为UTF8MB4。某些emoji表情符号无法在UTF8字符集下存储，比如emoji笑脸表情，对应的字符编码为0xF09F988E:
emoji表情
若强行在字符集为 UTF8 的列上插入 emoji 表情字符， MySQL 会抛出如下错误信息：
mysql> SHOW CREATE TABLE emoji_test;
+------------+--------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------+
| emoji_test | CREATE TABLE `emoji_test` (
`a` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO emoji_test VALUE (0xF09F988E);
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x8E' for column 'a' at row 1
SELECT CAST(0xF09F988E as char charset utf8mb4) as emoji;
/***************************************************
CAST() 函数将一种类型的值转换成指定类型的值。
目标类型：BINARY、CHAR、DATE、DATETIME、TIME、DECIMAL、SIGNED、UNSIGNED。
通常用于返回具有指定类型的值，以便在WHERE、JOIN和HAVING子句中进行比较。
***************************************************/
SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));
/***************************************************
CONCAT() 函数用于连接两个字符串，形成一个字符串
***************************************************/
MySQL8.0版本，字符集默认设置为UTF8MB4,之前的版本默认的字符集为Latin1。因为不同版本默认字符集的不同，需要显式的在配置文件中进行相关参数的配置：
[mysqld]
character-set-server = utf8mb4
...
另外，不同的字符集，CHAR(N)、VARCHAR(N)对应最长的字节也不相同。比如GBK字符集，1个字符最大存储2个字节，UTF8MB4字符集1个字符最大存储4个字节。所以从底层存储内核上看，在多字节字符集下，CHAR和VARCHAR底层的实现完全相同，都是变长存储！
UTF8MB4字符集中，CHAR(1)即可以存储1个'a'字节，也可以存储4个字节的emoji表情符号，因此CHAR本质也是变长的。鉴于目前默认的字符集推荐设置为UTF8MB4，所以在表结构设计时，可以把CHAR全部用VARCHAR替换，底层存储的本质实现一模一样。
排序规则
排序规则（Collation）是比较和排序字符串的一种规则，每个字符集都会有默认的排序规则，可以使用SHOW CHARSET来查看字符集及其默认排序规则，使用SHOW COLLATION来查看字符集支持的排序规则：
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.00 sec)
mysql> SHOW COLLATION WHERE Charset LIKE 'utf8%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
...
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 | PAD SPACE |
+----------------------------+---------+-----+---------+----------+---------+---------------+
103 rows in set (0.00 sec)
-
排序规则以
_ci结尾，表示不区分大小写（Case Insentive） -
排序规则以
_cs结尾，表示大小写敏感 -
排序规则以
_bin结尾，表示通过存储值的二进制进行比较
牢记，绝大部分业务的表结构设计无须设置排序规则为大小写敏感！
正确修改字符集
-- 修改表的字符集修改为UTF8MB4，下次新增列，若不显示指定字符集，新列字符集为UTF8MB4,但已经存在的列，其默认字符集并不做修改
ALTER TABLE emoji_test CHARSET utf8mb4;
-- 修改表中某一字段的字符集和排序规则
ALTER TABLE emoji_test CHANGE a a char(1) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- 要修改已经存在的列的字符集和排序规则，应该使用CONVERT TO
ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
业务表结构设计实战
用户性别设计
设计表结构时，会遇到一些固定选项值的字段。例如：
性别字段Sex，有效值只有男、女；
状态字段Status，有效的值为运行、停止、暂停、重启等有限状态。
大多数程序员同学喜欢使用整型类型去存储，比如：
CREATE TABLE users (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`sex` TINYINT DEFAULT 1,
...
PRIMARY KEY(`id`)
);
但是这样设计问题比较明显：
-
表达不清：在具体存储时，0表示女，还是1表示女呢？每个业务可能有不同的潜规则；
-
脏数据：因为TINYINT除了0、1，用户还可以插入数值2、3、4等，最终表中可能存在无效数据，后期再进行清理，代价会非常大。
在MySQL 8.0版本之前，可以使用ENUM字符枚举类型，只允许有限的定义值插入。如果将参数SQL_MODE设置为严格模式，插入非定义数据就会报错：
mysql> SHOW CREATE TABLE users;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into users values (1,'A');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
由于ENUM并非SQL标准的数据类型，而是MySQL所独有的一种字符串类型。抛出的错误提示并不直观，这样的实现总有一些遗憾，自MySQL 8.0.16版本之后，数据库原生提供CHECK约束功能：
mysql> SHOW CREATE TABLE users;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `sex_chk1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO users VALUES (1, 'A');
ERROR 3819 (HY000): Check constraint 'sex_chk1' is violated.
mysql> INSERT INTO users VALUES (2, 'M');
Query OK, 1 row affected (0.00 sec)
账户密码存储设计
数据库表结构设计时，千万不要直接在数据库中直接存储密码，一旦有恶意用户进入到系统，则面临用户数据泄露的极大风险。比如金融行业，从合规性角度来看，所有用户隐私字段都需要加密，甚至业务本身都无法知道用户存储的信息（隐私数据比如登录密码、手机号、信用卡信息等）。
相信不少程序员同学会通过函数MD5加密存储隐私数据，这没有错，因为MD5算法并不可逆。然而，MD5加密后的值是固定的，如12345678，它对应的MD5值固定为25d55ad283aa400af464c76d713c07ad。因此可以对MD5进行暴力破解，计算出所有可能的字符串对应的MD5值。若无法枚举所有的字符串组合，那可以计算一些常见的弱密码，如1111111，12345678等。
所以在设计密码存储时，还需要加盐（salt），每个公司的盐值都是不同的，因此计算出的值也是不同的。若盐值为tsalt,则密码12345678在数据库中的值为：
mysql> select md5('tsalt12345678');
+----------------------------------+
| md5('tsalt12345678') |
+----------------------------------+
| e76f9f3dffba437b56d1b03ea9c8ed2e |
+----------------------------------+
1 row in set (0.00 sec)
这样的密码存储设计时一种固定盐值的加密算法，其中存在3个主要问题：
-
若sal值倍（离职）员工泄露，则外部黑客依然存在暴力破解的可能性；
-
对于相同密码，其存储值相同，一旦一个用户的密码泄露，其他相同密码值的用户的密码也就泄露；
-
固定使用MD5加密算法，一旦MD5算法被攻破，则影响很大。
所以一个真正好的密码存储设计，应该是：动态盐+动态加密算法，比较推荐这么设计密码，列password存储的格式如下：
$salt$cryption_algorithm$value
-- $salt: 表示动态盐，每次用户注册时，业务产生不同的盐值，并存储在数据库中。若做的精细一点，可以动态盐+用户注册日期合并为一个更为动态的盐值
-- $cryption_algorithm：表示加密算法，如V1表示MD5，V2表示AES256算法，V3表示AES512算法等
-- $value：表示加密后的字符串
这时users表结构设计如下：
mysql> SHOW CREATE TABLE users;
+-------+-----------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`sex` char(1) COLLATE utf8mb4_general_ci NOT NULL,
`password` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL,
`regDate` datetime NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `users_chk_1` CHECK (((`sex` = _latin1'M') or (`sex` = _latin1'F')))
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users;
+----+-------+-----+------------------------------------------------ -------------+---------------------+
| id | name | sex | password | regDate |
+----+-------+-----+--------------------------------------------------------------+---------------------+
| 1 | David | M | $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074 | 2020-09-07 15:30:00 |
| 2 | Amy | F | $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882 | 2020-09-07 17:28:00 |
+----+-------+-----+--------------------------------------------------------------+---------------------+
2 rows in set (0.00 sec)
在上面例子中，用户David和Amy密码都是12345678，然而由于使用了动态盐和动态加密算法，两者存储的内容完全不同。即便别有用心的用户拿到当前密码加密算法，则通过加密算法$cryption_algorithm版本，可以对用户存储的密码进行升级，进一步做好对于恶意数据攻击的防范。
总结
字符串时使用最为广泛的数据类型之一，但也是设计最初容易犯错的部分，后期业务跑起来在进行修改，代价将会非常巨大。
-
CHAR和VARCHAR虽然分别用于存储定长和变长字符，但对于变长字符集，其本质是一样的，都是变长，设计时完全可以用VARCHAR替代CHAR；
-
推荐设置MySQL默认字符集为UTF8MB4，可以用于存储emoji等扩展字符；
-
排序规则很重要，用于字符的比较和排序，但大部分场景不需要使用区分大小写的排序规则；
-
修改表中所有列的字符集，使用命令
ALTER TABLE ... CONVERT TO ...; -
用户性别，运行状态等有限值的列，MySQL8.0.16版本直接使用
CHECK约束机制，之前的版本可使用ENUM枚举字符串类型，外加SQL_MODE的严格模式； -
业务隐私信息，如密码、手机、信用卡等信息，需要加密。切记简单的MD5算法是可以进行暴力破解，并不安全，推荐使用动态盐+动态加密算法进行隐私数据的存储。
网友评论