MySQL - auto decrement value

Question

Say I have a table like this (id is auto-increment):

id | col1 | col2 1 | 'msg'| 'msg' 2 | 'lol'| 'lol2' 3 | 'xxx'| 'x' 

Now I want to delete line number 2 and I get something like this

 id | col1 | col2 1 | 'msg'| 'msg' 3 | 'xxx'| 'x' 

The fact is that I want to get what:

 id | col1 | col2 1 | 'msg'| 'msg' 2 | 'xxx'| 'x' 

How can I do this with EASIEST (my knowledge of MySQL is very poor)?

Answer 1

You must not do this.
Do not accept an automatically incrementing unique identifier as a sequence number.
The word "unique" means that the identifier must depend on its string forever .

There is no connection between these numbers and the listing.
Imagine you want to select entries in alphabetical order. Where will your precious numbers be? The database doesn't look like an ordered list, as you probably think. This is not a flat file with lines stored in a predefined order. He has a completely different ideology. The rows in the database have no order. And it will be ordered only at a certain time, if it is explicitly set in ORDER BY .
It is also assumed that the database will search for you. Thus, you can say that with filtered rows or another order, this auto-increment number will have absolutely nothing to do with the actual positions of the rows.

If you want to list the result, this is the job of the presentation level. Just add a counter on the PHP side.

And again: these numbers should identify a specific record. If you change this number, you will never find your entry again.

Take this site for example. Stackup identifies its questions with this number:

stackoverflow.com/questions/ 3132439 / mysql-auto-decrement value

So, imagine that you saved this page address in a bookmark. Now Jeff comes and reviews the entire database. You click your bookmark and land on another question. The whole site will become a terrible mess.

Remember: numbering unique identifiers of evil!

Answer 2

I think that this is impossible. Perhaps you can perform the "update" operation. But you must do this for all records after the deleted record. This is a very bad solution for this.

Answer 3

Why use auto-increment if you want to change it manually?

Answer 4

Bad practice is to change the value of the auto_increment column. However, if you are sure you want, the following should help you.

If you delete only one entry at a time, you can use the transaction:

 START TRANSACTION; DELETE FROM table1 WHERE id = 2; UPDATE table1 SET id = id - 1 WHERE id > 2; COMMIT; 

However, if you delete several records, you will have to drop the column and re-add it. It is probably not guaranteed to put the strings in the same order as before.

 ALTER TABLE table1 DROP id; ALTER TABLE table1 ADD id INTEGER NOT NULL AUTO_INCREMENT; 

In addition, if you have data that depends on these identifiers, you need to make sure that they are updated.

Answer 5

You can renumber the whole table as follows:

 SET @r := 0; UPDATE mytable SET id = (@r := @r + 1) ORDER BY id;