Sign In/My Account | View Cart  

advertisement

AddThis Social Bookmark Button

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.
Full Threads Oldest First

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.