mysql 数据库的字符串类型有 char、varchar、binary、blob、text、enum、set。不同的类型在业务设计、数据库性能方面的表现完全不同，其中最常使用的是 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_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: '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_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。因此，正确修改列字符集的命令应该使用 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_testg *************************** 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)
用户性别设计
设计表结构时，你会遇到一些固定选项值的字段。例如，性别字段（sex），只有男或女；又或者状态字段（state），有效的值为运行、停止、重启等有限状态。
我观察后发现，大多数开发人员喜欢用 int 的数字类型去存储性别字段，比如：
create table `user` ( `id` bigint not null auto_increment, `sex` tinyint default null, ...... primary key (`id`) ) engine=innodb；
其中，tinyint 列 sex 表示用户性别，但这样设计问题比较明显。
在 mysql 8.0 版本之前，可以使用 enum 字符串枚举类型，只允许有限的定义值插入。如果将参数 sql_mode 设置为严格模式，插入非定义数据就会报错：
mysql> show create table userg *************************** 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 之前的版本并没有提供约束功能。自 mysql 8.0.16 版本开始，数据库原生提供 check 约束功能，可以方便地进行有限状态列类型的设计：
mysql> show create table userg *************************** 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 显式地抛出了违法约束的提示。
账户密码存储设计
切记，在数据库表结构设计时，千万不要直接在数据库表中直接存储密码，一旦有恶意用户进入到系统，则面临用户数据泄露的极大风险。比如金融行业，从合规性角度看，所有用户隐私字段都需要加密，甚至业务自己都无法知道用户存储的信息（隐私数据如登录密码、手机、信用卡信息等）。
相信不少开发开发同学会通过函数 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
其中：
这时表 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: 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，然而由于使用了动态盐和动态加密算法，两者存储的内容完全不同。
即便别有用心的用户拿到当前密码加密算法，则通过加密算法 $cryption_algorithm 版本，可以对用户存储的密码进行升级，进一步做好对于恶意数据攻击的防范。
到此这篇关于mysql中你可能忽略的collation的文章就介绍到这了,更多相关mysql中collation内容请搜索<编程笔记>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<编程笔记>！
需要了解更多数据库技术:MySQL中你可能忽略的COLLATION实例详解，都可以关注数据库技术分享栏目—编程笔记
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 