CODEWITHSUNDEEP
mysqlsql
Mattis
Mattis

Reputation: 5096

INSERT IGNORE still autoincrements, alternative SQL query?

$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...

Upvotes: 2

Views: 4371

Answers (3)

Mike
Mike

Reputation: 21659

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
);

Upvotes: 3

Robin Castlin
Robin Castlin

Reputation: 10996

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?

Upvotes: 0

Shef
Shef

Reputation: 45589

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

Upvotes: -1

Related Questions