sql - Auto Increment after delete in MySQL

Question

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.

I have:

table: course
fields: courseID, courseName

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

This table will relate to others in a database.

---

Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) business meaning.

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.

Answer 1

What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.

If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?

Are you really going to have more than 18,446,744,073,709,551,615 unique records over the course of your application's lifetime?