MySQL schränkt den eindeutigen Index nur ein, wenn eine andere Spalte leer ist

Question

Ich versuche, das Postgres-Schema so umzuschreiben, dass es zum MySQL-Dialekt (8.0.32) passt. Wie Sie wissen, unterstützt MySQL keine Teilindizes. Im folgenden Fall gibt es einen Index, der nur dann eindeutige Daten für customer_group.name erzwingt, wenn delete_at null ist. Dies ist sinnvoll, da es keinen Sinn macht, sicherzustellen, dass gelöschte Einträge eindeutig sind. Allerdings verstehe ich nicht, wie man dieselben Einschränkungen ohne teilweise Indizierung implementieren kann. ERSTELLBAR"custo

P粉463291248 · Answer

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

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

P粉311563823 · Answer

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。