MySQL中，字符串类型不能忽视的问题

原创

我爱哇哈哈 2024-05-11 20:03:57 博主文章分类：MySQL实战 ©著作权

文章标签 mysql User MySQL 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者我爱哇哈哈的原创作品，请联系作者获取转载授权，否则将追究法律责任

在MySQL数据类型中，还有一个我们常用的数据类型：字符串类型。在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，TEXT和BOLB两者的区别在于BLOB没有字符集属性，属于二进制存储。

MySQL与其他关系型数据库（ORACLE、SQLSERVER）不同的是，MySQL的VARCHAR字符类型最大能存储65536个字符，所以在MySQL下，很大部分场景的字符串存储，使用VARCHAR类型就足够了。

1、字符集

在表结构的设计中，除了设计字段的数据类型外，我们还需要定义字符的字符集，不同的字符在不同的字符集编码下，对应着不同的二进制值。常见的字符集有GBK、UTF8，通常推荐把默认字符集设置为UTF8。

而且随着移动互联网的飞速发展，推荐把 MySQL 的默认字符集设置为 UTF8MB4，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储，比如 emoji 笑脸表情，对应的字符编码为 0xF09F988E；

2、排序规则

排序规则（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)

建议：大部分的业务表结构设计无需设置排序规则为大小写敏感！

3、修改字符集

这里我们来说明一下，如果前期在表结构设计时，没有考虑字符集对业务数据存储的影响，后期需要对字符集进行转换，但修改后依然无法插入emoji这类字符时。

ALTER TABLE emoji_test CHARSET utf8mb4;

这种情况是因为上述语句只是将表的字符集修改了，新增列的时候会变为utf8mb4，但对于已存在的列，其默认的字符集并不做修改。我们可以通过命令查看：

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，所以，我们应该使用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_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)

二、业务表设计实战

1、用户性别字段设计

我们在用户表设计时，通常会遇到固定选项值的字段，比如性别（SEX），选项有男、女、未知。状态（state），选项有正常、禁用等状态。但在日常设计时，常可以看到有些技术人员把这类字段设计为INT型去存储：

CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sex` tinyint DEFAULT NULL,

  ......

  PRIMARY KEY (`id`)

) ENGINE=InnoDB；

tinyint 列 sex 表示用户性别，但这样设计问题比较明显。

表达不清：在具体存储时，0 表示女，还是 1 表示女呢？每个业务可能有不同的潜规则；
脏数据：因为是 tinyint，因此除了 0 和 1，用户完全可以插入 2、3、4 这样的数值，最终表中存在无效数据的可能，后期再进行清理，代价就非常大了。

在 MySQL 8.0 版本之前，可以使用 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 之前的版本并没有提供约束功能。自 MySQL 8.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 显式地抛出了违法约束的提示。

2、账号密码存储设计

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

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

因此，可以对 MD5 进行暴力破解，计算出所有可能的字符串对应的 MD5 值。

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

password = MD5（‘psalt12345678’）

这样的密码存储设计是一种固定盐值的加密算法，其中存在三个主要问题：

若 salt 值被（离职）员工泄漏，则外部黑客依然存在暴利破解的可能性；
对于相同密码，其密码存储值相同，一旦一个用户密码泄漏，其他相同密码的用户的密码也将被泄漏；
固定使用 MD5 加密算法，一旦 MD5 算法被破解，则影响很大。

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

这里推荐一个密码设计方式，password字段存储格式如下：

$salt$cryption_algorithm$value

$salt：表示动态盐，每次用户注册时业务产生不同的盐值，并存储在数据库中。若做得再精细一点，可以动态盐值 + 用户注册日期合并为一个更为动态的盐值。
$cryption_algorithm：表示加密的算法，如 v1 表示 MD5 加密算法，v2 表示 AES256 加密算法，v3 表示 AES512 加密算法等。
$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 User\G

*************************** 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，然而由于使用了动态盐和动态加密算法，两者存储的内容完全不同。