在MySQL表中插入值时，如何覆盖自动递增主键的属性? [英] How to override the attribution of an auto incrementing primary key when inserting a value in a MySQL table?

问题描述

我有一个用户的MySQL表，该表的主键是一个自动递增的整数.该表已经填充了键在0到30.000之间的记录.但是，并非所有记录都在那里.一些用户已被删除，因此我有漏洞".

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

现在，客户已经意识到他们错误地删除了许多用户，现在他们希望我重新插入那些拥有相同ID的用户，以与在后端运行的电子商务后端兼容.完全不同的机器，但为客户使用相同的ID.

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.

此刻我是

1. 通过从ID中删除auto_increment属性来更改表的结构
2. 添加我需要的记录，并指定其ID
3. 将更改恢复为表格的结构.

是否有更好的方法来实现这一目标?是否有任何SQL override函数可让我强制MySQL接受唯一的值，但不一定是行中的下一个数字"?

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

推荐答案

您不必禁用auto_increment功能.当您在表中插入一行并在行中指定主键值时，所需的ID将存储在数据库中. auto_increment仅在省略主键字段时使用.

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.

我想我可以举个例子:

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)

编辑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)

这篇关于在MySQL表中插入值时，如何覆盖自动递增主键的属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！