• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
123457682 显示全部楼层 发表于 2021-10-27 20:22:43 |阅读模式 打印 上一主题 下一主题
媒介

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:

若强行在字符集为 UTF8 的列上插入 emoji 表情字符, MySQL 会抛出如下错误信息:
  1. mysql> SHOW CREATE TABLE emoji_test\G
  2. *************************** 1. row ***************************
  3.        Table: emoji_test
  4. Create Table: CREATE TABLE `emoji_test` (
  5.   `a` varchar(100) CHARACTER SET utf8,
  6.   PRIMARY KEY (`a`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  8. 1 row in set (0.01 sec)
  9. mysql> INSERT INTO emoji_test VALUES (0xF09F988E);
  10. ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x8E' for column 'a' at row 1
复制代码
包括 MySQL 8.0 版本在内,字符集默认设置成 UTF8MB4,8.0 版本之前默认的字符集为 Latin1。由于不同版本默认字符集的不同,你要显式地在设置文件中进行相关参数的设置:
  1. [mysqld]
  2. character-set-server = utf8mb4
  3. ...
复制代码
另外,不同的字符集,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 来检察:
  1. mysql> SHOW CHARSET LIKE 'utf8%';
  2. +---------+---------------+--------------------+--------+
  3. | Charset | Description   | Default collation  | Maxlen |
  4. +---------+---------------+--------------------+--------+
  5. | utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
  6. | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |
  7. +---------+---------------+--------------------+--------+
  8. 2 rows in set (0.01 sec)
  9. mysql> SHOW COLLATION LIKE 'utf8mb4%';
  10. +----------------------------+---------+-----+---------+----------+---------+---------------+
  11. | Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
  12. +----------------------------+---------+-----+---------+----------+---------+---------------+
  13. | utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
  14. | utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
  15. | utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
  16. | utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
  17. | utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 | PAD SPACE     |
  18. ......
复制代码
排序规则以 _ci 末端,表现不区分巨细写(Case Insentive),_cs 表现巨细写敏感,_bin 表现通过存储字符的二进制进行比较。需要留意的是,比较 MySQL 字符串,默认接纳不区分巨细的排序规则:
  1. mysql> SELECT 'a' = 'A';
  2. +-----------+
  3. | 'a' = 'A' |
  4. +-----------+
  5. |         1 |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  8. mysql> SELECT CAST('a' as char) COLLATE utf8mb4_0900_as_cs = CAST('A' as CHAR) COLLATE utf8mb4_0900_as_cs as result;
  9. +--------+
  10. | result |
  11. +--------+
  12. |      0 |
  13. +--------+
  14. 1 row in set (0.00 sec)
复制代码
牢记,绝大部门业务的表布局计划无须设置排序规则为巨细写敏感!除非你能明白你的业务真正需要。
准确修改字符集
固然,相信不少业务在计划时没有思量到字符集对于业务数据存储的影响,所以后期需要进行字符集转换,但许多同学会发现实行如下操纵后,依然无法插入 emoji 这类 UTF8MB4 字符:
  1. ALTER TABLE emoji_test CHARSET utf8mb4;
复制代码
其实,上述修改只是将表的字符集修改为 UTF8MB4,下次新增列时,若不显式地指定字符集,新列的字符聚会会议变动为 UTF8MB4,但对于已经存在的列,其默认字符集并不做修改,你可以通过下令 SHOW CREATE TABLE 确认:
  1. mysql> SHOW CREATE TABLE emoji_test\G
  2. *************************** 1. row ***************************
  3.        Table: emoji_test
  4. Create Table: CREATE TABLE `emoji_test` (
  5.   `a` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  6.   PRIMARY KEY (`a`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  8. 1 row in set (0.00 sec)
复制代码
可以看到,列 a 的字符集依然是 UTF8,而不是 UTF8MB4。因此,准确修改列字符集的下令应该使用 ALTER TABLE ... CONVERT TO...这样才能将之前的列 a 字符集从 UTF8 修改为 UTF8MB4:
  1. mysql> ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4;
  2. Query OK, 0 rows affected (0.94 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. mysql> SHOW CREATE TABLE emoji_test\G
  5. *************************** 1. row ***************************
  6.        Table: emoji_test
  7. Create Table: CREATE TABLE `emoji_test` (
  8.   `a` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  9.   PRIMARY KEY (`a`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  11. 1 row in set (0.00 sec)
复制代码
业务表布局计划实战


用户性别计划
计划表布局时,你会遇到一些固定选项值的字段。比方,性别字段(Sex),只有男或女;又大概状态字段(State),有用的值为运行、克制、重启等有限状态。
我观察后发现,大多数开发人员喜欢用 INT 的数字范例去存储性别字段,比如:
  1. CREATE TABLE `User` (
  2.   `id` bigint NOT NULL AUTO_INCREMENT,
  3.   `sex` tinyint DEFAULT NULL,
  4.   ......
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB;
复制代码
其中,tinyint 列 sex 表现用户性别,但这样计划问题比较显着。
       
  • 表达不清:在具体存储时,0 表现女,照旧 1 表现女呢?每个业务大概有不同的潜规则;   
  • 脏数据:由于是 tinyint,因此除了 0 和 1,用户完全可以插入 2、3、4 这样的数值,终极表中存在无效数据的大概,后期再进行整理,代价就非常大了。
在 MySQL 8.0 版本之前,可以使用 ENUM 字符串枚举范例,只允许有限的定义值插入。如果将参数 SQL_MODE 设置为严格模式,插入非定义数据就会报错:
  1. mysql> SHOW CREATE TABLE User\G
  2. *************************** 1. row ***************************
  3.        Table: User
  4. Create Table: CREATE TABLE `User` (
  5.   `id` bigint NOT NULL AUTO_INCREMENT,
  6.   `sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB
  9. 1 row in set (0.00 sec)
  10. mysql> SET sql_mode = 'STRICT_TRANS_TABLES';
  11. Query OK, 0 rows affected, 1 warning (0.00 sec)
  12. mysql> INSERT INTO User VALUES (NULL,'F');
  13. Query OK, 1 row affected (0.08 sec)
  14. mysql> INSERT INTO User VALUES (NULL,'A');
  15. ERROR 1265 (01000): Data truncated for column 'sex' at row 1
复制代码
由于范例 ENUM 并非 SQL 标准的数据范例,而是 MySQL 所独有的一种字符串范例。抛出的错误提示也并不直观,这样的实现总有一些遗憾,重要是由于MySQL 8.0 之前的版本并没有提供约束功能。自 MySQL 8.0.16 版本开始,数据库原生提供 CHECK 约束功能,可以方便地进行有限状态列范例的计划:
  1. mysql> SHOW CREATE TABLE User\G
  2. *************************** 1. row ***************************
  3.        Table: User
  4. Create Table: CREATE TABLE `User` (
  5.   `id` bigint NOT NULL AUTO_INCREMENT,
  6.   `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
  9. ) ENGINE=InnoDB
  10. 1 row in set (0.00 sec)
  11. mysql> INSERT INTO User VALUES (NULL,'M');
  12. Query OK, 1 row affected (0.07 sec)
  13. mysql> INSERT INTO User VALUES (NULL,'Z');
  14. 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
复制代码
其中:
       
  • $salt:表现动态盐,每次用户注册时业务产生不同的盐值,并存储在数据库中。若做得再精细一点,可以动态盐值 + 用户注册日期归并为一个更为动态的盐值。   
  • $cryption_algorithm:表现加密的算法,如 v1 表现 MD5 加密算法,v2 表现 AES256 加密算法,v3 表现 AES512 加密算法等。   
  • $value:表现加密后的字符串。
这时表 User 的布局计划如下所示:
  1. CREATE TABLE User (
  2.     id BIGINT NOT NULL AUTO_INCREMENT,
  3.     name VARCHAR(255) NOT NULL,
  4.     sex CHAR(1) NOT NULL,
  5.     password VARCHAR(1024) NOT NULL,
  6.     regDate DATETIME NOT NULL,
  7.     CHECK (sex = 'M' OR sex = 'F'),
  8.     PRIMARY KEY(id)
  9. );
  10. SELECT * FROM User\G
  11. *************************** 1. row ***************************
  12.       id: 1
  13.     name: David
  14.      sex: M
  15. password: $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074
  16. regDate: 2020-09-07 15:30:00
  17. *************************** 2. row ***************************
  18.       id: 2
  19.     name: Amy
  20.      sex: F
  21. password: $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882
  22. regDate: 2020-09-07 17:28:00
复制代码
在上面的例子中,用户 David 和 Amy 暗码都是 12345678,然而由于使用了动态盐和动态加密算法,两者存储的内容完全不同。
即便醉翁之意的用户拿到当前暗码加密算法,则通过加密算法 $cryption_algorithm 版本,可以对用户存储的暗码进行升级,进一步做好对于恶意数据攻击的防范。
总结

到此这篇关于MySQL中你大概忽略的COLLATION的文章就先容到这了,更多相关MySQL中COLLATION内容请搜刮脚本之家从前的文章或继续欣赏下面的相关文章盼望各人以后多多支持脚本之家!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作