在一个或极少数查询中更新多个 MySQL 行的显示顺序

php sorting mysql

2022-08-30 22:58:27

我有一个表格，每行20行，每行都有一个数字用于显示顺序（1-20）。

SELECT * FROM `mytable` ORDER BY `display_order` DESC;

从管理区域中，您可以拖动行或为每行手动键入新数字。

当然，为每行循环更新查询是不好的，在一个或极少数的查询中，适合更新20行甚至更多（50-200 +）中的一个单元格，有什么替代方案？

编辑：很多好的回应和想法。我可能会扩展到目前为止我考虑过的想法：

一个数组字符串：我可以将顺序放在一个字符串中，以我想要的顺序列出唯一行ID - 例如行1，9，2，6，23。更新订单后，隐藏字段将使用 JavaScript 进行更新，并在完成后将其添加到数据库或文本文件中：

UPDATE `my_dispaly_order_table` SET `display_order`='1,9,2,6,23';

单独更新每一行：这是我试图避免的，但它只会非常不频繁地更改，因此每周或每月一次一次点击20-30个调用可能不是问题，因此只需在每行上调用UPDATE就是我通常做的事情：

UPDATE `mytable` SET `display_order`='1' WHERE `rowId` = 1;
UPDATE `mytable` SET `display_order`='2' WHERE `rowId` = 9;
UPDATE `mytable` SET `display_order`='3' WHERE `rowId` = 2;
UPDATE `mytable` SET `display_order`='4' WHERE `rowId` = 6;
UPDATE `mytable` SET `display_order`='5' WHERE `rowId` = 23;

答案 1

soulmerge的回答让我思考，我认为这是一个更好的解决方案。您需要做的是使用 IN（）选择 ID 所在的行，然后使用 CASE 设置值。

UPDATE mytable SET display_order =
  CASE id
    WHEN 10 THEN 1
    WHEN 23 THEN 2
    WHEN 4 THEN 3
  END CASE
WHERE id IN (10, 23, 4)

我在当前应用中需要这样做。在PHP中，我从jQuery UI的内置可排序功能中获得一组序列化（和有序）的id。所以数组看起来像这样：

$new_order = array(4, 2, 99, 15, 32); // etc

为了生成单个MySQL更新查询，我这样做：

$query = "UPDATE mytable SET display_order = (CASE id ";
foreach($new_order as $sort => $id) {
  $query .= " WHEN {$id} THEN {$sort}";
}
$query .= " END CASE) WHERE id IN (" . implode(",", $new_order) . ")";

最后的“内爆”只是给了我IN（）的ID列表。这对我来说很有效。

答案 2

您应该首先确保该列没有UNIQUE索引，否则mysql会告诉您约束在查询过程中被破坏。之后，您可以执行如下操作：

-- Move #10 down (i.e. swap #10 and #11)
UPDATE mytable SET display_order =
  CASE display_order
    WHEN 10 THEN 11
    WHEN 11 THEN 10
  END CASE
WHERE display_order BETWEEN 10 AND 11;

-- Move #4 to #10
UPDATE mytable SET display_order
  CASE display_order
    WHEN 4 THEN 10
    ELSE display_order - 1
  END CASE
WHERE display_order BETWEEN 4 AND 10;

但你实际上应该确保你用一个步骤做事。如果不使用 id，则分两步交换将导致编号中断。即：

-- Swap in two steps will not work as demostrated here:

UPDATE mytable SET display_order = 10 WHERE display_order = 11;
-- Now you have two entries with display_order = 10

UPDATE mytable SET display_order = 11 WHERE display_order = 10;
-- Now you have two entries with display_order = 11 (both have been changed)

这是对 mysql 的 CASE 语句的引用。