Не существует простого способа обойти поведение атрибута AUTO_INCREMENT по умолчанию в MySQL, и даже если вы найдете способ, я бы не рекомендовал вам это делать, так как это лучший способ столкнуться с проблемами в краткосрочной перспективе. AUTO_INCREMENT значения не предназначены для корректировки или сброса в производственной среде.
Одним из возможных решений вашей проблемы может быть небольшая денормализация вашей модели. Идея состоит в том, чтобы переместить поле AUTO_INCREMENT в боковую таблицу, где вам не нужно копировать или удалять строки. Все, что вам нужно сделать, это получить новое значение идентификатора из этой боковой таблицы при создании нового элемента и сохранить существующее значение идентификатора при копировании строк из одной таблицы в другую.
Для этого мы будем использовать триггер, который создаст для нас новый идентификатор и назначит его нашей записи элемента. Чтобы это работало, поле id таблицы элементов должно иметь значение null, поэтому мы должны заменить первичный ключ уникальным индексом.
Это изменение модели будет полностью прозрачным для вашего приложения, поэтому вам не нужно будет вносить изменения в код вашего приложения.
Вот несколько примеров скриптов. Допустим, у нас есть две таблицы элементов в нашей базе данных с некоторыми общими строками и некоторыми строками, которые необходимо переместить из первой таблицы во вторую:
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_INCREMENT. Поэтому мы немного изменим нашу модель следующим образом:
Мы выполним несколько шагов для переноса нашей модели данных. Операторы DDL в следующих сценариях миграции были сгенерированы с помощью neXtep Designer IDE.
- Сначала мы создаем новую таблицу item_keys, которая будет содержать поле
AUTO_INCREMENT:
-- 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_INCREMENT, мы должны вставить существующие идентификаторы в нашу новую таблицу:
-- 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_INCREMENT и инициализировать его значение для будущих вставок:
-- 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)
;
- Наконец, нам просто нужно создать триггеры, которые будут управлять созданием идентификаторов для нас:
-- 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;
|;
Теперь мы можем перемещать данные из одной таблицы в другую, сохраняя идентификаторы без изменений, и если мы перезапустим сервер, значение AUTO_INCREMENT в item_keys останется прежним.
--------------
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)
person
Bruno Gautier
schedule
13.11.2010
show variables like '%version%'- person derobert schedule 10.11.2010