Posted on

How not to do auto-increment (unique IDs)

When assigning unique IDs to rows, do not do

SELECT MAX() FROM table;
Add one.

Why not? Well, if two people insert a new row at the same time, you will get into trouble. And it will happen. The same applies to using this “method” to find out which id was just added for your insert. Chances are you end up with someone else’s id. Wouldn’t that be nice in say eCommerce, assigning a sale to a different person, perhaps?

The absolute worst method was spotted in the wild, and I’ll describe it here for your benefit:

SELECT * FROM tbl;
Grab mysql_num_rows() in the app.
Add one.
Do an insert using the new number

Apart from the processing time and memory consumption of the first statement (even a COUNT (*) would have been better ;-), a delete would makes either go bad. It is so fundamentally flawed. Ugh.

The proper way:

CREATE TABLE tbl (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ….);
INSERT INTO …
SELECT LAST_INSERT_ID() (or use the mysql_insert_id() API function) to find out which id was assigned.

Easy! Please please don’t mess with this, it’s so painful.

Posted on