5

In MySQL I used INSERT IGNORE statement to insert rows to table. Because one column is UNIQUE, some rows were not inserted (as they already been there). After execution of that statement I noticed that auto increment column has some missing numbers between rows, which later I realized that happened due to rows that was ignored and not added.

Is it possible to setup system to not increase auto increment counter if no row is inserted with IGNORE clause?

000
  • 26,951
  • 10
  • 71
  • 101
Ωmega
  • 42,614
  • 34
  • 134
  • 203
  • What version of MySQL are you using? – eggyal Apr 25 '12 at 15:42
  • 1
    Missing numbers are not a problem in an auto_increment column. They are never guaranteed to be gap-free. Just ignore it. –  Apr 25 '12 at 15:44
  • @eggyal - mysql Ver 14.14 Distrib 5.5.20, for Linux (i686) using readline 5.1 – Ωmega Apr 25 '12 at 15:47
  • A better phrasing would be: "reset counter when no record is added". Nothing in the documentation suggests that auto-incremented integers are reused automatically. – Álvaro González Apr 25 '12 at 15:48
  • @stackoverflow - I said the opposite: docs do *not* mention any special case where the counter will decrease automatically after being increased. All resets I know are kind of manual: `ALTER TABLE`, `TRUNCATE TABLE`... – Álvaro González Apr 25 '12 at 15:58
  • @stackoverflow - I've composed an answer on that. – Álvaro González Apr 25 '12 at 16:21

1 Answers1

3

Quoting from the manual page for INSERT:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

The INSERT IGNORE syntax is just a way to suppress certain error messages and it's helpful when you are aware that those errors might happen and/or want to handle them at a later stage. Behind the scenes, you still have a regular insert, except that it fails due to a violated key. MySQL needs the actual row values to make an insert and the AUTO_INCREMENT counter will increment according to regular rules:

  1. The value for the column is NULL.
  2. The value for the column is not set.
  3. The value for the column is greater than the counter.

So unless you can rethink your logic (e.g., test whether the key values exist before making the insert), the only way to reset the counter is ALTER TABLE:

ALTER TABLE t2 AUTO_INCREMENT = value;

It is not that gaps should matter anyway. If they do (e.g., you're generating invoice numbers that need to be correlative), you're probably using the wrong tool for the job.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • What would be correct statement for that? The following statement has a syntax error: `ALTER TABLE \`table\` AUTO_INCREMENT = (SELECT MAX(LAST_INSERT_ID(id)) FROM \`table\`)`. Please advice. – Ωmega Apr 25 '12 at 16:34
  • I suppose `ALTER TABLE` does not expect subqueries. If a variable does not do the trick, you can always compose dynamic SQL. Whatever, this method looks like the last resort IMHO. – Álvaro González Apr 25 '12 at 16:59