re-number of mysql "id" field

[sungpeng]

id

4

5

6

9

12

20

etc..

 

Above is mysql "id" field. Can I check how to re-number them automatic with one code so that they appear..

 

id

1

2

3

4

5

6

etc..

 

 

 

[Mchl]

First question: what for? The database does not care if there are gaps in primary key or not, and your users should never see/use these numbers.

[fenway]

Don't even consider doing this -- this is a horrible, horrible idea.

[christsealed]

I'm sort of a neat freak myself. While I'm aware of the dangers, I just couldn't help but try to figure this one out for myself. I wanted to renumber my id field after a deletion and I just couldn't take no for an answer. I'm sure there's a cleaner way, but here's how I did it.

 

$getRows = mysql_query("SELECT ID FROM $table") or die (mysql_error());
$numGetRows = mysql_num_rows($getRows);        
   	
$getLastRow = mysql_query("SELECT ID FROM $table ORDER BY ID DESC LIMIT 1") or die (mysql_error());
$row = mysql_fetch_assoc($getLastRow);

$maxID = $row['ID'];
    
$difference = (int)$maxID - (int)$numGetRows;

$renumber = mysql_query("UPDATE $table SET ID = ID - ('$difference') WHERE ID + ('$difference') > ('$maxID') ORDER BY ID ASC") or die (mysql_error());

 

I'm sure you can use SELECT COUNT instead of SELECT ID, and remove (int) from the $numGetRows variable. I didn't say it was optimized, but it does work!

 

Hope that helps!

[kickstart]

Hi

 

While fully echoing what Fenway and Mchl say in this ancient thread, it can be done even easier than that with the following:-

 

SET @rownum = 0;
update idtest SET Id = (@rownum:=@rownum+1) ORDER BY id

 

It really is a horrible idea though and updating the primary key like this will destroy any relationships based on it.

 

All the best

 

Keith

[Pikachu2000]

"Neatness" has nothing to do with it. It's a matter of referential integrity. That's what makes it a bad idea to go and jack around with a table's key.

[fenway]

And you're NOT aware of the dangers if you're even considering this.