MySQL如何在不破坏唯一约束或使用占位符行的情况下更新多行

Question

在MySQL中， 我有一个名为 Channel 的表（由 Prisma ORM 自动生成）

CREATE TABLE `Channel` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `guildId` INTEGER UNSIGNED NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `description` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `position` INTEGER NOT NULL,
    `parentId` INTEGER NULL,
    `ratelimit` INTEGER NOT NULL DEFAULT 0,
    `type` ENUM('textChannel', 'categoryChannel') NOT NULL,

    INDEX `Channel_guildId_idx`(`guildId`),
    UNIQUE INDEX `Channel_guildId_name_key`(`guildId`, `name`),
    UNIQUE INDEX `Channel_guildId_position_key`(`guildId`, `position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如您所见，有一个 UNIQUE INDEX 约束 Channel_guild_position_key

我怎样才能做一些不破坏这个的事情唯一约束，例如：（我可以使用 SELECT * FROM Channel WHERE guildId = $guildId 获取具有相同 guild_id 的所有行）

更新通道current 的位置字段值到 x（假设 x 大于 current.position） 更新 current.position 和位置 x 之间的所有通道，并将其位置字段减少 1 将 current.position 更新为位置 x

基本上，我想将通道移动到某个位置，并且新位置和旧位置之间的所有内容都需要向前推进。但我被我设置的约束所阻止，该约束使 (position, guildId) 成为唯一标识符，因此需要一个临时值来完成此任务。我正在寻找一种不会打破约束的单操作解决方案

注意：这包括（可能）超过两行，并且单个交换不起作用。

如果您仍然不明白这个问题，请考虑聊天频道，当客户端将频道拖动到不同的位置时，我想更新“之前”和“之后”位置“之间”的所有频道位置。

例子： 这里表格有 3 行

INSERT INTO Channel (id, guildId, name, position, type) VALUES (1, 1, 'ch1', 1, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (2, 1, 'ch2', 2, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (3, 1, 'ch3', 3, 'textChannel');

现在，假设我想将通道 'ch3' 移动到位置 1，那么 'ch1' 和 'ch2' 的位置将分别更改为 2、3。 但如果我运行这些操作：（假设我已经获得了 ID）

$guildId = 0

$originalPositionOfChannel3 = 3

$newPosition = 0

UPDATE Channel SET position = $newPosition WHERE guildId = $guildId AND name = 'ch3';
UPDATE Channel SET position = position + 1 WHERE guildId = $guildId AND position < $originalPositionOfChannel3 AND position >= $newPosition;```