MySQL仅当另一列为空时才约束唯一索引-PHP中文网问答

我正在尝试重写 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 可以重复。

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

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 | +----+-------+---------------------+

全部回复(2)

P粉4632912482024-01-17 09:12:31 2楼

使用计算列，您可以执行以下操作：

带有索引：

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

附注我确实没有使用 ANSI_QUOTES。

参见：DBFIDDLE

赞 +0

添加回复

P粉3115638232024-01-17 00:54:29 1楼

MySQL不支持部分索引，但支持表达式索引（从MySQL 8.0开始）。这是一个演示：

由于 UNIQUE 遵循 ANSI 的 NULL 规则，因此如果唯一索引的第二列为 NULL，则第一列中可能存在任意数量的重复项。第二列为 NULL 时不等于任何其他行，因此它始终是“唯一的”。

因此，如果仅当“deleted_at”为 NULL 时第二列是固定的非 NULL 值，则“name”在“deleted_at”为 NULL 的所有行上都是唯一的。

我必须更改“名称”的类型，因为您无法在 MySQL 中的 TEXT 列上创建索引，对于索引的 3072 字节限制来说，它可能太长了。

还更改了 PRIMARY KEY 以省略约束名称。 MySQL 将始终将主键简单地命名为 PRIMARY。