在一个或很少的查询中更新多个MySQL行的显示顺序 [英] Updating display order of multiple MySQL rows in one or very few queries

问题描述

我有一张桌子，说有20行，每行有一个显示顺序的数字(1-20).

I have a table with say 20 rows each with a number for display order (1-20).

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

在管理区域中，您可以拖动行或手动为每行键入一个新数字.

From an admin area you can drag the rows around or type a new number manually for each row.

对于每行循环遍历UPDATE查询当然不是很好，在一个或很少几个查询中有什么替代方法可用于更新20行或更多(50-200 +)中的一个单元格?

Surely it's not good to loop over an UPDATE query for every row, what's an alternative in one or very few queries suitable for updating one cell in 20 rows or even more, 50-200+?

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

A lot of good responses and ideas. I might expand on the ideas I've considered so far:

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

One array string: I could have the order in a string listing the unique row IDs in the order I want - eg rows 1,9,2,6,23. When the order is updated, a hidden field updates with JavaScript and adds that to the database or a text file when complete:

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

单独更新每一行:这是我要避免的操作，但是它很少更改，因此每周或每月一次匹配20-30次通话可能不是问题，因此通常，我通常只是在每一行上调用UPDATE:

Update each row individually: This is what I was trying to avoid but it would only be changed very infrequently so 20-30 calls in one hit once a week or month might not be a problem so simply calling UPDATE on each row is what I usually do:

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;

推荐答案

soulmerge的回答让我思考，我认为这是一个更好的解决方案.您需要做的是使用IN()选择具有ID的行，然后使用CASE设置值.

soulmerge's answer made me think and I think this is a better solution. What you need to do is select the rows with the id using IN() and then use CASE to set the value.

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的内置Sortable功能中获得了一组序列化(和有序)的ID.所以数组看起来像这样:

I have a need for this in my current app. In PHP, I'm getting a serialized (and ordered) set of id's from jQuery UI's built-in Sortable feature. So the array looks like this:

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

要生成单个MySQL更新查询，请执行以下操作:

To generate the single MySQL update query, I do this:

$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列表.这对我来说效果很好.

The "implode" at the end just gives me my ID list for IN(). This works beautifully for me.

这篇关于在一个或很少的查询中更新多个MySQL行的显示顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！