MYSQL：如何“重排”表格

Question

我有一个如下的表格：

| id  | name   | color  |
------+--------+---------
| 1   | pear   | green  |
| 2   | apple  | red    |
| 3   | banana | yellow |
| 4   | grape  | purple |

我想按“名称”列的字母顺序重新排序，并使用这个新的顺序重置id（自动增量）以得到以下结果：

| id  | name   | color  |
------+--------+---------
| 1   | apple  | red    |
| 2   | banana | yellow |
| 3   | grape  | purple |
| 4   | pear   | green  |

问题：如何使用MYSQL完成这个任务？

- cardflopper

---

你为什么希望ID的顺序与名称的顺序匹配？ - Jeremy Raymond

Answer 1

重设自增字段最简单的方式是创建一个新表。

MySQL提供了CREATE TABLE LIKE和RENAME TABLE等命令，它们非常实用。

CREATE TABLE table2 LIKE table1;

INSERT INTO table2
  SELECT * FROM table1 ORDER BY name;

DROP TABLE table1;

RENAME TABLE table2 TO table1;

- Yada

---

这是错误的。你不能选择全部（SELECT * FROM table1），因为这样做会将主键与行信息一起复制。这将导致你得到完全相同的表，具有相同的名称和颜色，链接到相同的ID，但顺序不同。 - Mohammed Joraid

在从表1插入之前，您需要从表2中删除PK；然后不要使用select *，而是选择除ID以外的所有列。这样它就能完美运行了。 - Zalaboza

Answer 2

请问您为什么想要这样做？

如果任何人修改任何一个名称值或插入新的行，都会破坏您的排序方案。试图在已经在表中其他位置（名称列）可用的PK的排序中存储一些含义似乎是多余的，因此是一个不好的想法。

一个更好的解决方案是不用担心ID列的值，只需在使用应用程序中的数据时按名称列进行排序即可。

附言：很抱歉我的回答类型不够明确。通常我会认为您有一个好的原因，并直接给出答案，但我注意到从您的其他问题中可以看出您仍处于数据库设计的早期学习阶段，所以我希望能为您指明正确的方向，而不是帮助您朝着错误的方法前进。

- JohnFx

---

这是一个很好的观点。如果您需要一些唯一数字与每个字符串的排序值相对应，可以添加一个额外的列并根据需要进行更新。如果ID在另一个表中用作外键，则重新排序ID将严重破坏事情。 - Eric J.

我唯一建议添加额外排序列的情况是当表格中其他列无法确定排序时。例如，如果应用程序允许用户任意重新排序项目。 - JohnFx

记录形成一个集合。这些集合不应该按特定顺序排序。请记住，关系数据库理论基于纯数学概念。如果您想在应用程序中指定特定顺序，请添加orderby子句，否则执行计划将决定如何检索数据，因此也会决定顺序；这些计划可能随时间而变化。此外，请记住，代理键对最终用户没有意义。 - lmsasu

如果您添加了额外的列或仅保留id列，请确保为这些列添加相应的索引，否则在表变得太大后，您将无法高效地搜索它。 - Geek Num 88

Answer 3

您可以从旧表中选择数据并将其有序地插入到新表中，可以在新表中设置自增ID。如果需要，可以删除旧表并重命名新表。具体操作请参考此文档。

- Eric J.

Answer 4

为什么不在查询语句的末尾添加“ORDER BY name ASC”？我猜您可能需要ID。

- ademers

Answer 5

如果您有一个带有自增主键（例如命名为'id'）的表，并且该键不被其他表所依赖，那么处理方式如下：

1. 完全删除id列。
2. alter table order by column_x, column_y;
3. 再次添加自增的主键列'id'。

我用phpmyadmin成功地进行了几次操作，速度也很快。如果需要重新排序具有主键索引的表，则无法实现。这就是为什么您需要先删除它。

如果您需要保留'id'列，但需要根据其他列重新排序，则需要省略'id'列的主键和索引状态并重新排序。然后，您需要添加一个新的列作为主键/索引。

- Tim Chaubet

Answer 6

 SELECT
       RANK() Over (ORDER BY Name) As NewID
     , Name
     , Color

 FROM Fruits

可以先保存到临时表，然后截断水果表并插入数据，但这可能是一个糟糕的解决方案。

- Paul Creasey

---

MySQL不支持窗口函数。 - Vladislav Rastrusny

这非常接近，但现在如何将NewID保存到id中？ - user4038080