我有一個包含兩個欄位的 SQL 表：id和order.
CREATE TABLE IF NOT EXISTS article (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`order` INT(11) UNIQUE,
PRIMARY KEY (`id`)
);
在這張表中，我有一些專案：
---- -------
| id | order |
---- -------
| 1 | 0 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
| 7 | 6 |
| 8 | 7 |
| 9 | 8 |
| 10 | 9 |
---- -------
現在我想更改一項的訂單位置：id 為 3 的元素（訂單位置 2）將移動到位置 6。因此，位置 4 和 6 之間的元素（包括最后一個）將不得不減少它們的order欄位。結果應該是這樣的：
---- -------
| id | order |
---- -------
| 1 | 0 |
| 2 | 1 |
| 4 | 2 | ?
| 5 | 3 | |
| 6 | 4 | | Updated items
| 7 | 5 | |
| 3 | 6 | ?
| 8 | 7 |
| 9 | 8 |
| 10 | 9 |
---- -------
當然，第一次更新——用 id3和order欄位更新專案6——很簡單：
UPDATE article
SET article.order = 6
WHERE id = 1;
然后我可以減少位置大于 2 和低于或 euql 小于 6 之間的專案：
UPDATE article
SET article.order = article.order -1
WHERE
article.order > 2
AND
article.order <= 6
;
但是這里有一個問題：order欄位是UNIQUE. 所以我必須首先將它設定NULL為我要移動的專案：
UPDATE article
SET article.order = NULL
WHERE id = 3;
UPDATE article
SET article.order = article.order -1
WHERE
article.order > 2
AND
article.order <= 6
;
UPDATE article
SET article.order = 6
WHERE id = 3;
Is there a way to avoid setting this NULL?
Here is a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=342d714e88e68d3c4c6e0ca1ec8efa6c
uj5u.com熱心網友回復：
有沒有辦法避免設定這個NULL？
不在 MySQL 中。SQL 標準定義了解決這個特定問題的可延遲約束的特性。不幸的是，MySQL 沒有實作 SQL 規范的這一部分。Null 是您唯一的選擇。
現在，當一個約束被標記為可延遲時（如在 PostgreSQL 或 Oracle 中），它的驗證可以推遲到每個 SQL 陳述句執行的末尾，甚至整個事務的末尾；也就是說，它的完全驗證僅在 時發生commit，在完成所有更新并且所有值都會再次良好之后。
如您所見，除非您可以選擇遷移到 PostgreSQL 或 Oracle（極不可能），否則您將無法使用空值。
uj5u.com熱心網友回復：
我會問自己是否真的需要唯一約束？是否有任何功能依賴于唯一的訂單價值？約束的額外開銷是否必要？如果不 -
UPDATE `article`
SET `order` = IF(`id` = 3, 6, `order` - 1)
WHERE `order` BETWEEN 2 AND 6;
db<>小提琴
uj5u.com熱心網友回復：
由于唯一鍵，可能不在 1 次更新中。
但它可以在 2 次更新中完成。
在一些變數的幫助下。
并進行降序排序，最后獲得 NULL。
--
-- move id 3 to position 6
--
SET @id := 3;
SET @orig := (select `order` from article where id = @id);
SET @dest := 6;
-- select @id id,@orig orig ,@dest dest;
UPDATE article
SET `order` = NULL
WHERE id = @id;
UPDATE article
SET `order` =
case
when @orig < @dest and `order` between @orig and @dest
then `order` - 1
when @orig > @dest and `order` between @dest and @orig
then `order` 1
when `order` is null then @dest
else `order`
end
WHERE (
`order` is null
OR `order` between least(@orig,@dest) and greatest(@dest,@orig)
)
ORDER BY if(@orig>@dest,1,-1)*`order` DESC;
關于db<>fiddle 的演示在這里
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/406862.html
標籤：