在具有唯一列的复制密钥更新上

Question

有一个表，其中包含数据表的一些UI设置，通常如：

| ID  |   name | alias |   pos  | def_pos | disp |
+-----+--------+-------+--------+---------+------+
|  1  |  name1 | bar   |   1    |    1    |   1  | 
|  2  |  name2 | foo   |   3    |    2    |   1  |
|  3  |  name3 | bar   |   2    |    3    |   1  |
|  4  |  name4 | baz   |   4    |    4    |   0  |

• ID:PRIMARY
• 名称：UNIQUE

这里pos可以由用户通过在图形界面中拖动列来更新。

由于name和id是唯一的，并且我一次更新多行，因此在更改值时，我使用INSERT和ON DUPLICATE KEY UPDATE而不是UPDATE。例如，如果交换上面例子中的第2行和第3行：

INSERT INTO
ui_data_columns (id, name, pos)
VALUES
(2, '', 2),
(3, '', 3)
ON DUPLICATE KEY UPDATE
pos = VALUES(pos)

根据：

• MySQL中的多次更新
• https://stackoverflow.com/a/34866431

到目前为止还不错：p

---

现在我的想法是给pos和def_pos添加UNIQUE约束，因为一列不能有相同的位置。不可能从UI中将两个列值设置为相同，但最好有约束，因为它们是唯一的，并且。。。好吧，学习。

挑战就变成了，如果一个人试图使用KEY UPDATE，那么在交换两个值时就会遇到冲突。因此，当pos存在时，我不能说pos = VALUES(pos)——即使它在同一句话中得到了纠正(若你们明白我的意思(。假设它们按顺序插入，则通常为：

INSERT INTO ui_data_columns (id, name, pos)
VALUES (2, '', 2)
ON DUPLICATE KEY UPDATE pos = VALUES(pos)
# Error: pos = 2 exists

即使这个修复是：

INSERT INTO ui_data_columns (id, name, pos)
VALUES (3, '', 3)
ON DUPLICATE KEY UPDATE pos = VALUES(pos)

怎么做？

---

小想法：

• 一些查询魔术
• 是否使用不带约束的表的临时副本清除并填充
• 先删除有问题的记录，然后插入
• 删除插入期间的约束，然后重新应用

Answer 1

一个技巧-使用中间体。即执行3次更新，而不是2次。

create table test (id int primary key, pos int unique);
insert into test values (1,1),(2,3),(3,2),(4,4);
select * from test;

id|pos-：|--：1|13|22|34|4

<blockquote\

insert into test values
(3,3), (2,2)
on duplicate key update pos = values(pos);

>

键"test.pos"的重复条目"3">

select * from test;

id|pos-：|--：1|13|22|34|4

<blockquote\

insert into test values
(2,0), (3,3), (2,2)
on duplicate key update pos = values(pos);
select * from test;

>

id|pos-：|--：1|12|23|34|4

db<>小提琴这里