移动mysql行中的数字以进行排序

Question

我正在开发一个具有拖放表视图的应用程序。当你拖放它的时候，它会使你的桌子变漂亮。我现在的问题是它的SQL部分。

我有一张桌子，像：

id    | item     | sortingId
3     |  test    | 1
1     |  test2   | 2
2     |  test3   | 3
8     |  test4   | 4
5     |  test5   | 5
6     |  test6   | 6
4     |  test7   | 7
7     |  test8   | 8

例如，我想把"test2“移到"test5”和"test6“之间的位置。现在应该是这样的：

id    | item     | sortingId
3     |  test    | 1
2     |  test3   | 2
8     |  test4   | 3
5     |  test5   | 4
1     |  test2   | 5
6     |  test6   | 6
4     |  test7   | 7
7     |  test8   | 8

我找到并尝试使用答案形式如下：How to reorder a sql table

但我对SQL并没有真正的经验，也无法使它发挥作用。

以下是我尝试过的：

$sql0 = "SET @old = 2";
$sql1 = "SET @new = 5";
$sql2 = "SET @id = (SELECT id FROM items WHERE sortingId = @old)";
$sql3 = "UPDATE items SET sortingId = 0 WHERE id = @id";
$sql4 = "UPDATE items SET sortingId = sortingId + sign(@old - @new) WHERE sortingId BETWEEN least(@old, @new) AND greatest(@old, @new)";
$sql5 = "UPDATE items SET sortingId = @new WHERE id = @id";

$sql = mysqli_query($this->db, $sql0);
$sql = mysqli_query($this->db, $sql1);
$sql = mysqli_query($this->db, $sql2);
$sql = mysqli_query($this->db, $sql3);
$sql = mysqli_query($this->db, $sql4);
$sql = mysqli_query($this->db, $sql5);

我的桌子叫做物品

Answer 1

如果我正确理解，您有三部分信息(我看到您将它们用作SQL变量，但目前我将使用PHP变量)：

$UpdatedRecNo = 1; // id of the record that is moving
$oldPosition = 2;  // current position for the record
$newPosition = 5;  // position the record needs to move to

这可以通过两个简单的UPDATE查询来完成，在您检查记录移动到哪个方向之后：

if ($oldPosition < $newPosition) {
    // record is moving from position 2 to position 5, so
    // sortingIds 3, 4 and 5 need to become 2, 3 and 4
    $sql1 = "UPDATE items
             SET    sortingId = sortingId - 1
             WHERE  sortingId > $oldPosition
             AND    sortingId <= $newPosition";
} else {
    // record is moving from position 5 to position 2, so
    // sortingIds 2, 3 and 4 need to become 3, 4 and 5
    $sql1 = "UPDATE items
             SET    sortingId = sortingId + 1
             WHERE  sortingId >= $newPosition
             AND    sortingId < $oldPosition";
}

$sql2 = "UPDATE items SET sortingId = $newPosition WHERE id = $UpdatedRecNo";

在PHP中使用您的逻辑并执行两个简单的更新查询似乎比动态声明SQL变量要少得多。

如果由于某种原因，您最终会在一条根本不移动的记录上到达这段代码($oldPosition和$newPosition是相同的)，那么$sql1就不会有任何匹配(例如，sortingId >= 2 AND sortingId < 2永远不会匹配)，并且$sql2将没有受影响的行，所以您很好。

免责声明:显然，如果这些变量来自用户输入，请为两个查询使用准备好的语句。因为我只是给出了一个例子，说明如何使用两个查询来完成这个任务，所以我没有费心。

Answer 2

像这样的事情应该可以做到：

start transaction;

set @old = 2;
set @new = 5;
set @id = (select id from items where sortingId = @old);

update items
set sortingId = 0
where id = @id;

update items
set sortingId = sortingId + sign(@old - @new)
where sortingId between least(@old, @new) and greatest(@old, @new);

update items
set sortingId = @new
where id = @id;

commit;

演示：http://rextester.com/TUG60933

它在两个方向都能工作。你可以把职位从5改为2)。

如果sortingId不是唯一的，则可以跳过第一个UPDATE语句(set sortingId = 0)。

请注意，这不是并发保存。