更新 mysql 数值字段只知道方向

Question

>我有这样的mysql表：id, [.....], priority

优先级字段用于根据需要对表进行排序。它基于人类智能。 我有一些价值观：

id, priority
1, 1
2, 7
3, 2
4, 4
5, 6
6, 5
7, 3

第二个（最大的）问题：例如，我想根据优先级向上或向下移动第 4 个元素。

第 4 个元素向上（左）和向下（右）移动

    1, 1         1,1
    2, 7         2,7
    3, 2         3,2
    4, 3         4,5
    5, 6         5,6
    6, 5         6,4
    7, 4         7,3

我想用单个MySQL查询来做到这一点，因为它是通过ajax调用的，我不希望在服务器端执行很多查询。

Answer 1

向上或向下更改优先级不会交换优先级吗？这里有一个不错的解决方案：交换 2 行 SQL 中的值。这将交换记录 1 和 4 的优先级

UPDATE yourtable AS t1
JOIN yourtable AS t2 ON 
       (t1.id = 1 AND t2.id = 4)
    OR (t1.id = 4 AND t2.id = 1)
SET
    t1.priority = t2.priority,
    t2.priority = t1.priority

实际上，如果您的优先级是唯一的，则可以在不使用id的情况下进行交换，而只需使用优先级。这将交换优先级 4 和 5：

UPDATE yourtable AS t1
JOIN yourtable AS t2 ON 
       (t1.priority = 4 AND t2.priority = 5)
    OR (t1.priority = 5 AND t2.priority = 4)
SET
    t1.priority = t2.priority,
    t2.priority = t1.priority

Answer 2

如果需要向下移动 4：

UPDATE your_table AS t1 JOIN your_table AS t2 ON (t1.id = 4 AND t2.priority = t1.priority + 1) SET t1.priority = t2.priority, t2.priority = t1.priority

或者，比如说，如果您需要向上移动 2：

UPDATE your_table AS t1 JOIN your_table AS t2 ON (t1.id = 2 AND t2.priority = t1.priority - 1) SET t1.priority = t2.priority, t2.priority = t1.priority

Answer 3

$int_prio = (1 - ((int)($_POST['move_up'] == 'TRUE') * 2));
$int_id = (int)$_POST['id'];
mysql_query("
INSERT INTO table_name (id, priority)
SELECT h2.id, 
    (h2.priority + ({$int_prio} * (1 - (2 * (h.id != h2.id))))) AS priority
FROM (  SELECT id, priority
        FROM table_name
        WHERE id = {$int_id}
        HAVING priority BETWEEN 2 AND ( SELECT MAX(priority)
                                        FROM priority
                                        GROUP BY '1') - 1) AS h
INNER JOIN priority AS h2
ON h.priority IN (h2.priority, h2.priority + {$int_prio})
ON DUPLICATE KEY UPDATE priority = VALUES(priority)
");

基本上更改 _POST 美元（最好比更安全）和table_name。

我添加了验证，以便它不会尝试更改最高和最低优先级。

抱歉，如果您发现上述查询令人困惑，但这是一种在单个查询中更新所有内容并添加验证的方法。