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

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 cu开发者_运维百科stomers.

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

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)

I had a similar problem. I discovered that you can set the ID to any number in an update, although you can't set it below the current minimum in an insert. To get around this I wrote a script that converted my import data into a series of SQL statements, with a pair that resembled the following for each row of data:

// This inserts the record with the next auto_increment id<br />
INSERT INTO mytable (ID, NAME, OTHER_COL...) VALUES (17, 'UNIQUE VALUE', 'other value'...);<br />
// This sets the correct ID on the newly inserted record<br />
UPDATE mytable SET ID=17 WHERE NAME='UNIQUE VALUE';

If you don't have a unique field, then you might write a SQL to get the largest ID and update that to the correct ID before inserting the next record. I hope this helps!

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.

It's possible, that the driver you use reads the table schema and adapts to it. When it sees auto_increment column it skips its value. This is the case with C# DataAdapter, like in Generating insert statement that includes identity column.

As they say DataAdapter cannot be configured and the only option is to use manual insert command.

I found this here because i have the same problem. Even this is older, maybee it helps someone.

The auto-increment only takes effect on NEW inserted roles. It not possible to set a new role with a given value that is lower than the currently highest increment-value in the table.

To solve this problem, just insert a new row (and let the auto-increment set a value) afterwards just change this value! (if the value is not in used, it will work)

untestet: maybe it could also solve the problem if you reset the auto-increment-counter