如何使InnoDB表在服务器重新启动时不重置自动增量? [英] How to make InnoDB table not reset autoincrement on server restart?

问题描述

我的开发机器上正在运行MySQL 5.5.37. 我使用innodb表.面临下一个问题-服务器重新启动后，自动增量将重置.

I have MySQL 5.5.37 running on my development machine. I use innodb tables. Faced next problem - autoincrement resets after server restart.

找到 autoinc_lock_mode ，设置为0 ，但没有帮助.

Found autoinc_lock_mode, set to 0, but did not helped.

SHOW VARIABLES命令显示autoinc_lock_mode的值为0.

SHOW VARIABLES command shows value 0 for autoinc_lock_mode.

我做什么:

select max(id) from tablex; // 11, autoincrement is 12
insert into tablex values ('foo');
select max(id) from tablex; // 12, autoincrement is 13
delete from tablex where id > 11; // autoincrement is 13

然后我重新启动服务器...和....(鼓卷)

Then I restart server... and .... (drum roll)

show create table tablex; // autoincrement 12 instead of 13

我做错了什么? :(

//UPD

我必须使用MyISAM表.谢谢大家的答复/评论.

I have to use MyISAM table. Thanks you all for replies/comments.

推荐答案

在InnoDB中，自动增量值不属于表元数据的一部分，并且在每次服务器重新启动时重置.

In InnoDB, the autoincrement value is not part of the table's metadata and is reset on each server restart.

InnoDB使用以下算法初始化自动增量 表t的计数器，该表t包含名为AUTO_INCREMENT的列 ai_col:服务器启动后，对于第一次插入表t中， InnoDB执行以下语句的等效项:

InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE

; InnoDB增加值 由该语句检索并将其分配给该列和 表格的自动递增计数器.默认情况下，值为 加一.该默认值可以被 auto_increment_increment配置设置.

; InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by one. This default can be overridden by the auto_increment_increment configuration setting.

如果表为空，则InnoDB使用值1.默认值可以是 被auto_increment_offset配置设置覆盖.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

在服务器可执行文件生存期内，它以特殊的结构存储，但在服务器重新启动之间不存在:

During the server executable lifetime, it's stored in a special structure but not persisted between server restarts:

如果为InnoDB表指定AUTO_INCREMENT列，则该表 InnoDB数据字典中的句柄包含一个特殊的计数器，称为 自动增量计数器，用于为分配新值 列.该计数器仅存储在主存储器中，而不存储在磁盘上.

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.

您要设置的变量定义了不同会话同时访问此结构的方式，而不是其生存期.

The variable you're setting defines the way this structure is accessed concurrently by different sessions, not its lifetime.

如果要在服务器重新启动之间保留自动增量值，则应在每次写入时将其保存在不受事务控制的表中(例如MyISAM或Archive)，并从服务器重启.

If you want to persist an auto-increment value between the server restarts, you should save it in a table not subject to transaction control (like MyISAM or Archive) on each write and reset it from the table on server restart.

这篇关于如何使InnoDB表在服务器重新启动时不重置自动增量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！