INSERT IGNORE still autoincrements, alternative SQL query?

Question

$q = $db->prepare('INSERT IGNORE INTO Cities (Name,Stamp) VALUES (?,?)');
$q->execute(array($city,$stamp));

I am running this code to insert new cities in the Cities table. I just noticed that every query increments the id field, and that's not what I want. It's not like I rely on the id values being gapless, but it's ridiculous to increment every time the code is run.

So, what kind of smart SQL query can I run to give me the same functionality without doing two queries (first SELECT, then INSERT) and that does not auto_increment?

Table description:

    Field       Type         Null   Key Default Extra
    id          int(11)      NO     PRI NULL    auto_increment
    Name        varchar(100) YES    UNI NULL     
    countryid   int(11)      YES        NULL     
    Stamp       int(11)      YES        NULL     
    uid         int(11)      YES        NULL

The code:

INSERT INTO Cities (Name,Stamp) VALUES (?,?) ON DUPLICATE KEY UPDATE Name = Name

increments the value on my table. The MySQL version is 5.5.9. The database engine is innoDB.

Possibly it can be this bug: http://bugs.mysql.com/bug.php?id=28781 If you scroll down to the bottom you can see that it's still an issue, three years later...

it's a standard mysql autoinctrment mechanism for integer primary key fields. what kind of inctrment logic would you like to apply to your table? — heximal, Aug 01 '11 at 12:07
I'm only using the id values to identify the cities over joined tables. However, the query is beeing run 100 000 times a day, with 98% duplicates being made, so incrementing every time is silly. — Mattis, Aug 01 '11 at 12:11
Add a unique index and ignore the dupicate error. That way, I think the id is not incremented. — Jacob, Aug 01 '11 at 12:15

Mike · Accepted Answer · 2011-08-01T14:33:18.820

See Bug #42497: V5.1 AUTO_INCREMENT gaps with InnoDB when using INSERT IGNORE (which is not actually a bug).

You either need to configure innodb_autoinc_lock_mode=0, or change your statement. For more information, see the related AUTO_INCREMENT Handling in InnoDB documentation.

This is a summary of the behaviour that you are seeing. The new method for allocating auto-increment values in InnoDB was introduced with MySQL 5.1.22:

For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values without taking any lock, but only if there is no concurrent session already holding the table-level AUTO-INC lock (because that other statement will be allocating auto-increment values one-by-one as it proceeds). More precisely, such an INSERT statement obtains auto-increment values under the control of a mutex (a light-weight lock) that is not held until the statement completes, but only for the duration of the allocation process.

In short, MySQL is able to determine how many rows will be inserted by your statement, and pre-allocates the auto-increment value in advance. This prevents multiple concurrent statements from interleaving auto-increment values. However, once that number has been allocated, it cannot be used again. This introduces the possibility of gaps in auto-increment values.

The following statement works for me, without incrementing the auto-increment value, and with innodb_autoinc_lock_mode = 1 (the default setting):

INSERT INTO Cities (Name, Stamp)
SELECT $Name, $Stamp
  FROM Cities
 WHERE NOT EXISTS (
    SELECT NULL
      FROM Cities
     WHERE Name = $Name
);

Yes, your example is probably the way to go, but that would default to two queries. Anyhow, thanks! — Mattis, Aug 01 '11 at 14:51
Just a note, this fails if the Cities table is empty. It also, for me, increments the auto_increment by 3 unless I put "limit 1" on the outer select statement — frymaster, Nov 06 '13 at 09:58
This does indeed fail when the table is empty. Anyone know how to fix the query so it will still work on an empty table? — dmikester1, Aug 10 '14 at 05:20

score 0 · Answer 2 · answered Aug 01 '11 at 13:21

0

Is Name or Stamp unique?

Elsewise the value increases because the same rows are inserted. But I guess you should notice if you have that many rows in your table?

answered Aug 01 '11 at 13:21

Robin Castlin

10,956
1
28
44

score -1 · Answer 3 · answered Aug 01 '11 at 12:07

-1

$q = $db->prepare('INSERT INTO Cities (Name,Stamp) VALUES (?,?) ON DUPLICATE KEY UPDATE Name = Name');
$q->execute(array($city,$stamp));

answered Aug 01 '11 at 12:07

Shef

44,808
15
79
90

I'm not sure that's what I need. I rather just want it to skip the insert on duplicate entry, not update it. – Mattis Aug 01 '11 at 12:09
@Mattis: This will skip the insert, it will just update the column with the value it already has. I haven't benchmarked, but if you have an index column `id`, it might be faster if you change the query to `ON DUPLICATE KEY UPDATE id = id`. – Shef Aug 01 '11 at 12:12
1

Okay, tried it some more and realized it still increments the id field. Oh well, maybe I'll add some logic and solve it that way. – Mattis Aug 01 '11 at 12:46
@Mattis: I am not quite sure I am following you. It will increment the `AUTO_INCREMENT` column if and only if an insert will be done, otherwise it will not increment it. Is this the behavior you are looking to avoid? – Shef Aug 01 '11 at 13:12
Your code works, but it is still increments the value even if nothing is inserted. It's just as if it first sees it as an INSERT and increments, and then comes to the ON DUPLICATE part and makes an UPDATE instead. – Mattis Aug 01 '11 at 13:29
http://stackoverflow.com/questions/5924762/prevent-autoincrement-on-mysql-duplicate-insert <-- Itay Moav gave the same answer there as you, and in the comments can you see that it still increments. – Mattis Aug 01 '11 at 13:33
http://bugs.mysql.com/bug.php?id=28781 I just read up on that bug. If you scroll down the whole page you can see that it still seems to be an issue. – Mattis Aug 01 '11 at 13:37
@Mattis: I have tested on two different MySQL servers `5.1.52` and `5.5.8`, none of them behave like you describe it. I ran a query on a table with `INSERT ... ON DUPLICATE` syntax, and the `id` of that table was an `AUTO_INCREMENT` column, so if I tried to insert something which already was on the table, it wouldn't be inserted. Then, when I tried to insert another record on the table which did not exist, with the same syntax, it was inserted just fine, and the id was the next sequence. So, is this what you are doing and you are not getting the next sequence, but a higher one? – Shef Aug 01 '11 at 13:43
I can't really figure out what would differ from your test setup that matters. It increments no matter what query I run. I'll update my post with more information and table description. – Mattis Aug 01 '11 at 13:52
Still skips IDs for me, much like Mattis is experiencing. Using 10.0.3-MariaDB – Alkanshel Feb 25 '14 at 04:49

INSERT IGNORE still autoincrements, alternative SQL query?

3 Answers3