Women in Technology

Hear us Roar

Article:		Ten MySQL Best Practices
Subject:		Alternative to point number 6
Date:		2005-01-07 00:09:26
From:		Tony Marston

Instead of using an AUTO_INCREMENT column the method I have used for years, and which works on all RDBMS systems, is as follows: SELECT MAX(id) FROM 'table' This gives the current highest value. Just add 1 for the next number. Provided that 'id' is an indexed column the query can be satisfied very, very quickly by retrieving the highest value from the index and without performing a full table scan. A problem I have had with obtaining numbers from a different table is that it is possible to import data into the data table (e.g. from a 'live' to a 'dev' database) which contains id values which are greater than the values on the sequence table, thus causing all future inserts to fail. The above method does not suffer from this problem.

Showing messages 1 through 1 of 1.

Alternative to point number 6
2008-01-27 17:33:21 zhalbrecht [View]

Oh no. no. no no no no no. Please don't do this. How are you guaranteeing that someone else won't do the same thing at the same time? Think the chances of that are so small you'll never have to worry? Start worrying. As someone who has had to fix this kind of issue let me assure you that problems will occur. You've made no mention of transactions and even at that point wrapping your own INSERTs in transactions manually, you really are reinventing the wheel.