Stop AUTO_INCREMENT From Jumping

One of my MySQL tables has an ID column - it's a primary key with auto_increment. And it works. If I add six rows they number 1 to 6 nicely. But if I delete rows 4 to 6 then add a new row, it becomes row 7. And if I delete that it becomes row 8. How can I make it so the next auto_increment is the smallest possible? (i.e. 1,2,3,4,5,6 - I delete 4-6 and the next row inserted becomes 4)

Simply you can't.Unchecking auto_increment flag you can do something like this:INSERT INTO table SELECTmax(id)+1 as id,value1 as v1,value2 as v2,...,valuen as vn from tableSupposing you have a table with n+1 fields: ID,v1,...,vn

There is no automatic option for this. You can do this manually by going into the MySQL database operations and changing the AUTO_INCREMENT value or primary key (something similar like that) to the lowest possible non-occupied id number.I don't know if it will skip over occupied numbers or just go back to using the highest number when it encounters a number that is already occupied (e.g. you already have a 7, but you don't have a 5, so you set it at 5, what happens when it gets to 7?)But this is the only way to do this efficiently.

The simplest way to deal with this would be to select the entry with the highest ID (SELECT id FROM table ORDER BY id DESC LIMIT 1), and set the current auto_increment value to that ID number plus one. However, I am not sure whether this is possible, but common sense tells me it should be

I don't think that there really is a way to do that effeciently, you would have to do it manually, and it would probably require several calls to the database.Why do you need this anyhow? Is it really all that important?

As the others pointed out, it's quite a hassle and not that feasible. It shouldn't be more troublesome to rewrite whatever you're working with to work with the auto_increment and missing ID's. I assume you query the values with ID+1 - either do nullcheck or take the next value in list like all database programs do.

Its not feasible to do the AUTO_INCREMENT by the database if some records got delete. It needs to be done manually, as all other members have mention. And if this procedure is not that important to maintain the seq of the numbers, then just use what the database does. Another point is that if you want to reset everything to start from 1, 2, .... etc again. By deleting all the records and insert new records again. The seq will be out. It will be last inserted. Eg. insert 1, 2, 3, 4delete 1, 2, 3, 4when you insert again. It will be 5, 6, 7, 8 and not 1, 2, 3, 4. So if you want to do it again starting from 1, use the ALTER function and alter the table. It will start all over again for the seq. Was trying to get it to start the seq of number again and took awhile to figure that out. So just share some points, in case someone is like me, figuring that out got hrs.Cheers

I dont think you can do that but I think it may be possible if you use php. Unselect auto increment and run a while. Get the ID and check it based on the last ID (find if it is +1 of last variable) then run an if tag. If it is +1 of the last var then do nothing but if not simply add the entrie. This works good for forums ect. so if member 5 is deleated. You allow easy to remember urls to re-appear (example: ID=5 is easyier then ID=156123). Get what I mean. But if you do it this way the ID can no longer be used as a way to order entries by when it was created. Here is an example of php you might use.

$LID=0; //Last ID$result = mysql_query("SELECT * FROM example ORDER BY ID");while($row = mysql_fetch_array( $result )) {if($CH!=1){$ID=$row['ID']; //Get ID$LID=$LID+1;if($LID!=$ID){////Add your mysql insert here.//$CH=1; //This var will stop from multiple entries.}unset($ID);}}

Now I haven't tested the code above. But it should work. This is the only way I could think of to solve your problem. Note: This assumes that your first ID is 1 not 0. If your first ID is 0 you need to add:

$ID=ID+1;

after:

$ID=$row['ID']; //Get ID

Hopefully this will solve your problem,
Sparkx

Well, usually a lot of new users which goes into databases doesn't like that when you delete a record the auto increment continues from the last one and they want the database to be sorted, in fact it is a bad habit, the auto increment shouldn't be ever changed and every row should be unique, if some database guy would find out that you use something like that in your job, he would definitely say something, I don't think it is bad for personal use, but there is no point, it really doesn't matter how the data is stored, the main thing it is stored and with any language you can process it and sort it, select what you want, some people doesn't even use delete in their query, they just deletes the records, but leaves the row and just doesn't process empty rows and in my opinion this is quite good, even better would be to have a column of status and for example 0 would be hidden, 1 would be not hidden, something like that..Of course, a lot of whom usually play with small databases, these kind of things isn't relevant, but it is a bad habit, I used to do such "hacks" myself, the only purpose for it, that the data would look more "beautiful" on phpMyAdmin

I don't think it is bad for personal use, but there is no point, it really doesn't matter how the data is stored, the main thing it is stored and with any language you can process it and sort it, select what you want,

That's not true, one common use of AUTO_INCREMENT is to give unique numbers to members of a group (like forums). While it does not necessarily matter if users are ordered in sequence, there are some functionality things that can be made faster if the assumption that there are no gaps to worry about hold. The reason to keep no gaps in numbers for entries isn't from the SQL perspective, but from the integrating other things with SQL perspective.

some people doesn't even use delete in their query, they just deletes the records, but leaves the row and just doesn't process empty rows and in my opinion this is quite good, even better would be to have a column of status and for example 0 would be hidden, 1 would be not hidden, something like that..

That can be problematic if you have a large number of entries that are no longer relevant because some hosts and servers (such as, oh, for example, Xisto) place limits on the amount of space you have, and depending on what you use your databases for, they can grow very large very quickly.
~Viz

P.S. try using periods as well as commas in your posts to make things easier to read. Just some more helpful advice we all (including me) should follow to make better posts.

Perhaps this is the same poster I just replied to. The auto increment will not go back and fill in blank numbers from deletion. this is for a VERY good reason. One being that the number is still in use tucked away in the hidden system tables of the RDBMS. If it is important that your auto increment numbers are all sequential, especially if it's your primary key, then your data model and/or programming flat out stink. Believe me when i say the people that developed the RDBMS in the first place know what they are doing. This behavior is normal and preferred. Thsi auto number also holds things like user id and timestamps with those id's that are supposed to remain unique for good reasons.

Perhaps this is the same poster I just replied to.
The auto increment will not go back and fill in blank numbers from deletion. this is for a VERY good reason. One being that the number is still in use tucked away in the hidden system tables of the RDBMS. If it is important that your auto increment numbers are all sequential, especially if it's your primary key, then your data model and/or programming flat out stink.

Believe me when i say the people that developed the RDBMS in the first place know what they are doing. This behavior is normal and preferred. Thsi auto number also holds things like user id and timestamps with those id's that are supposed to remain unique for good reasons.

I dont under stand this at lol

Then why post?And for god sakes when you're quoting someone KEEP THE QUOTE TAGS AROUND IT

...
I dont under stand this at lol

I know this thread is a bit older but given my previous response does not seem to have been clear enough I thought that I would clarify. Firstly RDBMS = Relational Database Management System - Such as MySQL Oracle SQL Server and so on. the RDBMS should not be confused with the term database as a database is your personal collection of data whereas the RDBMS is the underlying program that supports the data and allows you to manipulate the data. Thus calling MySQL a database is like calling MS word an office document.

The auto increment will not go back and fill in blank numbers from deletion. this is for a VERY good reason. One being that the number is still in use tucked away in the hidden system tables of the RDBMS.

In every database there are system tables as well the normal data tables. The system tables are placed in the database by the RDBMS. These system tables store information such as the names of the databases tables, the names and data types of the fields in tables and so on. These system tables also store what auto increment numbers have been used. These system tables are very important and should not be played with.

If it is important that your auto increment numbers are all sequential, especially if it's your primary key, then your data model and/or programming flat out stink.

This statement seems fairly self explanatory. I can think of no reason whatsoever why an auto increment field would need to be sequential. For the Primary Key of a table there is no reason at all. Primary keys need always to be unique. A primary key should never ever be reused. Just for example what happens if you need to restore a table where you have reused the primary key but, the backup has the original owner of that PK. Your data in that table and the ones related to it are not going to congruent.

Lastly all because you may not have a record in your database any more does not mean that the RDBMS does not know it was there. In the database logs there will be activity records for the primary keys of what has been done to record when and by whom.

Perhaps this more lengthy explanation clarifies my previous post or maybe it has obfuscated it.

had the same problem but now I realized that it's fairly ( as usual with SQL) straith forward to reformulat my query.

 I'm selecting say 10 items from specific point but want to move exactly 3 steps up/down this fails if the id's aren't evenly sorted, but I can just as well chose the starting point ( this is absolute, the top or bottom of the list ) and select out 13 items ( using LIMIT ) and T.H.E.and move 3 items down/up this list... Different route but resolves the problem..