如何按ID以自然顺序对行进行重新排序 [英] How to re-order rows by ID in natural order

问题描述

如何排序这样的行:

如果我的这些行具有数字ID:

If I have these rows with numeric IDs :

1 | 2 | 3 | 100 | 4

1 | 2 | 3 | 100 | 4

查询将对它们进行排序，如下所示:

The query will order them like so :

1 | 2 | 3 | 4 | 5

1 | 2 | 3 | 4 | 5

将ID为 100 的行更新为 4 ，并将ID为 4 的行更新为 5

Updating the row with ID of 100 to 4 and the row with ID of 4 to 5

换句话说

更新不按顺序的行.

推荐答案

mysql> create table t (i int);

mysql> insert into t values (1), (2), (3), (100), (4);

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|  100 |
|    4 |
+------+

mysql> set @seq := 0;

mysql> update t set i = (@seq:=@seq+1);
Rows matched: 5  Changed: 2  Warnings: 0

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

---

@ChristianKuetbach的评论很有意义.我回答了一种解决问题的方法，但问题还不清楚这些ID值是否用于表的主键.

---

Re the comment from @ChristianKuetbach makes a good point. I answered a way to accomplish what the question asks, but the question wasn't clear about whether these ID values are for a table's primary key.

如果是主键ID，那么您应该重新考虑按照描述的方式对它们重新编号.主键值必须唯一，但不能连续.您永远不要依赖自动生成的数字是连续的甚至是顺序的.由于DELETE或ROLLBACK或失败的INSERT仍会生成ID，因此始终会丢失数字.试图强行对ID进行排序最多是毫无意义的，并且可能会导致数据引用混乱.

If these values are primary key ID's, then you should reconsider renumbering them in the way you are describing. The primary key values are required to be unique, but not consecutive. You should never rely on auto-generated numbers being consecutive or even in order. You can always get missing numbers because of DELETE, or ROLLBACK, or failed INSERT that still generates an ID. Trying to force the ID's to be sorted is senseless at best, and will probably result in confusion of data references.

这篇关于如何按ID以自然顺序对行进行重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！