MySQL Auto_increment

[Capo64]

Is there a way to make auto_increment increment from the highest value in a column (not counting deleted entries)?

This is what happens now: I have a table with the ID column having auto_increment.

I create an entry. ID = 1.
I create another entry. ID = 2.
I delete entry 2.
I create another entry. ID = 3.
I end up with ID 1 and 3.

This is what I want to happen:

I create an entry. ID = 1.
I create another entry. ID = 2.
I delete entry 2.
I create another entry. ID = 2.
I end up with ID 1 and 2.

Is there something simple that will make this happen?

[exam]

Run these queries:

SELECT MAX(`id`) FROM `tablename`;
//$number gets the result of the first query plus 1.
ALTER TABLE `tablename` AUTO_INCREMENT = $number;

But you haven't done a good job describing your problem. What you really should do, is in your code to create a new row, have it check the table to see if there are any unused ids, and give the new row that id instead of the auto increment value.

[Boogle]

But you haven't done a good job describing your problem. What you really should do, is in your code to create a new row, have it check the table to see if there are any unused ids, and give the new row that id instead of the auto increment value.

I agree, you don't need to use auto_increment. You should make your own ID column and run a query that checks for the highest value within it then use

ID = HIGHEST VALUE + 1

That's the way i would do it. But why do these gaps in the ID matter anyway?Surely people aren't seeing your primary key/mainID ? You should be using a seperate reference ID for the clients to see?

Boog's

[Capo64]

Thanks, I won't use auto_increment. No, they don't see my primary key, and I guess you're right, it's not a huge deal, but I'd just prefer for there not to be gaps in the numbers

[Jesse Vlasveld]

I don't see the problem of skipping a number once in a time. It keeps nice track of you're total records you've ever had in that particular table row.

[exam]

Originally Posted by Boogle

I agree, you don't need to use auto_increment. You should make your own ID column and run a query that checks for the highest value within it then use

ID = HIGHEST VALUE + 1

That's the way i would do it. But why do these gaps in the ID matter anyway?Surely people aren't seeing your primary key/mainID ? You should be using a seperate reference ID for the clients to see?

Boog's

I never said not to use an auto_increment. In fact I think he should use auto_increment. Also, what you've described will work just like an auto incrementing column, with the exception that if the last record is deleted and then you add an new one the id will be reused. If you delete any but the last record, that ID won't be reused.

Originally Posted by Capo64

Thanks, I won't use auto_increment. No, they don't see my primary key, and I guess you're right, it's not a huge deal, but I'd just prefer for there not to be gaps in the numbers

Preferring that there not be gaps in the numbers is a *bad* reason to do what you're thinking of doing. Use an auto incrementing column. For your own sanity, and for anyone else maintaining your code, do it.

Originally Posted by Jesse Vlasveld

I don't see the problem of skipping a number once in a time. It keeps nice track of you're total records you've ever had in that particular table row.

I agree.