在真实业务场景中,整型类型
最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量,或电商中的库存数量、购买次数等。在业务中,整型类型的另一个常见且重要的使用用法是作为表的主键
,即用来唯一标识一行数据。
整型结合属性 auto_increment,可以实现自增功能
,但在表结构设计时用自增做主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:
用 BIGINT 做主键,而不是 INT;
自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。
从表 1 可以发现,INT 的范围最大在 42 亿
的级别,在真实的互联网业务场景的应用中,很容易达到最大值。例如一些流水表、日志表,每天 1000W 数据量,420 天后,INT 类型的上限即可达到。
因此,(敲黑板 1)用自增整型做主键,一律使用 BIGINT,而不是 INT
。不要为了节省 4 个字节使用 INT,当达到上限时,再进行表结构的变更,将是巨大的负担与痛苦。
那这里又引申出一个有意思的问题:如果达到了 INT 类型的上限,数据库的表现又将如何呢?是会重新变为 1?我们可以通过下面的 SQL 语句验证一下:
CREATE TABLE t (
a INT AUTO_INCREMENT PRIMARY KEY
);
mysql> INSERT INTO t VALUES (2147483647);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t VALUES (NULL);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY'
可以看到,当达到 INT 上限后,再次进行自增插入时,会报重复错误,MySQL 数据库并不会自动将其重置为 1。
第二个特别要注意的问题是,(敲黑板 2)MySQL 8.0
版本前,自增不持久化,自增值可能会存在回溯问题!
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
可以看到,在删除自增为 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
若要彻底解决这个问题,有以下 2 种方法:
升级 MySQL 版本到 8.0 版本,每张表的自增值会持久化;
若无法升级数据库版本,则强烈不推荐在核心业务表中使用自增数据类型做主键
。
其实,在海量互联网架构设计过程中,为了之后更好的分布式架构扩展性,不建议使用整型类型做主键,更为推荐的是字符串类型。
总结
- 自增主键要用bigint
- 自增主键最好用字符串类型