mysql - 按自定义顺序运行 UPDATE 查询

Question

有没有办法按特定的自定义顺序更新表中的所有记录？我特指一种情况，即实际订单来自“外部”(例如作为 POST 值)。

例如，有一张 table

   id | title | order_idx
   ----------------------
    1 | lorem | 1
    2 | ipsum | 2
    3 | dolor | 3

我有一个提交隐藏字段的表单，按以下顺序携带 ID 值:2、3、1我想更新表，按照表单字段提供的 ID 顺序，将增量数字添加到下一行的 order_idx 中。因此，在这种情况下，最终结果应如下所示:

   id | title | order_idx
   ----------------------
    1 | lorem | 3
    2 | ipsum | 1
    3 | dolor | 2

是否可以在单个 UPDATE 查询中以某种方式完成此操作，而不是在 php 循环中运行 3 个查询(每个查询都包含 WHERE 子句)

Answer 1

您可以在赋值语句中使用条件表达式，如下所示:

UPDATE t 
SET x = CASE 
WHEN 2 THEN 1 
WHEN 3 THEN 2 
WHEN 1 THEN 3
ELSE x
WHERE ....

参数化:

UPDATE t 
SET x = CASE 
WHEN ? THEN 1 
WHEN ? THEN 2 
WHEN ? THEN 3
ELSE x
WHERE ....

无论哪种情况，查询很可能需要动态构建，以考虑不同数量的订购商品。

<小时/>

由于您的评论表明可能有数百...MySQL 对查询长度有限制(reference) .

对于很多人，我会开始推荐不同的方法。

Step 1) CREATE TEMPORARY TABLE `newOrder` (id INT, new_order_idx INT);
Step 2) INSERT id's and their new order into the temp table.
Step 3) UPDATE t INNER JOIN newOrder AS n ON t.id = n.id SET t.order_idx = n.new_order_idx WHERE ...
Step 4) DROP TEMPORARY TABLE newOrder;

流程本身不再是单个查询；但更新是。

<小时/>

注意:如果您有一个涉及 order_idx 的唯一 key ，我完全确定其中任何一个都可以工作。有时，当我需要保持唯一性时，通常的解决方案是在一步中将要调整的记录移动到完全不同的范围，然后在第二步中移动到新位置。 (像 UPDATE t SET order_idx = -1 * order_idx WHERE ... 这样的东西将作为本答案第二部分中第 3 步之前的范围转换。)