- 什么是AUTO_INCREMENT
在我们的使用中,AUTO_INCREMENT主要应用于主键,目的是生成唯一且连续的主键(自增主键)
为什么要唯一?唯一ID呀,就像身份证号一样,一条数据一个号,好找
为什么要连续?这个我会再写一篇文档解释,立个flag,这周五发布
-
AUTO_INCREMENT 的相关配置
auto_increment_increment 步长
auto_increment_offset 初始值
show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
- AUTO_INCREMENT的初始化
当你在innodb的表中指定一列为AUTO_INCREMENT时,则内存中会创建一个计数器,MySQL 5.7 及更早版本中,自动递增计数器存储在主内存中,而不是磁盘上。要在服务器重新启动后初始化自动递增计数器,InnoDB
将在第一次插入包含AUTO_INCREMENT
列的表时执行以下等效语句 。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
在 MySQL 8.0 中。当前最大自动递增计数器值在每次更改时写入redo log并保存到每个检查点的数据字典中。这个改变使当前最大递增计数器值在服务器重新启动时保持不变。
在正常关闭后重新启动服务器时, InnoDB
使用存储在数据字典中的当前最大自动增量值初始化内存中自动增量计数器。
在崩溃恢复服务器重新启动时, InnoDB
使用存储在数据字典中的当前最大自动增量值初始化内存中计数器,并扫描 redo log 以查找上次检查点写入的自动增量计数器值。如果 redo log 值大于内存计数器值,则应用 redo log 值。但是,在服务器意外退出的情况下,无法保证不重复使用先前分配的自动增量值。每次当前最大自动增量值由于INSERT 或 UPDATE操作,新值写入redo log ,但如果在 redo log 刷新到磁盘之前发生意外退出,则可以在服务器重新启动后初始化自增计数器时重新使用先前分配的值。
InnoDB
使用(SELECT MAX(ai_col) FROM table_name
FOR UPDATE)初始化自动递增计数器的唯一情况是在导入没有元数据文件的表时否则,从元数据文件中读取当前最大自动递增计数器值(如果存在)。除了计数器值初始化之外,当尝试使用以下语句将计数器值设置为小于或等于持久计数器值时,语句的等效项 用于确定表的当前最大自动递增计数器值SELECT MAX(ai_col) FROM
table_name
FOR UPDATE.cfg
.cfg
SELECT MAX(ai_col) FROM
table_name
ALTER TABLE ... AUTO_INCREMENT =
陈述。例如,您可能会在删除某些记录后尝试将计数器值设置为较小的值。在这种情况下,必须查表以确保新的计数器值不小于或等于当前实际的最大计数器值。N
FOR UPDATE
- AUTO_INCREMENT的值
通过 show create table 表名
show create table t;
+-------+-----------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------+
| t | CREATE TABLE `t` ( |
`id` int NOT NULL AUTO_INCREMENT,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=67
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
+-------+-----------------------------------------------------------+
可以看到当前的 AUTO_INCREMENT 是67 也就是下一条数据的id为67
- AUTO_INCREMENT的赋值
执行 INSERT 语句时 形如 insert into t(id) values( X ); 对AUTO_INCREMENT的影响
输入值 | 实际ID | AUTO_INCREMENT |
---|---|---|
0或null或未指定 | AUTO_INCREMENT | AUTO_INCREMENT+ auto_increment_offset |
X >= AUTO_INCREMENT | X | X + auto_increment_offset |
X< AUTO_INCREMENT | X | AUTO_INCREMENT |