CODEWITHSUNDEEP
mysql
BaHar AyØub
BaHar AyØub

Reputation: 337

Create a rownum column in MySQL

I want to create a column in a mySQL table (auto increment), I know how to do that, but when I delete a line, columns must be organized automatically, here is an example:

rownum |  name
------------
1      | aaaa
2      | bbbb
3      | cccc
4      | dddd

If I delete the second line the result should be:

rownum |  name
------------
1      | aaaa
2      | cccc
3      | dddd

not like that:

rownum |  name
------------
1      | aaaa
3      | cccc
4      | dddd

Is there anyway to do that or do I have to edit the column every time I delete/insert something?

Upvotes: 1

Views: 1441

Answers (4)

swapnesh
swapnesh

Reputation: 26732

Try this, tested at my own end -

ALTER TABLE test ADD COLUMN id2 int unsigned not null;
SET @a:=0;
UPDATE test SET id2=@a:=@a+1 ORDER BY `rownum`;
ALTER TABLE test DROP rownum;
ALTER TABLE test CHANGE id2 rownum int UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (rownum);

Before -

before

After -

after

FYI This query block will change your column structure if noticed in a screenshot, however you can always change the column structuring in an easy single line query run.

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

you can rearange autoincrement by those steps:

 ALTER TABLE your_table DROP rownum;
 ALTER TABLE your_table AUTO_INCREMENT = 1;
 ALTER TABLE your_table ADD rownum bigint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

explication:

  • drop the column rownum
  • set auto_increment to 1
  • recreate column rownum with autoincrement in first place

Upvotes: 0

Edper
Edper

Reputation: 9322

Why not just create a dummy row number

SELECT 
    @curRow := @curRow + 1 AS row_number,
    t.name
FROM    Tbll t
JOIN    (SELECT @curRow := 0) r;

So, you don't worry on re-ordering every time there is deletion.

Upvotes: 2

Fabio
Fabio

Reputation: 23500

I think the only way to do that is dropping the primary key column and re-create it. All your rows will be reassigned and they should be in the order they were inserted. Anyway I don't understand why you would do that because if you have any table with foreign key as the column dropped you will have to manually change them. Are you sure you want to do that?

Upvotes: 0

Related Questions