更改 MySQL 表中行顺序的最佳方法? [英] Best way to change order of rows in MySQL table?
问题描述
我有一个 MySQL 表，其中的行需要按照用户决定的特定顺序进行排序.换句话说，需要用户能够在表中的随机点插入新行，并且还需要在选择查询时将现有行移动到表输出中的新随机位置/行号...
I have a MySQL table with rows that need to be sorted in a particular order decided by the user. In other words, need the user to be able to insert a new row at a random point in the table, and also move existing rows to a new random location/row number in the table output on a select query...
目前我想到的解决方案:
Solutions I have thought of so far:
我可以使用带有 ORDER BY 的整数，但是，我需要在插入"点之后更新每一行.
I could use an integer with an ORDER BY, however, then i will need to update every row after the point of "insertion".
我可以使用时间戳和索引整数，将新行的索引整数设置为等于当前占据插入点的行的索引整数，然后在查询中先按索引排序，然后按时间戳记，但是当我想将旧行移动到新索引而不是插入新索引时，仍然会出现全部更新"问题.
I could use a timestamp and an index integer, set the index integer of the new row to be equal to that of the row currently occupying the point of insertion, and then in the query first order by index, then by timestamp, but the "updating all" problem still occurs when i want to move an old row to a new index, instead of insert a new one.
推荐答案
一般来说，关系数据库最基本的一点是数据的顺序(存储时)完全无关.
One of the most fundamental points of relational databases in general is that the order of the data (as stored) is utterly irrelevant.
如果您想按特定顺序检索数据，请选择数据，并在 order-by 子句中指定所需的顺序.
If you want retrieved data in a particular order, select the data, and specify the required order in an order-by clause.
如果您想一直指定该顺序，您可能需要创建一个视图，并在视图定义中指定一个顺序(如果您执行 select，这仍然可以被覆盖在指定其自己的 order by 子句的视图上).
If you want to specify that order all the time, you might want to create a view, and specify an ordering in the definition of the view (this can still be overridden if you do a select on the view that specifies its own order by clause).
如果您确实一直(或几乎所有)时间都以特定顺序从该表中检索所有数据，您可能希望按该顺序创建聚集索引.这可以/将(通常)有助于按该顺序检索数据.
If you really retrieve all the data from that table in a particular order all (or nearly all) the time, you may want to create a clustered index on that order. This can/will (typically) help in retrieving data in that order.
这篇关于更改 MySQL 表中行顺序的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
