缓慢的自动增量重置 [英] Slow auto_increment reset

问题描述

我有很多表，由于某些原因，我需要在应用程序启动时为此表调整自动增量值.

I have a lot of tables and because of some reasons I need to adjust auto increment value for this tables on application startup.

我尝试这样做:

mysql> select max(id) from item;
+----------+
| max(id)  |
+----------+
| 97972232 |
+----------+
1 row in set (0.05 sec)

mysql> alter table item auto_increment=1097972232;

在另一个会话中:

afrolov@A1-DB1:~$ mysql -u root -e "show processlist" | grep auto_increment
472196  root    localhost       test    Query   39      copy to tmp table       alter table item auto_increment=1097972232

MySQL开始重建表！为什么MySQL需要这样做?如何在调整auto_increment值的同时避免重建巨大的表?

MySQL is starting to rebuild table! Why MySQL need to do it? How can I avoid rebuilding huge tables while adjusting auto_increment value?

MySQL 5.0，InnoDB.
表格定义:

MySQL 5.0, InnoDB.
Table definition:

 CREATE TABLE `item` (
      `id` bigint(20) NOT NULL auto_increment,
      `item_res_id` int(11) NOT NULL default '0',
      `stack_count` int(11) NOT NULL default '0',
      `position` int(11) NOT NULL default '0',
      `place` varchar(15) NOT NULL default '',
      `counter` int(11) NOT NULL default '-1',
      `is_bound` tinyint(4) NOT NULL default '0',
      `remove_time` bigint(20) NOT NULL default '-1',
      `rune_res_id` int(11) default NULL,
      `rune_id` bigint(20) default NULL,
      `avatar_id` bigint(20) NOT NULL,
      `rune_slot_res_id` int(11) default NULL,
      `is_cursed` tinyint(4) NOT NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `avatar_id` (`avatar_id`,`place`,`position`),
      UNIQUE KEY `rune_id` (`rune_id`),
      KEY `idx_item_res_id` (`item_res_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=97972233 DEFAULT CHARSET=utf8;

关于我为什么要这样做.长话短说，我想解决关于在服务器重启时重置auto_increment值的mysql innodb问题.有时我们将表中的行复制到另一个表中，并且必须保持行ID不变.当我们向table1添加一行(例如，id = 1)，将行复制到table2，从table1删除行并重新启动MySQL时，那么当我们在table1中创建新的一行时，该行也将获得id = 1.因此，如果我们必须将行复制到table2中，则会得到唯一的约束违例.我们已经有很多代码，很难全部重写.调整自动增量值似乎是解决此问题的最简单方法.

About why I have to do this. Long story short i want to workaround mysql innodb issue about reseting auto_increment value on server restart. Sometimes we copy rows from our tables to another tables and we must keep rows id unchanged. When we add one row (with id=1 for example) to table1, copy row to table2 , delete row from table1 and restart MySQL, then when we create a new one row in table1 this row will get id=1 too. So if we will have to copy row to table2 we get unique constraint violation. We already have a lot of code and it will be hard to rewrite it all. Adjusting autoincrement value seems the easiest way to fix this problem.

已添加:

MySQL 5.5-完全相同:(

推荐答案

只需向每个表添加需要auto_increment_id-1的临时记录， 并在此之后快速轻松地删除记录，但可能太脏了

simply add a temporary record that has desired auto_increment_id-1 to each table, and remove the record after that, quick and easy, but probably too dirty

示例:

insert into item set id=1097972232-1;

执行后，下一个auto_increment将是1097972232，这就是您想要的

after the execution, the next auto_increment will be 1097972232, which is what you desired

这可以避免速度缓慢

这篇关于缓慢的自动增量重置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！