当前位置:数据库 > > 正文

mysql参数说明(MySQL中你可能忽略的COLLATION实例详解)

时间:2021-10-07 00:14:22类别:数据库

mysql参数说明

MySQL中你可能忽略的COLLATION实例详解

前言

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:

mysql参数说明(MySQL中你可能忽略的COLLATION实例详解)

若强行在字符集为 utf8 的列上插入 emoji 表情字符, mysql 会抛出如下错误信息:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 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 版本在内,字符集默认设置成 utf8mb4,8.0 版本之前默认的字符集为 latin1。因为不同版本默认字符集的不同,你要显式地在配置文件中进行相关参数的配置:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • [mysqld]
  •  
  • character-set-server = utf8mb4
  •  
  • ...
  • 另外,不同的字符集,char(n)、varchar(n) 对应最长的字节也不相同。比如 gbk 字符集,1 个字符最大存储 2 个字节,utf8mb4 字符集 1 个字符最大存储 4 个字节。所以从底层存储内核看,在多字节字符集下,char 和 varchar 底层的实现完全相同,都是变长存储!

    mysql参数说明(MySQL中你可能忽略的COLLATION实例详解)

    从上面的例子可以看到,char(1) 既可以存储 1 个 'a' 字节,也可以存储 4 个字节的 emoji 笑脸表情,因此 char 本质也是变长的。

    鉴于目前默认字符集推荐设置为 utf8mb4,所以在表结构设计时,可以把 char 全部用 varchar 替换,底层存储的本质实现一模一样。

    排序规则

    排序规则(collation)是比较和排序字符串的一种规则,每个字符集都会有默认的排序规则,你可以用命令 show charset 来查看:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 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 字符串,默认采用不区分大小的排序规则:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 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 字符:

  • ?
  • 1
  • alter table emoji_test charset utf8mb4;
  • 其实,上述修改只是将表的字符集修改为 utf8mb4,下次新增列时,若不显式地指定字符集,新列的字符集会变更为 utf8mb4,但对于已经存在的列,其默认字符集并不做修改,你可以通过命令 show create table 确认:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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。因此,正确修改列字符集的命令应该使用 alter table ... convert to...这样才能将之前的列 a 字符集从 utf8 修改为 utf8mb4:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 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)
  • 业务表结构设计实战

    用户性别设计

    设计表结构时,你会遇到一些固定选项值的字段。例如,性别字段(sex),只有男或女;又或者状态字段(state),有效的值为运行、停止、重启等有限状态。
    我观察后发现,大多数开发人员喜欢用 int 的数字类型去存储性别字段,比如:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 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 设置为严格模式,插入非定义数据就会报错:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 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 之前的版本并没有提供约束功能。自 mysql 8.0.16 版本开始,数据库原生提供 check 约束功能,可以方便地进行有限状态列类型的设计:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 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 显式地抛出了违法约束的提示。

    账户密码存储设计

    切记,在数据库表结构设计时,千万不要直接在数据库表中直接存储密码,一旦有恶意用户进入到系统,则面临用户数据泄露的极大风险。比如金融行业,从合规性角度看,所有用户隐私字段都需要加密,甚至业务自己都无法知道用户存储的信息(隐私数据如登录密码、手机、信用卡信息等)。

    相信不少开发开发同学会通过函数 md5 加密存储隐私数据,这没有错,因为 md5 算法并不可逆。然而,md5 加密后的值是固定的,如密码 12345678,它对应的 md5 固定值即为 25d55ad283aa400af464c76d713c07ad。

    因此,可以对 md5 进行暴力破解,计算出所有可能的字符串对应的 md5 值。若无法枚举所有的字符串组合,那可以计算一些常见的密码,如111111、12345678 等。我放在文稿中的这个网站,可用于在线解密 md5 加密后的字符串。

    所以,在设计密码存储使用,还需要加盐(salt),每个公司的盐值都是不同的,因此计算出的值也是不同的。若盐值为 psalt,则密码 12345678 在数据库中的值为:

  • ?
  • 1
  • password = md5(‘psalt12345678')
  • 这样的密码存储设计是一种固定盐值的加密算法,其中存在三个主要问题:

    若 salt 值被(离职)员工泄漏,则外部黑客依然存在暴利破解的可能性;

    对于相同密码,其密码存储值相同,一旦一个用户密码泄漏,其他相同密码的用户的密码也将被泄漏;

    固定使用 md5 加密算法,一旦 md5 算法被破解,则影响很大。

    所以一个真正好的密码存储设计,应该是:动态盐 + 非固定加密算法。

    我比较推荐这么设计密码,列 password 存储的格式如下:

  • ?
  • 1
  • $salt$cryption_algorithm$value
  • 其中:

    这时表 user 的结构设计如下所示:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • create table user (
  •  
  •     id bigint not null auto_increment,
  •  
  •     name varchar(255) not null,
  •  
  •     sex char(1) not null,
  •  
  •     password varchar(1024) not null,
  • &nbs

    猜您喜欢