MySQL表重新排序 [英] MySQL table reorder

问题描述

我有一张像

的桌子id - 一些数据
1 - ...
2 - ...
4 - ...
5 - ...
8 - ...
12 - ...

I have a table like

id - some data
1 - ...
2 - ...
4 - ...
5 - ...
8 - ...
12 - ...

这张表真的很长，有数千个 ID.看看 ID，它们之间有空"数字.我想对所有 ID 重新排序以确保没有空 ID.

The table is really long, with thousands IDs. Look at IDs, there are "empty" numbers between of them. I want to reorder all IDs to have no empty ones.

表格应该看起来像
1 - ...
2 - ...
3 - ...
4 - ...
等

Table should look like
1 - ...
2 - ...
3 - ...
4 - ...
etc.

查询会是什么样子?

推荐答案

我不提倡重新编号 id.id 列应该是表的主键，间隙没有区别.如果它用于外键引用，那么你会搞乱你的数据库.

I don't advocate re-numbering ids. The id column should be the primary key for the table, and gaps don't make a difference. If it is used for foreign key references, then you will be messing up your database.

但是，你可以这样做:

set @rn = 0;

update `table` t
    set t.id = (@rn := @rn + 1)
    order by t.id;

这篇关于MySQL表重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！