MYSQL在使用select插入时如何获得下一个自动增量(MYSQL how to get next auto increment when inserting with select)

编程入门行业动态更新时间:2024-10-18 06:00:15

我有两个表的结构相同，但内容不同。每个表的第一列是自动增量ID。

我想要做的是从一个表插入一些行到另一个表。但是，id列应自动设置在目标表中，而不是从源中复制。

到目前为止，我使用的是：

INSERT INTO table_1 SELECT id, column_2, column_3, column_4 FROM table_2 WHERE column_4 = 'a value'

我不能只选择表2中的ID，如上所示，因为这会在表1中创建重复的ID。

我尝试用以下内容替换'id':( (SELECT MAX(id)+1 FROM table_1) AS id 。得到正确的id，但不会为要插入的每一行增加它。

我已经尝试过(SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA_TABLES WHERE SCHEMA_TABLE = 'table_1') AS id但却说“无法解析列AUTO_INCREMENT”

我尝试过LAST_INSERT_ID() as id 。这实际上是在我第一次运行语句时有效，但是进一步尝试失败并显示消息'重复条目875用于密钥PRIMARY'

最后，我试图完全离开列，但我得到一条错误消息，说列数不匹配。

任何人都可以对此有所了解吗？我应该指出我需要纯MySQL。我不是在PHP的上下文中。

谢谢

I have two tables who's structure is identical, but the content is different. The first column of each table is an auto increment id.

What I am trying to do is insert some rows from one table into the other. However, the id column should set automatically in the target table and not be copied from the source.

So far I am using something like:

INSERT INTO table_1 SELECT id, column_2, column_3, column_4 FROM table_2 WHERE column_4 = 'a value'

I cannot just select the ID from table 2 as shown above as this creates duplicate ids in table 1.

I have tried replacing 'id' with: (SELECT MAX(id)+1 FROM table_1) AS id. That gets the correct id, but doesn't increment it for each row to be inserted.

I have tried (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA_TABLES WHERE SCHEMA_TABLE = 'table_1') AS id but that just fails saying 'Unable to resolve column AUTO_INCREMENT'

I have tried LAST_INSERT_ID() as id. This actually worked the first time I run the statement, but further attempts fail with the message 'Duplicate entry 875 for key PRIMARY'

Finally, I have tried to just leave the column out altogether but i then get an error message saying the number of columns do not match.

Can anyone shed any light onto this? I should point out that I need pure MySQL. I am not in the context of PHP.

Thanks