Is the order of inserts specified for INSERT IGNORE ... SELECT?

Question

I have a table like:

CREATE TABLE {
  email VARCHAR PRIMARY,
  last_login DATE
}

And I can populate with a select result set like:

("[email protected]", "2019-01-01"),
("[email protected]", "2019-02-01")

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

Found specification:

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

Answer 1

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.

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.