[Mysql实战数据库字段类型讲解 字符串类型]

MySQL常见的字符串类型有:

CHAR,VARCHAR,BINARY,BLOB,TEXT,ENUM,SET

不同的类型在业务涉及.数据库性能的表现完全不同,今天主要深入了解

CHAR,VARCHAR

CHAR 和 CARCHAR的定义:

CHAR(N) 用来保存固定长度的字符,N的范围是0~255,请牢记 ,N表示的是字符 不是字节。 VARCHAR(N) 用来保存变长字符,N的范围为0~65536,N表示字节。

在超出65536个字节的情况下,可以考虑使用更大的字符类型TEXT 或 BLOB,两者最大的存储长度为4G,其区别是BLOB没有字符集属性,纯属二进制存储

绝大部分场景使用VARCHAR就足够了！

字符集

在表结构定义为CHAR 和 VARCHAR 用以存储字符以外,还需要额外定义字符，对应的字符集,因为每种字符在不同字符集编码下,对应不同的二进制值。常见字符集GBK,UTF8，通常我们默认设置为UTF8,
但是推荐字符集为UTF8MB4,因为有些的图片表情无法在UTF8下存储,emoji表情,
在我其他文章也写到过,例如微信授权登录, 当我们获取微信名称的时候,很多朋友的微信不止是中文,还带有小表情格式,然而这里就需要用到utf8MB4的格式

若强行在字符集UTF8下插入表情,就比如QQ的那些微笑表情;
就会报错:

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

设置格式方法很多:

[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
(这里 的CHAR 和 VARCHAR 没有执行快慢之分)

排序规则:

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

正确修改命令 ALTER TABLE…CONVERT TO…

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)

表设计实战应用:

大多数的时候 我们大家都喜欢用int去 来区分性别的状态,例如0,1
尤其是 使用tinyiint 去表示状态 ,
tinyint 会出现脏数据:

• 当tinyint 的长度为1的时候 会出现脏数据,返回BOOlean类型的ture/false
• 而且脏数据 还能出现 2。3.4其他的数值,后期清理代价太大了

这里修改的方法是:

1. 调整长度为4 就变好了 或者在sql查询的时候修改;

在MYsql8.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

自从在,MYsql8.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.

用户密码安全设计

我不废话了:直接说安全设计，单纯的MD5 也会出现 被暴力破解的风险;

所以在密码存储的使用上,还需要加盐(salt)

每个公司的盐值都是不同的;因此计算的值也不同

比如盐值为psalt则密码123456在数据库的值为:

password=MD5(psalt123456)

上面的级别还不够 还得升级;

动态盐+非固定的加密算法;

$salt$cryption_algorithm$value

• $salt：表示动态盐，每次用户注册时业务产生不同的盐值，并存储在数据库中。若做得再精细一点，可以动态盐值 + 用户注册日期合并为一个更为动态的盐值。
• $cryption_algorithm：表示加密的算法，如 v1 表示 MD5 加密算法，v2 表示 AES256 加密算法，v3 表示 AES512 加密算法等。
• $value：表示加密后的字符串。

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

总结:

字符串是使用最为广泛的数据类型之一，但也是设计最初容易犯错的部分，后期业务跑起来再进行修改，代价将会非常巨大。希望你能反复细读本讲的内容，从而在表结构设计伊始，业务就做好最为充分的准备。我总结下本节的重点内容：

• CHAR 和 VARCHAR 虽然分别用于存储定长和变长字符，但对于变长字符集（如
  GBK、UTF8MB4），其本质是一样的，都是变长，设计时完全可以用 VARCHAR 替代 CHAR；
• 推荐 MySQL 字符集默认设置为 UTF8MB4，可以用于存储 emoji 等扩展字符；
• 排序规则很重要，用于字符的比较和排序，但大部分场景不需要用区分大小写的排序规则；
• 修改表中已有列的字符集，使用命令 ALTER TABLE … CONVERT TO …；
• 用户性别，运行状态等有限值的列，MySQL 8.0.16 版本直接使用 CHECK 约束机制，之前的版本可使用 ENUM
  枚举字符串类型，外加 SQL_MODE 的严格模式；
• 业务隐私信息，如密码、手机、信用卡等信息，需要加密。切记简单的MD5算法是可以进行暴力破解，并不安全，推荐使用动态盐+动态加密算法进行隐私数据的存储。