当前位置: 动力学知识库 > 问答 > 编程问答 >

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 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!

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: