在 MySQL 表中插入值时如何覆盖自动递增主键的属性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1884387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to override the attribution of an auto incrementing primary key when inserting a value in a MySQL table?
提问by mac
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".
我有一个 MySQL 用户表，其主键是一个自动递增的 integer。该表已经填充了键在 0 到 30.000 之间的记录。不过，并非所有记录都在那里。一些用户已被删除，因此我有“漏洞”。
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.
现在客户意识到他们错误地删除了一堆用户，他们现在希望我重新插入那些保持他们原来相同 ID 的用户，以与电子商务的后端兼容，后者完全运行在不同的机器上，但对客户使用相同的 ID。
At the moment I am:
目前我是：
- altering the structure of the table, by removing the auto_increment property from the ID
- adding the records I need, specifying their ID
- reverting the alterations to the structure of the table.
- 通过从 ID 中删除 auto_increment 属性来改变表的结构
- 添加我需要的记录，指定它们的 ID
- 恢复对表结构的更改。
Is there a better way to achieve this? Is there any SQL overridefunction that would allow me to force MySQL to accept a value that is unique but not necessarily "the next number in the line"?
有没有更好的方法来实现这一目标？是否有任何 SQLoverride函数可以让我强制 MySQL 接受一个唯一但不一定是“行中的下一个数字”的值？
采纳答案by Dan Soap
You don't have to disable the auto_incrementfeature. 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_incrementis only used, when you omit the primary key field.
您不必禁用该auto_increment功能。当您在表中插入一行并在行中指定主键值时，您想要的 id 存储在数据库中。在auto_increment仅使用，当你忽略了主键字段。
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
编辑 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)
回答by Annulet Consulting
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:
我有一个类似的问题。我发现您可以在更新中将 ID 设置为任何数字，尽管您不能在插入中将其设置为低于当前最小值。为了解决这个问题，我编写了一个脚本，将我的导入数据转换为一系列 SQL 语句，每行数据都有一对类似于以下内容：
// 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!
如果您没有唯一字段，那么您可能会编写一条 SQL 来获取最大的 ID 并将其更新为正确的 ID，然后再插入下一条记录。我希望这有帮助！
回答by Andy West
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.
为了避免 AUTO_INCREMENT（或任何数据库中的标识字段）的实现出现问题，当数据库之外的东西（人或系统）必须知道这些值时，我从不使用它。
The MySQL documentationsays:
在MySQL文档说：
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.
AUTO_INCREMENT 属性可用于为新行生成唯一标识。
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.
在我看来，这就是你应该关心的。数字就在那里，它们是独一无二的。不要介意它们是什么以及它们是否有“间隙”或“漏洞”。如果您想要外部可见的 ID，也许某种 GUID 会更好。
回答by Jarekczek
It's possible, that the driver you use reads the table schema and adapts to it. When it sees auto_incrementcolumn it skips its value. This is the case with C# DataAdapter, like in Generating insert statement that includes identity column.
您使用的驱动程序可能会读取表架构并对其进行调整。当它看到auto_increment列时，它会跳过它的值。C# 就是这种情况DataAdapter，就像在生成包含标识列的插入语句中一样。
As they say DataAdaptercannot be configured and the only option is to use manual insertcommand.
正如他们所说DataAdapter，无法配置，唯一的选择是使用手动insert命令。
