auto_increment

earswithfeet

Hi,

i need to add an "id" (with auto_increment) field to an existing MySQL table.
However, after adding it, every entry gets the "id" = "1".
How can i add the new "id" entry, but also make sure, that it makes use of the "auto_increment" and so getting values from "1,2,3,4,...."?

Thanks

malbera

$query="alter table test add id int(10) not null auto_increment, add primary key(id)";
mysql_query($query);

earswithfeet

Wow!
Thanks a lot for the ultra-fast reply!

TouchTheClouds

i have a row with "auto_increacement" in a mysql table. Whenever i delete entries, f.e. 7th and 8th entries of 8 total, and then add an entry after the last - 6th, mysql increases it to 9th, not to 6th. It looks like server keeps in memory id's of those deleted rows. Any ideas? 🙂

mrmufin

TouchTheClouds ---
The database is behaving the way it should. Really. If you absolutely need to get around this you can do a "SELECT MAX(id + 1) FROM table" to get the last AUTO_INCREMENT value (plus one), and 'force' the next row to use that value in its AUTO_INCREMENT column...

But since there's always going to be a 'lapse' between the SELECT and INSERT statements, I don't recommend this solution, especially without a surrounding LOCK statement, since you don't want another process writing data into that table with the AUTO_INCREMENT id you just selected.