There is no easy way to get around the default behavior of the AUTO_INCREMENT attribute in MySQL, and even if you find a way, I would not recommend you to do this, as this is the best way to run into problems in the short term. AUTO_INCREMENT values are not intended to be configured or reset in a production environment.
One of the possible solutions to your problem may be incorrect denormalization of your model. The idea is to move the AUTO_INCREMENT field to the side table, where you do not need to copy or delete rows. All you have to do is get the new id value from this side table when creating a new element and keep the existing id value when copying rows from one table to another.
To achieve this, we will use a trigger that will create a new identifier for us and assign it to our item record. The id field of the element table must be zero for this to work, so we must replace the primary key with a unique index.
This model change would be completely transparent for your application , so you would have no changes in the application code .
Here are some sample scripts. Let's say we have two table of elements in our database, some common rows and some rows that need to be transferred from the first table to the second table:
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);
If we try to transfer some data from one table to another, and then restart the server, we will AUTO_INCREMENT value reset problem. Therefore, we will slightly modify our model as follows:
We will go on to a few steps to migrate our data model. The DDL statements in the following migration scenarios were created using the neXtep Designer IDE.
- First we create a new item_keys table that will contain the
AUTO_INCREMENT field:
- 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);
- But before activating the
AUTO_INCREMENT attribute AUTO_INCREMENT we must insert the existing identifiers in our new table:
- 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
;
- Now we can activate the
AUTO_INCREMENT attribute and initialize its value for future insertions:
- 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;
- Then we can modify the
item1 and item2 to replace the primary key with a unique index and refer to the primary key of the item_keys table:
- 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)
;
- Finally, we just need to create triggers that will control the creation of identifiers for us:
- 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
|;
Now we can move data from one table to another, keeping the identifiers unchanged, and if we restart the server, the AUTO_INCREMENT value in item_keys will remain the same.
--------------
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)