首页 » MySQL/TiDB/GoldenDB » Differences between MySQL AUTO_INCREMENT 5.7 VS 8.0

Differences between MySQL AUTO_INCREMENT 5.7 VS 8.0

  • 2022/06/17
  • MySQL/TiDB/GoldenDB
  • 207 views
  • Differences between MySQL AUTO_INCREMENT 5.7 VS 8.0已关闭评论

AUTO_INCREMENT机制用于 InnoDB表， AUTO_INCREMENT必须将列定义为某个索引的第一列或唯一列，可以对表执行等效的索引 SELECT MAX(ai_col) 查找以获得最大列值。上AUTO_INCREMENT列h索引没有要求必须是是 PRIMARY KEY 或 UNIQUE，但为了避免 列中的重复值，建议使用这些索引类型。在一些情况下MySQL5.7中会产生重复值duplicate-key error, 8.0有所改变。

mysql> use anbob;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `tt` (
    ->   `a` int(11) NOT NULL AUTO_INCREMENT,
    ->   `b` tinyint(4) DEFAULT NULL
    -> ) ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> CREATE TABLE `tt` (
    ->   `a` int(11) NOT NULL AUTO_INCREMENT,
    ->   `b` tinyint(4) DEFAULT NULL,
    ->   index(a)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> show create table tt \g                                         
+-------+----------------------------------------------------------------+
| Table | Create Table                                                   |
+-------+----------------------------------------------------------------+
| tt    | CREATE TABLE `tt` (                                          
  `a` int NOT NULL AUTO_INCREMENT,                                     
  `b` tinyint DEFAULT NULL,                                            
  PRIMARY KEY (`a`)                                                    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |   
+-------+----------------------------------------------------------------+
1 row in set (0.00 sec)                                                
																	
mysql> desc tt;                                                        
+-------+---------+------+-----+---------+----------------+            
| Field | Type    | Null | Key | Default | Extra          |            
+-------+---------+------+-----+---------+----------------+            
| a     | int     | NO   | PRI | NULL    | auto_increment |            
| b     | tinyint | YES  |     | NULL    |                |            
+-------+---------+------+-----+---------+----------------+            
2 rows in set (0.00 sec)

# MySQL 5.7

This section describes how InnoDB initializes AUTO_INCREMENT counters.

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

# MySQL 8.0
在 MySQL 5.7 及更早版本中，修改 INSERT 语句序列中间的 AUTO_INCREMENT 列值可能会导致“重复条目”错误。例如，如果您执行的 UPDATE 操作将 AUTO_INCREMENT 列值更改为大于当前最大自动增量值的值，则未指定未使用的自动增量值的后续 INSERT 操作可能会遇到“重复条目”错误。在 MySQL 8.0 及更高版本中，如果您将 AUTO_INCREMENT 列值修改为大于当前最大自动增量值的值，则新值将被持久化，并且后续 INSERT 操作会从新的更大值开始分配自动增量值。

The current maximum auto-increment counter value is now written to the redo log each time the value changes, and saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

在服务器正常关闭后重新启动时，InnoDB 使用存储在数据字典中的当前最大自动增量值初始化内存中的自动增量计数器。InnoDB 使用存储在数据字典中的当前最大自动增量值初始化内存中的自动增量计数器，并扫描redo log 以查找自上一个检查点以来写入的自动增量计数器值。如果redo log的值大于内存中的计数器值，则应用redo log的值。但是，在服务器意外退出的情况下，无法保证重用先前分配的自动增量值。每次由于 INSERT 或 UPDATE 操作而改变当前最大自增值时，都会将新值写入重做日志，但如果在重做日志刷新到磁盘之前发生意外退出，则先前分配的值可能是在服务器重新启动后初始化自动增量计数器时重用。

在 MySQL 5.7 和更早版本中，在 ROLLBACK 操作之后立即重新启动服务器可能会导致重用先前分配给回滚事务的自动增量值，从而有效地回滚当前最大的自动增量值。在 MySQL 8.0 中，当前最大的自动增量值被持久化，防止重复使用以前分配的值。

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：