MySQL僅當另一列為空時才約束唯一索引

Question

我正在嘗試重寫 Postgres 架構以適應 MySQL (8.0.32) 方言。如您所知，MySQL 不支援部分索引。

在下列情況下，只有當 deleted_at 為 null 時，才會存在一個索引，該索引會強制執行 customer_group.name 上的唯一資料。

這是有道理的，因為確保刪除的條目是唯一的是沒有意義的。但是，我不明白如何在沒有部分索引的情況下實現相同的約束。

CREATE TABLE
  "customer_group" (
    "id" integer NOT NULL AUTO_INCREMENT,
    "created_at" datetime NOT NULL DEFAULT NOW(),
    "updated_at" datetime NOT NULL DEFAULT NOW(),
    "deleted_at" datetime,
    "name" text NOT NULL,
    "metadata" text,
    CONSTRAINT "PK_142c3338-da81-4d0c-8cd8-490bb41cd187" PRIMARY KEY ("id")
  );

-- solve this
-- ensure name is unique when one customer_group is not deleted
CREATE UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name")
WHERE
  "deleted_at" IS NULL;

附註我確實在使用 ANSI_QUOTES。

有人建議我嘗試使用 2 列而不是一列的唯一索引。但是，如果約束是UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name", "deleted_at") 那麼我會得到與我想要的相反的結果： deleted_at 為NULL，則name 可以重複。

Answer 1

使用計算列，您可以執行以下操作：

CREATE TABLE
  `customer_group` (
    `id` integer NOT NULL AUTO_INCREMENT,
    `created_at` datetime NOT NULL DEFAULT NOW(),
    `updated_at` datetime NOT NULL DEFAULT NOW(),
    `deleted_at` datetime,
    `name` text NOT NULL,
    `metadata` text,
    `deleted_row` bit as (CASE WHEN deleted_at is null THEN false ELSE true END),
    CONSTRAINT `PK_142c3338-da81-4d0c-8cd8-490bb41cd187` PRIMARY KEY (`id`)
  );

有索引：

CREATE UNIQUE INDEX `IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9` ON 
  `customer_group` (`deleted_row`,`name`(100),`id`);

附註我確實沒有使用 ANSI_QUOTES。

請參閱：DBFIDDLE

Answer 2

MySQL不支援部分索引，但支援表達式索引（從MySQL 8.0開始）。這是一個示範：

CREATE TABLE
  "customer_group" (
    "id" integer NOT NULL AUTO_INCREMENT,
    "created_at" datetime NOT NULL DEFAULT NOW(),
    "updated_at" datetime NOT NULL DEFAULT NOW(),
    "deleted_at" datetime,
    "name" VARCHAR(50) NOT NULL,
    "metadata" text,
    PRIMARY KEY ("id")
  );

CREATE UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group"
  ("name", (CASE WHEN "deleted_at" IS NULL THEN true ELSE NULL END));

由於 UNIQUE 遵循 ANSI 的 NULL 規則，因此如果唯一索引的第二列為 NULL，則第一列中可能存在任意數量的重複項。第二列為 NULL 時不等於任何其他行，因此它總是「唯一的」。

因此，如果僅當「deleted_at」為 NULL 時第二列是固定的非 NULL 值，則「name」在「deleted_at」為 NULL 的所有行上都是唯一的。

INSERT INTO customer_group SET name = 'name1', deleted_at = NULL
Query OK, 1 row affected (0.00 sec)

INSERT INTO customer_group SET name = 'name1', deleted_at = NULL
ERROR 1062 (23000): Duplicate entry 'name1-1' for key 'customer_group.IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9'

INSERT INTO customer_group SET name = 'name2', deleted_at = '2018-01-01'
Query OK, 1 row affected (0.00 sec)

INSERT INTO customer_group SET name = 'name2', deleted_at = '2018-01-01'
Query OK, 1 row affected (0.00 sec)

SELECT id, name, deleted_at FROM customer_group;
+----+-------+---------------------+
| id | name  | deleted_at          |
+----+-------+---------------------+
|  1 | name1 | NULL                |
|  3 | name2 | 2018-01-01 00:00:00 |
|  4 | name2 | 2018-01-01 00:00:00 |
+----+-------+---------------------+

我必須更改「名稱」的類型，因為您無法在 MySQL 中的 TEXT 列上建立索引，對於索引的 3072 位元組限制來說，它可能太長了。

也更改了 PRIMARY KEY 以省略約束名稱。 MySQL 將始終將主鍵簡單地命名為 PRIMARY。