重新排列MySQL表中的行 [英] Reorder rows in a MySQL table

问题描述

我有一张桌子:

+--------+-------------------+-----------+
| ID     | Name              |  Order    |
+--------+-------------------+-----------+
| 1      | John              | 1         |
| 2      | Mike              | 3         |
| 3      | Daniel            | 4         |
| 4      | Lisa              | 2         |
| 5      | Joe               | 5         |
+--------+-------------------+-----------+

订单可以由admin更改，因此可以更改订单列.在管理方面，我有一个带有选择框Insert After:的表单，用于输入数据库.在插入的列之后，我应该使用哪种查询来对+1排序.

The order can be changed by admin hence the order column. On the admin side I have a form with a select box Insert After: to entries to the database. What query should I use to order+1 after the inserted column.

我想以一种使服务器负载最小的方式执行此操作，因为此表当前有1200行.这是保存表顺序的正确方法还是有更好的方法?

I want to do this in a such way that keeps server load to a minimum because this table has 1200 rows at present. Is this the correct way to save an order of the table or is there a better way?

任何帮助表示赞赏

这是我想做的，这要感谢它的matt:

Here's what I want to do, thanks to itsmatt:

想要重新排列第1行到第1100行之后，您打算保留2-1100不变，然后将1修改为1101并递增1101-1200

want to reorder row number 1 to be after row 1100, you plan to leave 2-1100 the same and then modify 1 to be 1101 and increment 1101-1200

推荐答案

您需要分两个步骤进行操作:

You need to do this in two steps:

UPDATE MyTable 
   SET `Order` = `Order` + 1 
 WHERE `Order` > (SELECT `Order` 
                    FROM MyTable 
                   WHERE ID = <insert-after-id>);

...这将使每行的订单号比要插入的人更远.

...which will shift the order number of every row further down the list than the person you're inserting after.

然后:

INSERT INTO MyTable (Name, `Order`)
VALUES (Name, (SELECT `Order` + 1 FROM MyTable WHERE ID = <insert-after-id>));

要插入新行(假设ID为自动递增)，其订单号比要插入的人多一.

To insert the new row (assuming ID is auto increment), with an order number of one more than the person you're inserting after.

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