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。