MySQL进阶：表结构设计

2021-05-30 本文已影响0人 xiaogp

摘要：MySQL

表结构设计是MySQL架构设计最为基础的工作，优化表结构设计是开发规范，避免后期因为表结构设计不合理导致的付出巨大调整代价

数字类型

对于数字类型的表结构设计总结

不推荐使用整型类型的属性 Unsigned，若非要使用，参数 sql_mode 务必额外添加上选项 NO_UNSIGNED_SUBTRACTION；
自增整型类型做主键，超过了类型的最大值会报错主键重复插入
自增整型类型做主键，可以修改为Unsigned，这样可以使得取值范围扩大为原来的2倍；
自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大；
MySQL 8.0 版本前，自增整型不能持久化，如果宕机会有回溯问题
当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
不要再使用浮点类型 Float、Double，建议使用DECIMAL，MySQL 后续版本将不再支持上述两种类型；
账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑。

（1）整数类型

MySQL 数据库支持 SQL 标准支持的整型类型：INT、SMALLINT。此外，MySQL 数据库也支持诸如 TINYINT、MEDIUMINT 和 BIGINT 整型类型（表 1 显示了各种整型所占用的存储空间及取值范围）

在整型类型中，有 signed 和unsigned 属性，其表示的是整型的取值范围，默认为 signed

signed：带符号，数值可以是正负和零
unsigned：不带符号，数值是大于等于0

两者的取值的范围极差是一致的，signed相当于把unsigned截取了一半放到了负数上，对于像年龄，成绩等必定为非负数的数值可以设置为unsigned，支持零和正整数，例如

mysql> CREATE TABLE `student` (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   age INT unsigned DEFAULT NULL
    -> ) ;
Query OK, 0 rows affected (0.86 sec)

插入非负数正确，插入-1报错

mysql> INSERT `student` (`age`) VALUES (0);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT `student` (`age`) VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'age' at row 1

但是使用unsigned之后会出现一个问题，即当需要对unsigned进行数据分析计算时，如果计算结果存在负数则会报错out of range，即MySQL 要求 unsigned 数值相减之后依然为 unsigned，否则就会报错

mysql> select id, age - 10 from student;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`student`.`age` - 10)'

如果计算结果不违背非负数则能正常运行

mysql> select id, age +10 from student;
+----+---------+
| id | age +10 |
+----+---------+
|  1 |      10 |
|  2 |      60 |
+----+---------+

为了避免这个错误，需要对数据库参数 sql_mode 设置为NO_UNSIGNED_SUBTRACTION，允许相减的结果为 signed，这样才能得到最终想要的结果

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select id, age - 10 from student;
+----+----------+
| id | age - 10 |
+----+----------+
|  1 |      -10 |
|  2 |       40 |
+----+----------+

在开发过程中不建议刻意使用unsigned，因为他降低了容错率，在进行插入和分析时限制为非负数，可能导致输出结果报错

（2）浮点类型和高精度型

MySQL 之前的版本中存在浮点类型Float 和Double，但这些类型因为不是高精度，也不是 SQL 标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型，而数字类型中的高精度 DECIMAL 类型可以使用，当声明该类型列时，可以（并且通常必须要）指定精度和标度，比如

mysql> CREATE TABLE `student` (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   salary DECIMAL(8, 2) DEFAULT NULL
    -> ) ;
Query OK, 0 rows affected (0.24 sec)

插入数据查看结果，可见MySQL将小数保留到decimal指定的标度，保留小数点后2位，精度表示所有数值的最大个数，如果精度为8，标度为2，则小数点前的数字个数为最大6个，小数点后面的数字如果超过2个则自动截断，即存储的小数范围是-999999.99到999999.99

mysql> INSERT `student` (`salary`) VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT `student` (`salary`) VALUES (1.2345);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT `student` (`salary`) VALUES (123456789.2345);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> INSERT `student` (`salary`) VALUES (123456.23);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT `student` (`salary`) VALUES (-3);
Query OK, 1 row affected (0.04 sec)

mysql> select * from student;
+----+-----------+
| id | salary    |
+----+-----------+
|  1 |      1.00 |
|  2 |      1.23 |
|  3 | 123456.23 |
|  4 |     -3.00 |
+----+-----------+
4 rows in set (0.00 sec)

DECIMAL通常用于保留准确精确度的列，例如会计系统中的货币数据

（3）整型类型与自增设计

整型类型的另一个常见且重要的使用用法是作为表的主键，即用来唯一标识一行数据。整型结合属性 auto_increment，可以实现自增功能，其中需要注意

用 BIGINT 做主键，而不是 INT；
自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）

INT 的范围最大在 42 亿的级别（如果设置成unsigned），在真实的互联网业务场景的应用中，很容易达到最大值。例如一些流水表、日志表，每天 1000W 数据量，420 天后，INT 类型的上限即可达到。因此用自增整型做主键，一律使用 BIGINT，而不是 INT。不要为了节省 4 个字节使用 INT，对INT自增做测试如下

mysql> CREATE TABLE `student` (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(100) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.25 sec)

插入INT最大自增值，此时再插入一条数据报错主键重复输入

mysql> INSERT `student` (name) VALUES ('xgp');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT `student` (id, name) VALUES (2147483647, 'gp');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT `student` (name) VALUES ('wf');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

默认的INT AUTO_INCREMENT是从1开始的，并且是signed模式，最大值是2147483647

mysql> select * from student;l
+------------+------+
| id         | name |
+------------+------+
|          1 | xgp  |
| 2147483647 | gp   |
+------------+------+

可以将AUTO_INCREMENT和UNSIGNED连用，这样可以扩大取值范围，默认还是从1开始，初始值是AUTO_INCREMENT自身的一个参数

mysql> CREATE TABLE `student` (
    ->   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(100) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> INSERT `student` (name) VALUES ('xgp');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT `student` (id, name) VALUES (2147483647, 'gp');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT `student` (name) VALUES ('wf');
Query OK, 1 row affected (0.04 sec)

mysql> select * from student;
+------------+------+
| id         | name |
+------------+------+
|          1 | xgp  |
| 2147483647 | gp   |
| 2147483648 | wf   |
+------------+------+

UNSIGNED的INT最大值是4294967295，超过了报错主键重复插入

mysql> INSERT `student` (id, name) VALUES (4294967295, 'gp2');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT `student` (name) VALUES ('wf2');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

正确的使用BIGINT + UNSIGNED做自增主键的例子如下，最大值是18446744兆亿

CREATE TABLE `student` (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) DEFAULT NULL
);

MySQL 8.0 版本前，自增不持久化，自增值可能会存在回溯问题，是指MySQL记录的自增值不会持久化到磁盘，因此当发生宕机时，自增值可能回到之前的值，测试如下

mysql> CREATE TABLE `student` (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   salary DECIMAL(8, 2) DEFAULT NULL
    -> ) ;
Query OK, 0 rows affected (0.24 sec)

再插入数据，同时使用create table的AUTO_INCREMENT查看下一个数据的自增主键值

mysql> INSERT `student` (`salary`) VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT `student` (`salary`) VALUES (1.2345);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT `student` (`salary`) VALUES (123456.23);
Query OK, 1 row affected (0.04 sec)

mysql> show create table `student`;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                      |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salary` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-----------+
| id | salary    |
+----+-----------+
|  1 |      1.00 |
|  2 |      1.23 |
|  3 | 123456.23 |
+----+-----------+
3 rows in set (0.00 sec)

当下已插入3条数据，下一个数据的自增值是4，此时删除第3条数据，自增值还是4

mysql> delete from student where id = 3;
Query OK, 1 row affected (0.09 sec)

mysql> show create table `student`;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                      |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salary` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时如果从后台kill掉MySQL进程，再次打开MySQL查看表的自增值，重新变为3

mysql> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                      |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salary` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

若要彻底解决这个问题，需要升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化，否则强烈不推荐在核心业务表中使用自增数据类型做主键，在海量互联网架构设计过程中，为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型

（4）资金字段设计

在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为整型类型。也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。如1元在数据库中用整型类型 100 存储。原因如下

DECIMAL长度不好统一：类型 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一
DECIMAL计算效率低：类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效

推荐使用 BIG INT 来存储金额相关的字段。字段存储时采用分存储，即便这样 BIG INT 也能存储千兆级别的金额。这样的好处是，所有金额相关字段都是定长字段，占用 8 个字节，存储高效。另一点，直接通过整型计算，效率更高。在数据库设计中，定长存储的性能更好

字符串类型

对于字符串类型字段的表结构设计总结

CHAR 和 VARCHAR 虽然分别用于存储定长和变长字符，但对于变长字符集（如 GBK、UTF8MB4），其本质是一样的，都是变长，设计时完全可以用 VARCHAR 替代 CHAR；
CHAR和VARCHAR后面的N都是字符个数，不是字节；
推荐 MySQL 字符集默认设置为 UTF8MB4，可以用于存储 emoji 等扩展字符；
用于字符的比较和排序，MySQL默认的字符集不区分大小写，但大部分场景不需要用区分大小写的排序规则；
修改表中已有列的字符集，使用命令 ALTER TABLE ... CONVERT TO ....；
用户性别，运行状态等有限值的列，MySQL 8.0.16 版本直接使用 CHECK 约束机制，之前的版本可使用 ENUM 枚举字符串类型，外加 SQL_MODE 的严格模式；

（1）CHAR 和 VARCHAR 的定义

MySQL 数据库的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的类型在业务设计、数据库性能方面的表现完全不同，其中最常使用的是 CHAR、VARCHAR

CHAR(N) ：用来保存固定长度的字符，N 的范围是 0 ~ 255，N 表示的是字符，而不是字节。
VARCHAR(N) ：用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符
CHAR和VARCHAR的区别：一般定长的数据选用char类型，比如身份证号，手机号，电话等，长度变化很大的可以使用varchar类型
TEXT 或 BLOB：在超出 65536 个字符的情况下，可以考虑使用更大的字符类型，两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储

CHAR和VARCHAR的N都是字符数量，如

mysql> CREATE TABLE `student` (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(4)  DEFAULT NULL,
    ->   phone CHAR(11) DEFAULT NULL
    -> ) ;
Query OK, 0 rows affected (0.25 sec)

插入数据测试，可见不论中文字符串还是英文字符串，都是算一个字符数，超过N则报错too long

mysql> INSERT `student` (`name`, `phone`) VALUES ('wbcv', '13852517263');
Query OK, 1 row affected (1.08 sec)

mysql> INSERT `student` (`name`, `phone`) VALUES ('我是什么', '13852517262');
Query OK, 1 row affected (0.16 sec)

mysql> INSERT `student` (`name`, `phone`) VALUES ('我是什么吗', '13852517265');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> INSERT `student` (`name`, `phone`) VALUES ('我是什么', '1385251726');
Query OK, 1 row affected (0.53 sec)

mysql> INSERT `student` (`name`, `phone`) VALUES ('wbcvx', '13852517263');
ERROR 1406 (22001): Data too long for column 'name' at row 1

MySQL 数据库的 VARCHAR 字符类型，最大能够存储 65536 个字符，所以在 MySQL 数据库下，绝大部分场景使用类型 VARCHAR 就足够了

（2）字符集

MySQL中有utf8和utf8mb4两种编码，在MySQL中推荐永远使用utf8mb4而不是utf8，这是mysql的一个遗留问题，MySQL中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行，包括 MySQL 8.0 版本在内，字符集默认设置成 UTF8MB4，8.0 版本之前默认的字符集为 Latin1
可以在建表时在最后加上表的字符集

mysql> CREATE TABLE `student` (
    ->   id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(100) DEFAULT NULL
    -> ) charset=utf8mb4;
Query OK, 0 rows affected (0.27 sec)

也可以在创建数据库的时候就指定库下所有表的默认字符集

mysql> create database test2 default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

对于已经指定字符集的表，可以通过CONVERT TO CHARSET语句修改

mysql> ALTER TABLE student CONVERT TO CHARSET utf8mb4;
Query OK, 0 rows affected (0.70 sec)

（3）排序规则

每个字符集都有对应的排序规则（Collation），utf8mb4默认对应的排序规则是utf8mb4_general_ci

mysql> SHOW CHARSET LIKE 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

排序规则以 _ci 结尾，表示不区分大小写（Case Insentive），_cs 表示大小写敏感，_bin 表示通过存储字符的二进制进行比较。需要注意的是，比较 MySQL 字符串，默认采用不区分大小的排序规则，MySQL在做大小比较，排序，去重的时候对于字符串的情况不考虑大小写

mysql> select * from student where name = 'a';
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | A    |
+----+------+

绝大部分业务的表结构设计无须设置排序规则为大小写敏感

（4）使用枚举类型处理固定选项值的字段

对于取值是固定选项的字段，比如性别，可以采用数值类型比如tinyint但这样设计问题比较明显

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

推荐使用ENUM字符串枚举类型，如果插入值不在ENUM的范围则会报错截断该条记录全部删除

mysql> CREATE TABLE `student` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `sex` ENUM('M', 'F'),
    ->   `name` VARCHAR(100)
    -> );

mysql> INSERT INTO student (sex, name) VALUES ('F', 'gp');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO student (sex, name) VALUES ('M', 'wf');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO student (sex, name) VALUES ('G', 'slj');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> select * from student;
+----+------+------+
| id | sex  | name |
+----+------+------+
|  1 | F    | gp   |
|  2 | M    | wf   |
+----+------+------+

参考

姜承尧的MySQL实战宝典 https://kaiwu.lagou.com/course/courseInfo.htm?courseId=869#/content
MYSQL中的COLLATE是什么？ https://www.jianshu.com/p/f8707b8461d3