Mysql Row id problem

shan_nathan · 10-20-2008, 06:17 AM

Dear all,

I am new to mysql, I am using mysql database with the front end php.

I am using contacts table for my office contact details.

i created the table using the following command

mysql > CREATE TABLE Contacts ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name TEXT, Email DATE NOT NULL );

now i added lot of contacts and the id was AUTO_INCREMENT so i did not give any ID . When i delete a contact ( example 355 Last contact )

After i add a new contact it's taking 356 ID only. the auto adjustment is not happening
can some one help me how to solve this issue.

Thanks in advance
Shan

rawdata · 10-20-2008, 10:48 AM

To my knowledge, it won't adjust. Just a shortcoming of the auto incrementer I guess. If anyone knows different, please post here. I have roster that uses auto inc and would like to make it adjust too if it will.

Wim Sturkenboom · 10-20-2008, 12:59 PM

Source: 'MySQL, The definite guide to using, programming and administering MySQL 4.1 and 5.0, 3rd edition' by Paul Dubois

Quote:

The sequence might be affected if you delete rows from the table. That is, sequence values might be reused; it is storage engine-dependent whether this occurs

For MyISAM tables, the normal behaviour is not to reuse. However, you can work around it by making the auto-increment column part of a composite index.

Code:

create table mytable
{
columnA char(1) default 'a' NOT NULL,
id INT auto_increment NOT NULL,
name char(20),
..
other colmns here
..
PRIMARY KEY (columnA, id)
} ENGINE=MYISAM;

The trick is to always insert the same value in columnA. If you now delete a record, MYISAM will reuse deleted id (according to the book, not tested). Disadvantage is obviously the sue of extra space due to the extra column.

BDB tables do reuse deleted values
InnoDB tables and MEMORY tables do not reuse deleted values

Notes:
1)
The id column MUST be specified as the last field in the primary key (so id, columnA wil not work)
2)
The given example allows the occurence of the same id a number of times

Code:

columnA id name
  a      1  shan_nathan
  a      2  rawdata
  b      1  wim sturkenboom
  a      3  someone else

3)
As far as I know, deleted values will be reused when the end of the range is reached

chrism01 · 10-20-2008, 08:45 PM

auto-increment means what it says. Its a common feature of all RDBMS's that they don't re-use values. some may wrap around at the end, but given that they are usually used for nums that shouldn't repeat eg txn ids, its not a good idea....