PRIMARY KEY auto-increments on INSERT IGNORE

Question

Here's an entire insert: http://codebin.org/view/61969a27

Here's an example of that insert:

INSERT IGNORE INTO `listings`
(
    `feed_id` ,
     `source` ,
    `time` ,
    `country`,
    `state` ,
    `city` ,
    `zip`,
    `title` ,
    `url`
)
VALUES (
     '5050498326',
     'cl_web',
     '1433027144',
     'US',
     'AL',
     'Auburn',
     '36830',
     'Moms Helping Moms Work From Home!',
     'http://auburn.craigslist.org/web/5050498326.html'
 ), (
     '5050537388',
     'cl_web',
     '1433026553',
     'US',
     'AL',
     'Auburn',
     '36830',
     'Moms Helping Moms Work From Home!',
     'http://auburn.craigslist.org/web/5050537388.html'
);

What I'm finding is that auto_increment is increasing on 'failed' inserts, or rather inserts that are ignored.

I have a list of values and a lot of the time (most of the time) I already have those in the database.

Is there another way to write this query so that auto_increment does not increase? Furthermore, is there a better more optimal way to write this kind of query? I know you can do inserts on selects, but I am in no way familiar enough with SQL.

Answer 1

Imagine there are two transactions. The first transaction aborts after they both request a new auto-increment value. Since the second transaction must have received a higher auto-increment number, and the first transaction did not use its assigned number, there is now be a gap. It is theoretically impossible to guarantee gapless auto-increment columns.

You can reduce the gaps by using a where clause to filter out key violations. For example, this query only inserts a row if the PK is not taken:

insert  Table1
        (pk, col1)
select  1
,       'val1'
where   not exists
        (
        select  *
        from    Table1
        where   pk = 1
        )

This requires more SQL code than insert ignore, but should result in fewer auto-increment gaps.