慢速自动增量重置

Question

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

我尝试这样做：

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 5.0、InnoDB。
表定义：

 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，我们就会违反唯一约束。我们已经有很多代码，很难全部重写。调整自动增量值似乎是解决此问题的最简单方法。

添加：

MySQL 5.5 - 都一样:(

Answer 1

只需将需要 auto_increment_id-1 的临时记录添加到每个表中，
然后删除记录，快速简单，但可能太脏了

示例：

insert into item set id=1097972232-1;

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

这可以避免缓慢

Answer 2

这是 MySQL 的记录“功能”：

如果您使用除 RENAME 之外的任何选项来 ALTER TABLE，MySQL 总是会创建一个临时表，即使数据并不严格需要复制（例如当您更改列名时）。对于MyISAM表，您可以通过将myisam_sort_buffer_size系统变量设置为较高的值来加速索引重新创建操作（这是更改过程中最慢的部分）。

http://dev.mysql.com/doc/refman/5.0 /en/alter-table.html

MySQL 5.1 和 5.5 支持更多一些带有临时表的 alter table 操作，但是更改 auto_increment 没有记录为其中之一。

无论如何，为什么需要更改 auto_increment 值？这不是你应该经常做的事情。

Answer 3

没有简单的方法可以绕过 MySQL 中的 AUTO_INCRMENT 属性默认行为，即使您找到了方法，我也不建议您这样做，因为这是遇到以下问题的最佳方法短期内出现的问题。 AUTO_INCRMENT 值不应在生产环境中调整或重置。

解决您的问题的一种可能的解决方案可能是稍微对您的模型进行非规范化。我们的想法是将 AUTO_INCRMENT 字段移至侧表，您无需在其中复制或删除行。您所要做的就是在创建新项目时从该侧表获取新的 id 值，并在将行从一个表复制到另一个表时保留现有的 id 值。

为了实现这一点，我们将使用一个触发器来为我们创建一个新的 id 并将其分配给我们的项目记录。项目表的 id 字段必须可以为空才能工作，因此我们必须用唯一索引替换主键。

此模型更改将对您的应用程序完全透明，因此您无需在应用程序代码中进行任何更改。

以下是一些示例脚本。假设我们的数据库中有两个项目表，其中有一些公共行，以及一些需要从第一个表移动到第二个表的行：

CREATE TABLE `item1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_res_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `item2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_res_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO item1 (item_res_id) VALUES (1);
INSERT INTO item1 (item_res_id) VALUES (2);
INSERT INTO item2 (item_res_id) VALUES (1);

如果我们尝试将一些数据从一个表移动到另一个表，然后重新启动服务器，我们会遇到AUTO_INCRMENT值重置的问题。因此，我们将稍微修改我们的模型，如下所示：

我们将分几个步骤来迁移我们的数据模型。以下迁移脚本中的 DDL 语句是使用 neXtep Designer IDE 生成的。

• 首先，我们创建一个新的 item_keys 表，该表将保存 AUTO_INCRMENT 字段：

-- Creating table 'item_keys'
CREATE TABLE item_keys ( 
   id BIGINT(20) UNSIGNED NOT NULL
  ,key_ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) Engine=InnoDB default charset=utf8;

-- Creating Primary Key constraint 'PRIMARY' on table 'item_keys'
ALTER TABLE item_keys ADD CONSTRAINT PRIMARY KEY (id);

• 但在激活 AUTO_INCRMENT 属性之前，我们必须将现有 id 插入新表中：

-- Initializing item_keys with existing ids
INSERT INTO item_keys (id)
    SELECT i1.id
    FROM item1 i1
        LEFT JOIN item_keys ik ON ik.id = i1.id
    WHERE ik.id IS NULL
;

INSERT INTO item_keys (id)
    SELECT i2.id
    FROM item2 i2
        LEFT JOIN item_keys ik ON ik.id = i2.id
    WHERE ik.id IS NULL
;

• 我们现在可以激活AUTO_INCRMENT 属性，并初始化其值以供将来插入：

-- Activating auto_increment constraint...
ALTER TABLE item_keys MODIFY id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

-- Initializing auto_increment value
SELECT @inc_value := MAX(id) FROM item_keys;
SET @alter_query = CONCAT('ALTER TABLE item_keys AUTO_INCREMENT=',@inc_value); 
PREPARE alter_query FROM @alter_query; 
EXECUTE alter_query; 
DEALLOCATE PREPARE alter_query; 

• 然后我们可以更改 item1 和 item2 表以用唯一索引替换主键，并引用 item_keys 表的主键：

-- De-activating auto_increment constraint...
ALTER TABLE item1 MODIFY id BIGINT(20) UNSIGNED NOT NULL;
-- Dropping constraint 'PRIMARY'...
ALTER TABLE item1 DROP PRIMARY KEY;
ALTER TABLE item1 MODIFY id BIGINT(20) UNSIGNED NULL;
-- Creating index 'item1_uk'...
CREATE UNIQUE INDEX item1_uk ON item1 (id);
-- Creating Foreign Key constraint 'item1_keys_fk' on table 'item1'
ALTER TABLE item1 ADD 
   CONSTRAINT item1_keys_fk FOREIGN KEY item1_keys_fk
      (id) REFERENCES item_keys
      (id)
;

-- De-activating auto_increment constraint...
ALTER TABLE item2 MODIFY id BIGINT(20) UNSIGNED NOT NULL;
-- Dropping constraint 'PRIMARY'...
ALTER TABLE item2 DROP PRIMARY KEY;
ALTER TABLE item2 MODIFY id BIGINT(20) UNSIGNED NULL;
-- Creating index 'item2_uk'...
CREATE UNIQUE INDEX item2_uk ON item2 (id);
-- Creating Foreign Key constraint 'item2_keys_fk' on table 'item2'
ALTER TABLE item2 ADD 
   CONSTRAINT item2_keys_fk FOREIGN KEY item2_keys_fk
      (id) REFERENCES item_keys
      (id)
;

• 最后，我们只需创建将为我们管理 ids 创建的触发器：

-- Creating trigger 'tr_item1_bi' on table 'item1'...
DELIMITER |;
CREATE TRIGGER tr_item1_bi BEFORE INSERT ON item1
FOR EACH ROW
BEGIN
   IF (NEW.id IS NULL) THEN

        -- If no item id has been specified in the INSERT statement, it
        -- means we want to create a new item. We insert a new record
        -- into the item_keys table to get an item id.
        INSERT INTO item_keys (
            key_ctime
          )
        VALUES (NOW());

        SET NEW.id = LAST_INSERT_ID();
    END IF;
END;
|;

-- Creating trigger 'tr_item2_bi' on table 'item2'...
DELIMITER |;
CREATE TRIGGER tr_item2_bi BEFORE INSERT ON item2
FOR EACH ROW
BEGIN
   IF (NEW.id IS NULL) THEN

        -- If no item id has been specified in the INSERT statement, it
        -- means we want to create a new item. We insert a new record
        -- into the item_keys table to get an item id.
        INSERT INTO item_keys (
            key_ctime
          )
        VALUES (NOW());

        SET NEW.id = LAST_INSERT_ID();
    END IF;
END;
|;

现在我们可以将数据从一个表移动到另一个表，保持 ids 不变，如果我们重新启动服务器上，item_keys 中的 AUTO_INCRMENT 值将保持不变。

--------------
INSERT INTO item2
    SELECT i1.*
    FROM item1 i1
        LEFT JOIN item2 i2
            ON i2.id = i1.id
    WHERE i2.id IS NULL
--------------
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

--------------
DELETE FROM item1
--------------
Query OK, 2 rows affected (0.00 sec)

--------------
INSERT INTO item1 (item_res_id) VALUES (3)
--------------
Query OK, 1 row affected (0.00 sec)

--------------
SELECT * FROM item1
--------------

+------+-------------+
| id   | item_res_id |
+------+-------------+
|    3 |           3 |
+------+-------------+
1 row in set (0.00 sec)

--------------
SELECT * FROM item2
--------------

+------+-------------+
| id   | item_res_id |
+------+-------------+
|    1 |           1 |
|    2 |           2 |
+------+-------------+
2 rows in set (0.00 sec)

--------------
SELECT * FROM item_keys
--------------

+----+---------------------+
| id | key_ctime           |
+----+---------------------+
|  1 | 2010-11-14 10:31:21 |
|  2 | 2010-11-14 10:31:21 |
|  3 | 2010-11-14 10:31:46 |
+----+---------------------+
3 rows in set (0.00 sec)

Answer 4

如果您需要在两个或多个服务器之间维护唯一 ID，请不要使用这种每次重置 auto_increment 的 alter table 方法。更改增量的增量会更容易，以便每个服务器无需干预即可生成唯一的 ID。对于两台服务器，您设置一台从 0 开始，一台从 1 开始，增量为 2 - 之后一台将生成偶数 ID，另一台将生成赔率。对于 3 个或更多服务器，您只需将初始值设置为 0/1/2，增量为 3，对于 4 个服务器，初始值为 0/1/2/3，增量为 4，等等...

有关服务器端设置的详细信息，请参见此处：

http://dev.mysql.com/ doc/refman/5.1/en/replication-options-master.html#sysvar_auto_increment_increment

这样，您只需为每个服务器的每个表重置 auto_increment 一次，之后它们将自动处理唯一性问题。

Answer 5

不是吗：

ALTER TABLE item AUTO_INCREMENT=1;

？

来源