MySql and auto-increment

Get back to zero?

[cyclic]

Probably v. stupid question, but I have an id field which auto-increments for each new record. When these records are deleted and a new one added the id field is +1. Is there anyway to prevent the id field becoming a large integer?

[Nick_W]

Not that I know of but, why would you want to? Maybe I'm missing your point ;)

Nick

[andreasfriedrich]

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 22.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.

From mysql manual. Relative URL: manual_Reference.html#CREATE_TABLE

Andreas

[cyclic]

Thanks Andreas. I just thought that the id field is going to get to be a very large number in a short time. I can't delete all rows therefore I am stuck with it.

[andreasfriedrich]

An unsigned int can be up to 4,294,967,295. With an unsigned bigint you can store up to 9,223,372,036,854,775,807 rows in your table. I guess that should be big enough for most applications. Thatīs quite a number of customers or items in stock ;)

Int always uses 4 bytes in your db regardless of whether it is 0 or 4,294,967,295. Similarly A bigint column always uses 8 bytes on disk.

Andreas