MySQL mengekang indeks unik hanya jika lajur lain kosong

Question

Saya cuba menulis semula skema Postgres agar sesuai dengan dialek MySQL (8.0.32). Seperti yang anda ketahui, MySQL tidak menyokong indeks separa. Dalam kes berikut, akan terdapat indeks yang menguatkuasakan data unik pada customer_group.name hanya jika deleted_at adalah batal. Ini masuk akal kerana tidak ada gunanya memastikan entri yang dipadamkan adalah unik. Walau bagaimanapun, saya tidak faham cara melaksanakan kekangan yang sama tanpa pengindeksan separa. BOLEH CREATETABLE"custo

P粉463291248 · Answer

Menggunakan lajur yang dikira, anda boleh melakukan perkara berikut:

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

Dengan indeks:

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

P.S. Saya tidak menggunakan ANSI_QUOTES.

Lihat juga: DBFIDDLE

P粉311563823 · Answer

MySQL tidak menyokong indeks separa, tetapi menyokong indeks ekspresi (bermula dengan MySQL 8.0). Berikut ialah demo:

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

Memandangkan UNIQUE mengikut peraturan NULL ANSI, jika lajur kedua indeks unik ialah NULL, mungkin terdapat sebarang bilangan pendua dalam lajur pertama. Lajur kedua apabila NULL tidak sama dengan mana-mana baris lain, jadi ia sentiasa "unik".

Jadi jika lajur kedua ialah nilai bukan NULL tetap hanya apabila "deleted_at" ialah NULL, maka "name" adalah unik pada semua baris dengan "deleted_at" ialah 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 |
+----+-------+---------------------+

Saya terpaksa menukar jenis "nama" kerana anda tidak boleh mencipta indeks pada lajur TEXT dalam MySQL dan ia mungkin terlalu panjang untuk had indeks 3072 bait.

Juga menukar KUNCI PRIMER untuk menghilangkan nama kekangan. MySQL akan sentiasa menamakan kunci utama dengan mudah PRIMARY.