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"?

Thank you in advance for your time!

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)

Answer 2

In order to avoid problems with the implementation of AUTO_INCREMENT (or identity fields in any database), I never use it when something outside the database (either a person or a system) has to know about the values.

The MySQL documentation says:

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

In my opinion, that's all you should care about. The numbers are there and they are unique. Never mind what they are and if they have "gaps" or "holes". If you want externally-visible IDs, maybe some kind of GUID would be better.