山东富国建设投资有限公司网站,橙米网站建设,网站开发选题申请理由,做网站设计需要学什么MySQL 8.0 新特性之自增变量持久化 文章目录 MySQL 8.0 新特性之自增变量持久化MySQL 5.7 vs 8.0 测试对比MySQL 5.7MySQL 8.0 参考资料 MySQL 8.0 中支持自增变量持久化#xff0c;实际也是解决之前版本中存在的自增主键重启重置的 BUG 问题#xff08;
BUG #199#xff1…MySQL 8.0 新特性之自增变量持久化 文章目录 MySQL 8.0 新特性之自增变量持久化MySQL 5.7 vs 8.0 测试对比MySQL 5.7MySQL 8.0 参考资料 MySQL 8.0 中支持自增变量持久化实际也是解决之前版本中存在的自增主键重启重置的 BUG 问题
BUG #199Innodb autoincrement stats los on restart 。 MySQL 8.0 开始当前最大的自增计数器每当发生变化值会被写入 redo log 中并在每个检查点时保存在 engine-private system table 中对 AUTO_INCREMENT 值进行持久化MySQL 重启后该值也不会改变。
MySQL 5.7 vs 8.0 测试对比
MySQL 5.7
# MySQL 5.7
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 4 | 2 | 11 | 22 |
----------------------
4 rows in set (0.01 sec)# 对应自增值AUTO_INCREMENT为 5
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT,c1 int(11) DEFAULT NULL,c2 int(11) DEFAULT NULL,c3 int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)# 修改主键值
mysql update t2 set id5 where id4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 5 | 2 | 11 | 22 |
----------------------
4 rows in set (0.00 sec)# 自增值AUTO_INCREMENT未发生变化
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT,c1 int(11) DEFAULT NULL,c2 int(11) DEFAULT NULL,c3 int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)# 插入数据此时报错“主键冲突”
mysql insert into t2 (c1,c2,c3) values (3,11,12);
ERROR 1062 (23000): Duplicate entry 5 for key PRIMARY# 再次查看自增值AUTO_INCREMENT已变为 6
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT,c1 int(11) DEFAULT NULL,c2 int(11) DEFAULT NULL,c3 int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)# 测试再次插入数据此时正常
mysql insert into t2 (c1,c2,c3) values (3,11,12);
Query OK, 1 row affected (0.00 sec)
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 5 | 2 | 11 | 22 |
| 6 | 3 | 11 | 12 |
----------------------
5 rows in set (0.00 sec)# 自增值AUTO_INCREMENT此时变为 7
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT,c1 int(11) DEFAULT NULL,c2 int(11) DEFAULT NULL,c3 int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)MySQL 8.0
# MySQL 8.0
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 4 | 2 | 11 | 22 |
----------------------
4 rows in set (0.01 sec)# 对应自增值AUTO_INCREMENT为 5
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int NOT NULL AUTO_INCREMENT,c1 int DEFAULT NULL,c2 int DEFAULT NULL,c3 int DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.01 sec)# 修改主键值
mysql update t2 set id5 where id4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 5 | 2 | 11 | 22 |
----------------------
4 rows in set (0.00 sec)# 查看自增值AUTO_INCREMENT已变为 6
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int NOT NULL AUTO_INCREMENT,c1 int DEFAULT NULL,c2 int DEFAULT NULL,c3 int DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)# 再次修改主键值
mysql update t2 set id8 where id5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 8 | 2 | 11 | 22 |
----------------------
4 rows in set (0.00 sec)# 查看自增值AUTO_INCREMENT已变为 9
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int NOT NULL AUTO_INCREMENT,c1 int DEFAULT NULL,c2 int DEFAULT NULL,c3 int DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT9 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)# 插入数据正常
mysql insert into t2 (c1,c2,c3) values (3,11,12);
Query OK, 1 row affected (0.00 sec)
mysql select * from t2;
----------------------
| id | c1 | c2 | c3 |
----------------------
| 1 | 1 | 11 | 22 |
| 2 | 2 | 22 | 33 |
| 3 | 2 | 22 | 25 |
| 8 | 2 | 11 | 22 |
| 9 | 3 | 11 | 12 |
----------------------
5 rows in set (0.00 sec)# 查看自增值AUTO_INCREMENT正常变为 10
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int NOT NULL AUTO_INCREMENT,c1 int DEFAULT NULL,c2 int DEFAULT NULL,c3 int DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT10 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.01 sec)参考资料
【1】AUTO_INCREMENT Handling in InnoDB
