Mysql学习-第一章（表设计数字类型）

表设计

数字类型

整数类型

其中关键字signed表示有符号，unsigned表示无符号（自然数），

建议不要可以使用unsigned

原因当设计数据表字段的时候使用unsigned，当分析某条数据的时候，两数相减可能会抛出错误，值有可能小于0.

浮点数类型和高精度类型

MySQL 之前的版本中存在浮点类型 Float 和 Double，但这些类型因为不是高精度，也不是 SQL 标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。

数字类型中的高精度 DECIMAL 类型可以使用，当声明该类型列时，可以（并且通常必须要）指定精度和标度。

`salary` decimal(8,2)

8 是精度（精度表示保存值的主要位数），2 是标度（标度表示小数点后面保存的位数）。通常在表结构设计中，类型 DECIMAL 可以用来表示用户的工资、账户的余额等精确到小数点后 2 位的业务。

资金字段设计

然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 INT 整型类型

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

金额字段的取值范围如果用 DECIMAL 表示的，如何定义长度呢？因为类型 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。

用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。

另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIG INT 来存储金额相关的字段。

字段存储时采用分存储，即便这样 BIG INT 也能存储千兆级别的金额。这里，1兆 = 1万亿。

这样的好处是，所有金额相关字段都是定长字段，占用 8 个字节，存储高效。另一点，直接通过整型计算，效率更高。

注意，在数据库设计中，我们非常强调定长存储，因为定长存储的性能更好。

为什么强调定长

数据库中的记录的存储方式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KC8mc7Vd-1621404288664)(https://s0.lgstatic.com/i/image6/M01/3B/89/CioPOWCFSRCAAVQuAACRlghH4RE916.png)]

若发生更新，记录 1 原先的空间无法容纳更新后记录 1 的存储空间，因此，这时数据库会将记录 1 标记为删除，寻找新的空间给记录1使用，如：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0pG6A0DU-1621404288666)(https://s0.lgstatic.com/i/image6/M00/3B/89/CioPOWCFSRaAW5QcAACeeklGwXQ436.png)]

上图中*记录 1 表示的就是原先记录 1 占用的空间，而这个空间后续将变成碎片空间，无法继续使用，除非人为地进行表空间的碎片整理。

那么，当使用 BIG INT 存储金额字段的时候，如何表示小数点中的数据呢？其实，这部分完全可以交由前端进行处理并展示。作为数据库本身，只要按分进行存储即可。

业务表结构设计

整数类型与自增设计

整型结合属性 auto_increment，可以实现自增功能，但在表结构设计时用自增做主键

两个要求

• 用 BIGINT 做主键，而不是 INT；

为什么？

用自增整型做主键，一律使用 BIGINT，而不是 INT。不要为了节省 4 个字节使用 INT，当达到上限时，再进行表结构的变更，将是巨大的负担与痛苦。

当达到 INT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1。

验证代码：

create table t(
 a INT auto_increment PRIMARY KEY);
INSERT into t values(2147483647);
INSERT INTO t VALUES(null);

• 自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）。

验证代码（在5.6.24-log版本验证通过）

mysql> SELECT * FROM t;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.01 sec)

mysql> DELETE FROM t WHERE a = 3;
Query OK, 1 row affected (0.02 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec

在8.0.22上已经解决。

• 升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；

• 若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键。

在删除自增为 3 的这条记录后，下一个自增值依然为 4（AUTO_INCREMENT=4），这里并没有错误，自增并不会进行回溯。但若这时数据库发生重启，那数据库启动后，表 t 的自增起始值将再次变为 3，即自增值发生回溯。

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 s

知识点来自学习-姜承尧老师拉钩网教导内容。