CODEWITHSUNDEEP
mysqlsql
Philip
Philip

Reputation: 1078

Order IDs in a table

Is it possible to make phpMyAdmin rearrange the IDs of a table? Like when I got 1, 2, 3, 5, 6, ... PMA makes it 1, 2, 3, 4, 5, 6, ..?

Thanks

Upvotes: 0

Views: 60

Answers (3)

erik258
erik258

Reputation: 16285

The best way I can think of to renumber data is to use an auto_increment. Say you want to reorder table t with out-of-order / holey ID column id.

CREATE TABLE `t` ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ... );

First step is to create a new numbering and associate each existing ID to a new ID counting up from 1. 

-- this could be a temporary table
CREATE TABLE `new_t_ids` ( 
    new_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    current_id INT UNSIGNED NOT NULL 
);
INSERT INTO new_t_ids (current_id) SELECT id FROM t ORDER BY id ASC;

now we can update t:

UPDATE t  JOIN new_t_ids ON t.id = new_t_ids.current_id  SET t.id = new_id;

finally we set the auto_increment. This ends up being excessively tricky because the ALTER can't set to a vairable directly, but a good pattern to show off in stackoverlow anyway (FYI, I had to look it up)

SET @next_id  = ( SELECT MAX(ID) + 1 from t );
set @s = CONCAT("alter table t auto_increment = ", @next_id);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

Now your IDs are all renumbered from 1.

We can leave the new_t_ids table around for reference, or 

DROP TABLE new_t_ids;

That having been said, this is a bad idea. The numbers don't have to be without gaps between. My big concern is that if anything relates to this data and your foreign keys aren't properly defined, you're going to end up breaking all those associations.

Furthermore, if you're doing this to the table online, you'll want to lock it so that the content doesn't change while you're working. Better really to just take the database out of service.

So my recommendation is to try to let go of your rather human perspective on how data should look in a computer:) Holes are a lot more OK generally speaking than broken relational data.

Upvotes: 2

ceejayoz
ceejayoz

Reputation: 179994

If they're subject to change regularly, they're not really IDs. Imagine if your Social Security Number or credit card number changed every time someone died.

Upvotes: 0

Arnold
Arnold

Reputation: 98

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

Upvotes: 0

Related Questions