How do I reset sequence numbers to become consecutive?

Question

I've got a mysql table where each row has its own sequence number in a "sequence" column. However, when a row gets deleted, it leaves a gap. So...

1
2
3
4

...becomes...

1
2
4

Is there a neat way to "reset" the sequencing, so it becomes consecutive again in one SQL query?

Incidentally, I'm sure there is a technical term for this process. Anyone?

UPDATED: The "sequence" column is not a primary key. It is only used for determining the order that records are displayed within the app.

Leatherleaf

Any reason to have straight sequence? The primary key aim is to be unique, not to be consecutive. Imagine PK that is UUID, it is not consecutive at all, but it does its work great. – Nikkinikkie

Are you assigning the ID yourself, or using AUTO_INCREMENT? – Fulfil

@Nikkinikkie No reason I guess. Just thought it would be neater. – Leatherleaf

@Urbycoz: I don't care of my PK actual values - I just satisfied with the fact that my PKs are unique and don't change their values through all row lifetime. So just follow the same idea. – Nikkinikkie

@Yuck. I'm assigning the ID myself. I didn't think that an auto_increment would work in this situation. – Leatherleaf

@Urbycoz: uhm, how do you calculate next ID value?! – Nikkinikkie

@Urbycoz: and is that column a primary key – Nikkinikkie

@Urbycoz: if it is not PK, what is it used for? – Nikkinikkie

@Nikkinikkie I've added another line to the question. Hope that clarifies things. – Leatherleaf

@Urbycoz: yep, and that makes a lot of sense now to want that in a consecutive order ;-) Anyway, I prefer to have sparsed enumerable with step ~10 to have the ability of easy inserting item between 2 others – Nikkinikkie

@zerkms: That only works 10 times ;P – Dervish

Answer 1

If the field is your primary key...

...then, as stated elsewhere on this question, you shouldn't be changing IDs. The IDs are already unique and you neither need nor want to re-use them.

Now, that said...

---

Otherwise...

It's quite possible that you have a different field (that is, as well as the PK) for some application-defined ordering. As long as this ordering isn't inherent in some other field (e.g. if it's user-defined), then there is nothing wrong with this.

You could recreate the table using a (temporary) auto_increment field and then remove the auto_increment afterwards.

I'd be tempted to UPDATE in ascending order and apply an incrementing variable.

SET @i = 0;
UPDATE `table`
   SET `myOrderCol` = @i:=@i+1
 ORDER BY `myOrderCol` ASC;

^{(Query not tested.)}

It does seem quite wasteful to do this every time you delete items, but unfortunately with this manual ordering approach there's not a whole lot you can do about that if you want to maintain the integrity of the column.

You could possibly reduce the load, such that after deleting the entry with myOrderCol equal to, say, 5:

SET @i = 5;
UPDATE `table`
   SET `myOrderCol` = @i:=@i+1
 WHERE `myOrderCol` > 5
 ORDER BY `myOrderCol` ASC;

^{(Query not tested.)}

This will "shuffle" all the following values down by one.

Dervish

Answer 2

I'd say don't bother. Reassigning sequential values is a relatively expensive operation and if the column value is for ordering purpose only there is no good reason to do that. The only concern you might have is if for example your column is UNSIGNED INT and you suspect that in the lifetime of your application you might have more than 4,294,967,296 rows (including deleted rows) and go out of range, even if that is your concern you can do the reassigning as a one time task 10 years later when that happens.

Azov

+1: Good answer. Though having gaps in the sequence makes it more complex to alter the ordering. – Dervish

Answer 3

This is a question that often I read here and in other forums. As already written by zerkms this is a false problem. Moreover if your table is related with other ones you'll lose relations.

Just for learning purpose a simple way is to store your data in a temporary table, truncate the original one (this reset auto_increment) and than repopulate it.

Silly example:

create table seq (
id int not null auto_increment primary key,
col char(1)
) engine = myisam;

insert into seq (col) values ('a'),('b'),('c'),('d');

delete from seq where id = 3;

create temporary table tmp select col from seq order by id;

truncate seq;

insert into seq (col) select * from tmp;

but it's totally useless. ;)

Gabey

Just to note, this will work as expected but this is a hack not a built-in reset of auto_increment column – Digenesis

The sequence column is not used to link to other tables. It's not a primary key. – Leatherleaf

ON UPDATE CASCADE takes care of the key issue, unless you are actually creating a new table. – Citystate

Answer 4

If this is your PK then you shouldn't change it. PKs should be (mostly) unchanging columns. If you were to change them then not only would you need to change it in that table but also in any foreign keys where is exists.

If you do need a sequential sequence then ask yourself why. In a table there is no inherent or guaranteed order (even in the PK, although it may turn out that way because of how most RDBMSs store and retrieve the data). That's why we have the ORDER BY clause in SQL. If you want to be able to generate sequential numbers based on something else (time added into the database, etc.) then consider generating that either in your query or with your front end.

Fulmer

Some application may have a user-defined ordering. If so, there is nothing wrong with storing this in the DB. – Dervish

That would be part of the "ask yourself why". If there's a requirement to allow user-defined ordering then of course you're going to keep that in the database. I never said that it was "wrong" to have something like this in the database. – Fulmer

Answer 5

Assuming that this is an ID field, you can do this when you insert:

INSERT INTO yourTable (ID)
SELECT MIN(ID)
FROM yourTable
WHERE ID > 1

As others have mentioned I don't recommend doing this. It will hold a table lock while the next ID is evaluated.

Fulfil