Vitess v8 PlanetScale MySQL db约束错误

Question

使用Vitess v8 (PlanetScale DB)表：

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;

测试数据：

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');

第一步：(如果你不明白为什么这一步是重要的，那也没关系)

UPDATE `Channel` SET `position` = 0.5 WHERE `id` = 3;

Ergest提供的编辑查询：

UPDATE `Channel` AS `ch`
INNER JOIN ( 
  SELECT `id` as `id2`,
  ( SELECT COUNT(*)
    FROM (SELECT * FROM `Channel`) AS `b`
    WHERE `b`.`position` <= `a`.`position`
  ) AS `p`
  FROM `Channel` AS `a` WHERE `guildId` = 1
) AS `td` ON `ch`.`id` = `td`.`id2`
SET `ch`.`position` = `td`.`p`;

错误：

error: code = AlreadyExists desc = Duplicate entry '1-2' for key 'Channel.Channel_guildId_position_key' (errno 1062)

Answer 1

您正在使用ORDER BY。

MySQL文档：

多表语法:对于多表语法，更新在table_references中命名的每个表中符合条件的行。每个匹配行都会被更新一次，即使它多次匹配条件。对于多表语法，不能使用ORDER和LIMIT .

当您不使用ORDER BY position ASC时，我认为在子查询中使用LIMIT没有意义。

尝试使用INNER JOIN

UPDATE `Channel` AS `ch`,
INNER JOIN ( 
            SELECT`id`,
                      ( SELECT COUNT(*)
                        FROM `Channel` AS `b`
                        WHERE `b`.`position` <= `a`.`position`
                      ) AS `p`
           FROM `Channel` AS `a` WHERE `guildId` = 1
           ) AS `td` on   `ch`.`id` = `td`.`id`; 
SET `ch`.`position` = `td`.`p` ;