问题描述
我的开发机器上正在运行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.
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:
您要设置的变量定义了不同会话同时访问此结构的方式，而不是其生存期.
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表在服务器重新启动时不重置自动增量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！