是否为 INSERT IGNORE ... SELECT 指定了插入顺序? [英] Is the order of inserts specified for INSERT IGNORE ... SELECT?

问题描述

我有一张像:

CREATE TABLE {
  email VARCHAR PRIMARY,
  last_login DATE
}

我可以填充一个选择结果集，如:

And I can populate with a select result set like:

("a@b.c", "2019-01-01"),
("a@b.c", "2019-02-01")

如果我将使用 INSERT IGNORE ... SELECT 插入到该表中，是否指定了插入的行和忽略的行?

If I will insert into that table using INSERT IGNORE ... SELECT, is it specified which row gets inserted and which is ignored?

找到规范:

• 指定 IGNORE 以忽略会导致重复键违规的行."来自 https://dev.mysql.com/doc/refman/5.5/en/insert-select.html.但这并不能说明哪个是重复的，哪个是非重复的.

• "Specify IGNORE to ignore rows that would cause duplicate-key violations." from https://dev.mysql.com/doc/refman/5.5/en/insert-select.html. But this does not say which is the duplicate and which is the not-duplicate.

推荐答案

insert 语句没有指定插入行的顺序 - select 语句可以.但是，除非您使用 order by 子句明确定义顺序，否则 select 返回行的顺序完全取决于数据库，不应信任.

The insert statement doesn't specify the order the rows are inserted - the select statement does. However, unless you explicitly define the order with an order by clause, the order select returns rows is completely up to the database, and shouldn't be trusted.

换句话说，如果你关心哪一行被插入，哪一行被忽略，在你的 select 语句中添加一个 order by 子句，并确保你想要的行先插入.

In other words, if you care which row gets inserted and which gets ignored, add an order by clause to your select statement and make sure that the row you want to insert comes first.

这篇关于是否为 INSERT IGNORE ... SELECT 指定了插入顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！