将行从一个表插入到另一个表中，同时创建增量值？

Edit: OOPS, apparently in my test tables, I forgot to set id as autoincremental. After setting it the query works as expected. Sorry everyone!

编辑：OOPS，显然在我的测试表中，我忘了将id设置为autoincremental。设置后，查询按预期工作。大家好！

I have two tables, Source and Dest.

我有两个表，Source和Dest。

Source has the fields field1 and field2

Source有字段field1和field2

Dest has the fields id, field1 and field2. Id is the primary index and autoincremental.

Dest有字段id，field1和field2。 Id是主要索引和自动增量。

I have tried the following query:

我尝试了以下查询：

INSERT INTO dest (field1, field2)
SELECT field1, field2
FROM source
WHERE NOT EXISTS(SELECT * 
                 FROM dest 
                 WHERE (source.field2=dest.field2)
                 );

But then, the id in each copied row is 0.

但是，每个复制行中的id为0。

How can I say in my query that I want to copy field1 and field2, and create a new autoincremental value for id?

如何在我的查询中说我要复制field1和field2，并为id创建一个新的自动增量值？

2 个解决方案

#1

You can rewrite the exists statement with a left join:

您可以使用左连接重写exists语句：

INSERT INTO dest (field1, field2)
SELECT field1, field2
FROM source
LEFT JOIN dest
ON source.field2 = dest.field2
WHERE dest.field2 IS NULL;

The id should auto-increment automatically, too. If not, check that it does in the table's definition (show create table dest).

id也应自动自动增加。如果没有，请检查它是否在表的定义中显示（show create table dest）。

#2

The query should increment dest.id if it's declared as AUTO_INCREMENT.

如果它被声明为AUTO_INCREMENT，则查询应该递增dest.id。

Please post the output of

请发布输出

SHOW CREATE TABLE dest

Also, if you only want unique values of field2 to be in dest, you may declare dest.field2 as UNIQUE and just use this:

另外，如果你只想要将field2的唯一值放在dest中，你可以将dest.field2声明为UNIQUE并使用它：

INSERT IGNORE
INTO    dest
SELECT  field1, field2
FROM    source

#1