Re: auto_increment in mysql

> I use MySQL 3.22.23 but I just had a hunt about on the web and I found > a posting that confirmed your findings - I.E: auto_increment starts at > 1!! > > I stand corrected! The reasoning behind this a colleague of mine recently informed me of.. When an insert occurs, the new record is given the value of '0'. The auto-increment occurs once the insert is complete. If you use a 0 value in the auto_increment'd field, and you do a table modification (done this once or twice), you'll find you 0'd field jumped up to the next auto_increment value. bkx --MySQL Documentation extract-- An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 23.4.30 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM table but not with a MyISAM table. If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for both table types. NOTE: There can be only one AUTO_INCREMENT column per table, and it must be indexed. MySQL Version 3.23 will also only work properly if the auto_increment column only has positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers 'wrap' over from positive to negative and also to ensure that one doesn't accidently get an auto_increment column that contains 0. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query: SELECT * from tbl_name WHERE auto_col IS NULL --End Documentation--: manual_Reference.html#IDX839