1

Summary of the issue: With MySQL and SQLite, a failed INSERT OR IGNORE on a table with AUTOINCREMENT still increases the autoincremented value. This has been discussed here: INSERT IGNORE increases auto increment counter even no record is added?

My issue: a table with 99% failed inserts ends up with huge index numbers, for example record 1500 has the index 165200.

This is impractical, so I am looking for a way to counter this behaviour.

Since INSERT OR IGNORE is supposedly the same as INSERT ON CONFLICT IGNORE, I have thought of using INSERT ON CONFLICT FAIL or INSERT ON CONFLICT ABORT.

My problem is that I am inserting from a SELECT this way:

INSERT OR IGNORE INTO t1 (x,y,z,) SELECT * FROM t2

This syntax does not accept ON CONFLICT FAIL. Neither of those two work:

INSERT ON CONFLICT FAIL INTO t1 (x,y,z,) SELECT * FROM t2

INSERT INTO t1 (x,y,z,) SELECT * FROM t2 ON CONFLICT FAIL

Any thoughts or hints?

Community
  • 1
  • 1
Michał Leon
  • 2,108
  • 1
  • 15
  • 15

1 Answers1

3

First of all, on conflict is only for sqlite, and is used when you define the constraint (e.g. ... primary key on conflict abort ...). abort will not insert any rows if one of the rows fails. fail is even stranger: if you try to insert 5 rows and the 3rd row fails because of keys, it will insert the first 2 rows and stops then. So in short: don't use it. Both versions are NOT the same as ignore (at least for more than one row).

There is basically just one way to prevent the autoincrement when you insert multiple rows: don't insert rows that are already in the table.

The usual way to do that is to check if the row exists for yourself instead of the "lazy" on duplicate key/ignore. E.g. to check for duplicate values in a column y:

INSERT INTO t1 (x,y,z) 
SELECT * FROM t2 main
where not exists (
    select * from t1 test
    where main.y = test.y);

Yes, I know, it will make your code longer. You will unfortunatly have to live with that.

Solarflare
  • 10,721
  • 2
  • 18
  • 35