2

In MySQL, you can insert a row and update the 'last insert ID' at the same time. I'm using this trick to be able to insert items conditionally (uniqueness) and still get the unique ID in all cases.

The code below works. The problem is that the ON DUPLICATE KEY statement also updates the Auto_increment value. Is it possible to avoid that?

CREATE TABLE T(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    X VARCHAR(64) NOT NULL UNIQUE
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

INSERT INTO T(X) VALUES ('x') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
INSERT INTO T(X) VALUES ('x') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

Now we have one entry with id=1. However, the next entry will have id=3 since the Auto_increment value was updated when the second INSERT failed. This can be checked as follows:

SELECT Auto_increment FROM information_schema.tables WHERE table_name = 't'
     AND table_schema = DATABASE();

Q: Is it possible to use this trick (or equivalent) and keep the Auto_increment value? Obviously, in this case, it doesn't need to get updated.

l33t
  • 18,692
  • 16
  • 103
  • 180
  • 1
    There may well be valid reasons, but I'm curious: why do you care what `AUTO_INCREMENT` value is assigned, so long as it is unique? – eggyal Oct 02 '12 at 14:49
  • There are valid reasons (as listed here: http://stackoverflow.com/questions/7087869/mysql-insert-on-duplicate-update-adds-one-to-the-autoincrement). Still, I don't like my ID running away without a purpose :) – l33t Oct 02 '12 at 15:01
  • 1
    This is due to neither `ON DUPLICATE KEY UPDATE` nor `LAST_INSERT_ID()`: it [also arises](http://sqlfiddle.com/#!2/9751e/1/0) with `INSERT IGNORE` (although multiple insertions [behave curiously](http://sqlfiddle.com/#!2/ec62c/1), with the counter incremented for subsequent insertions but no gaps within the records inserted). The [accepted answer](http://stackoverflow.com/a/7100901/623041) of the question to which you've just linked explains this behaviour nicely. – eggyal Oct 02 '12 at 15:19
  • Why would you spend time and effort avoiding gaps in your auto-increment ids? – Jocelyn Oct 02 '12 at 17:56
  • Your title is misleading: using LAST_INSERT_ID() does not increase anything, it just returns the last generated id. – Jocelyn Oct 02 '12 at 17:57
  • Maybe I wouldn't. It's more like I want to know if this is by design, and why it is so. – l33t Oct 03 '12 at 07:21

0 Answers0