MySQL - auto decrementing value

Question

Let's say that I've got a table, like that (id is auto-increment):

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

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

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

The thing is, what I want to get is that:

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

How can I do that in the EASIEST way (my knowledge about MySQL is very poor)?

Answer 1

I think there is no way to this directly. Maybe you can do "update" operation. But you must do it for all record after your deleted record. It is very bad solution for this.

Answer 2

You can renumber the whole table like this:

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

Answer 3

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

Answer 4

You shouldn't do that.
Do not take auto-increment unique identifier as an ordinal number.
Word unique means that the identifier should be stuck with it's row forever

There is no connection between these numbers and enumerating.
Imagine you want to select records in alphabetical order. Where would your precious numbers go? A database is not like ordered list, as you probably think. It is not a flat file with ordered rows. It has totally different ideology. Rows in the database do not have any order. And take order only at select time, if it was excpicitly set by ORDER BY clause.
Also, database is supposed to do a search and filtering for you. So, with selected rows and different ordering this auto-increment number has nothing to do with real rows positions.

If you want to enumerate output - it is presentation layer job. Just add a counter on the PHP side.

And again: these numbers supposed to identify a certain record. If you change this number, you'd never find your record again.

Take Stackoverflow as an example. SO identifies questions with such a number:
stackoverflow.com/questions/3132439/mysql-auto-decrementing-value
So, imagine you saved this page address to a bookmark. Now Jeff comes along and renumber the whole database. You press your bookmark and land on the different question. Whole site would be a terrible mess.
Remember: Renumbering unique identifiers is evil!

Answer 5

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

If you are only deleting a single record at a time, you could use a transaction:

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

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

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

Also, if you have data that relies on these IDs, you will need to make sure it is updated.