MYSQL:如何“重新排序"一张桌子 [英] MYSQL: how to "reorder" a table

问题描述

我有一个如下表，

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

我想使用名称"列按字母顺序重新排序，并使用此新顺序重置ID(自动递增)，以显示以下内容

I'd like to reorder alphabetically using the "name" column and reset the id (autoincrement) with this new order to end up with the following

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

问题:如何使用MYSQL做到这一点?

QUESTION: how can I do this with MYSQL?

推荐答案

我能问你为什么要这么做吗?

Can I ask why you would want to do this?

如果任何人修改任何名称值或插入新行，都会使您的订购方案混乱.试图以表的其他位置(名称列)已经可用的PK顺序存储一些含义似乎是多余的，因此是个坏主意.

If anyone modifies any of the name values or inserts new rows it will mess up your ordering scheme. Trying to store some meaning in the ordering of the PK that is already available elsewhere in the table (the name column) seems redundant and consequently a bad idea.

更好的解决方案是不用担心ID列的值，而只是在使用应用程序中的数据时对名称列进行排序.

A much better solution is not to worry about the value of the ID column and just sort on the name column when you use the data in your app.

PS::对不起，请回答非回答类型.通常，我认为您有充分的理由，只给出直接回答您要尝试做的事情的答案，但是我从您的其他问题中注意到，您仍处于数据库设计的早期学习阶段，因此我想提供帮助为您指明正确的方向，而不是帮助您朝着不明智的方法前进.

PS: Sorry for the non-answer type response. Normally I'd assume you had a good reason and just give an answer that directly addresses what you are trying to do, but I noticed from your other questions that you are still in the early learning stages about database design, so I wanted to help point you in the right direction instead of helping further your progress towards an ill-advised approach.

这篇关于MYSQL:如何“重新排序"一张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！