SQL ID值自动向上移动

Question

I have a table which an auto-increment ID column. I want my database to automatically change the ID value so there is always an ID value of 1.

For example:

ID Firstname Lastname

1  John      Smith
2  Charlie   Jacobs
3  Jason     young

I want it so that if I delete row ID 1 then row ID 2 changes to ID 1 and row ID 3 changes to ID 2. Like this:

ID Firstname Lastname

1  Charlie   Jacobs
2  Jason     young

I want to be able to then add names after which would carry on from ID 3. Preferably I was wondering if there was a setting in SQL that can do this automatically. If this isn't possible then a PHP script would be the second best thing.

Answer 1

That is not a right thing to do. Why do you want to do that? This would mess up your primary key values. So don't do it. Coming to the solution

If you are displaying this inside a form, just generate a row number for each row in your select statement. It will not have any gaps in between. MYSQL Generating Row Numbers

SELECT @row_number:=@row_number+1 AS row_number,db_names FROM mysql_testing, 
(SELECT @row_number:=0) AS t
ORDER BY db_names;