MySQL: Insert If Not Exists, Keep AutoIncrement

MySQL has an interesting issue - I call it a problem, I think MySQL calls it documented and expected behavior - where if you attempt to insert a row into a table (InnoDB) and the insertion fails due to a constraint violation, it will still increment the auto-increment counter.

The solution, annoying as it is, is to basically do a check while inserting to not even attempt the insert if that particular constraint (or, really, any other constraint you decide) would fail.

Let's say I have a table of "seen devices." Let's say each device checks in once in a while, but I want to poll the list of active devices far more frequently. I don't want to insert entries for devices showing up present if an identical entry (ID and last-seen timestamp) is already present. (Actually, for safety, let's say that if an entry with the same ID and a greater-than-or-equal-to last-seen timestamp is present.) The code (using prepared statements) looks something like this:

INSERT INTO devs_alive (dev_id, last_seen)
        SELECT * FROM (SELECT ?, ?) AS tmp
        WHERE NOT EXISTS (
                SELECT entry_id FROM devs_alive
                WHERE dev_id = ? AND last_seen >= ?
        ) LIMIT 1;

And since we're using prepared statements, you obviously have to bind the parameters, which might look something like:

1234	$stmt = $conn->prepare($query); $stmt->bind_param('iiii', $dev_id, $last_seen, $dev_id, $last_seen); $stmt->execute(); $stmt->close();

123	$stmt = $conn->prepare($query); $rows = $stmt->execute($dev_id, $last_seen, $dev_id, $last_seen); $stmt->finish;

Ches Koblents