MYSQL:分区表保持 id 唯一

Question

我们正在使用具有如下架构的表:-

CREATE TABLE `user_subscription` (
`ID` varchar(40) NOT NULL,
`COL1` varchar(40) NOT NULL,
`COL2` varchar(30) NOT NULL,
`COL3` datetime NOT NULL,
`COL4` datetime NOT NULL,
`ARCHIVE` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
)

现在我们想对 ARCHIVE 列进行分区。 ARCHIVE 只能有 2 个值 0 或 1，因此有 2 个分区。

实际上，在我们的案例中，我们将分区用作归档过程。要进行分区，我们需要将 ARCHIVE 列作为主键的一部分。但这里的问题是 2 行可以具有相同的 ID 和不同的 ARCHIVE 列值。实际上这不是我们的主要问题，因为 2 行将在不同的分区中。问题是当我们将其中一个的存档列值更新为另一个以将其中一行移动到存档分区时，它将不允许我们更新给出“重复错误”的条目。

有人可以在这方面提供帮助吗？

Answer 1

不幸的是，

A UNIQUE INDEX (or a PRIMARY KEY) must include all columns in the table's partitioning function

而且由于 MySQL 也不支持检查约束，我能想到的唯一丑陋的解决方法是通过触发器手动强制执行唯一性:

CREATE TABLE t (
  id INT NOT NULL,
  archived TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id, archived),  -- required by MySQL limitation on partitioning
)
PARTITION BY LIST(archived) (
  PARTITION pActive VALUES IN (0),
  PARTITION pArchived VALUES IN (1)
);

CREATE TRIGGER tInsert
BEFORE INSERT ON t FOR EACH ROW
CALL checkUnique(NEW.id);

CREATE TRIGGER tUpdate
BEFORE UPDATE ON t FOR EACH ROW
CALL checkUnique(NEW.id);

DELIMITER //
CREATE PROCEDURE checkUnique(pId INT)
BEGIN
  DECLARE flag INT;
  DECLARE message VARCHAR(50);
  SELECT id INTO flag FROM t WHERE id = pId;
  IF flag IS NOT NULL THEN
    -- the below tries to mimic the error raised
    -- by a regular UNIQUE constraint violation
    SET message = CONCAT("Duplicate entry '", pId, "'");
    SIGNAL SQLSTATE "23000" SET
      MYSQL_ERRNO = 1062,
      MESSAGE_TEXT = message,
      COLUMN_NAME = "id";
  END IF;
END //

( fiddle )

MySQL's limitations on partitioning作为一个令人沮丧的人(特别是它不支持外键)，我建议不要完全使用它，直到表变得如此之大以至于它成为一个实际问题。