how reset the id of mysql?

[jason21]

hi,
there is any way to reset the id of mysql?
thanks for your help

[the182guy]

Yes use the TRUNCATE SQL query e.g.

TRUNCATE TABLE "table_name"

Obviously this will delete all the data in the table aswell as reset the id.

[penagate]

There are two other ways that don't truncate any table data.

-- Reset the increment ID of a table to its highest existing value + 1
ALTER TABLE table_name AUTO_INCREMENT=1

-- Override the automatic insert ID
SET insert_id=n
INSERT INTO table_name ...

[visualAd]

You should never need to reset the auto increment ID unless you intend to remove the data, as each record is designed to be unique. This could destroy the integrity of the tables - especially if it is linked to others.

The best thing to do is plan ahead. You can use any integer type as an auto increment field, these are:

1bit (boolean), 8bit (byte), 16bit (small), 24bit (medium), 32bit (int), 64bit (big)

A standard integer (32 bits) will give you a total of 4,294,967,296 unique records. You should be using this type if you are using a 32bit operating system as it is fastest.

If you think you might need more than 4.2 million records, you can use the big int, giving you a total of 18,446,744,073,709,551,615 unique records. I worked out that populating your database with one record every second; it would take over 4105 billion years to exahaust that number.

Assuming the universe lasts that long you will still have to find space to store the 128 exabytes of data your auto increment field alone will require. Not to mention all the other fields.

In short a 32bit integer should be more than adequate for the life time of the majority of database tables.