How to override the attribution of an auto incrementing primary key when inserting a value in a MySQL table?

Question

I have a MySQL table of users whose primary key is an auto-incrementing integer. The table is already populated with records whose key is between 0 and 30.000. Not all records are there, though. Some users have been removed and therefore I have "holes".

Now the client has realised they removed a bunch of users by mistake, and they now want me to reinsert those users keeping the same ID they had, for compatibility with the back-end of the e-commerce, which runs on a different machine altogether, but uses the same ID's for the customers.

At the moment I am:

1. altering the structure of the table, by removing the auto_increment property from the ID
2. adding the records I need, specifying their ID
3. reverting the alterations to the structure of the table.

Is there a better way to achieve this? Is there any SQL override function that would allow me to force MySQL to accept a value that is unique but not necessarily "the next number in the line"?

Answer 1

You don't have to disable the auto_increment feature. When you insert a row into the table and you do specify the primary key value in the row, the id you want is stored in the database. The auto_increment is only used, when you omit the primary key field.

EDIT: I thought I might give examples for that:

mysql> describe test; +-------+------------------+------+-----+---------+----------------+ | Field | Type             | Null | Key | Default | Extra          | +-------+------------------+------+-----+---------+----------------+ | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | | value | varchar(45)      | NO   |     | NULL    |                | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.02 sec)  mysql> insert into test (value) values ('row 1'); Query OK, 1 row affected (0.06 sec)  mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 | row 1 | +----+-------+ 1 row in set (0.00 sec)  mysql> insert into test values (15, 'row 2'); Query OK, 1 row affected (0.03 sec)  mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 | row 1 | | 15 | row 2 | +----+-------+ 2 rows in set (0.00 sec) 

EDIT 2

mysql> insert into test (id, value) values (3, 'row 3'); Query OK, 1 row affected (0.00 sec)  mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 | row 1 | | 15 | row 2 | |  3 | row 3 | +----+-------+ 3 rows in set (0.00 sec)