問題描述

我有這張桌子:

CREATE TABLE `executed_tests` (
    `id` INTEGER AUTO_INCREMENT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `test_id` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `completed_date` DATE,
    PRIMARY KEY (`id`)
);

我想對字段 user_id 和 test_id 設(shè)置唯一約束，但僅當(dāng) conclusion_date 為空時.如果 conclusion_date 不為 null，則約束不適用.

I want to set up an unique constraint on fields user_id and test_id, but only when conclusion_date is null. If conclusion_date is not null, the constraint doesn't apply.

因此每個用戶和測試只會存在一個不完整的執(zhí)行.

So there will exist only one incomplete execution per user and test.

像這樣:

UNIQUE(`user_id`, `test_id`) WHEN (`completed_date` IS NULL)

如何在 MySQL 5.6 上完成此操作?

How can I accomplish this on MySQL 5.6?

推薦答案

MySQL 支持 功能關(guān)鍵部分 自 8.0.13.

MySQL supports functional key parts since 8.0.13.

• 如果您的版本足夠新，您可以將索引定義為:

• If your version is sufficiently recent you can define your index as:

UNIQUE(`user_id`, `test_id`, (IFNULL(`completed_date`, -1)))

(dbfiddle.uk 上的演示)

請注意，上述索引還將防止已完成執(zhí)行的日期重復(fù).如果這些應(yīng)該是有效的，那么稍微修改的索引就可以工作:

Note that the above index will also prevent duplciate dates for completed executions. If those should be valid then a slightly modified index would work:

UNIQUE(`user_id`, `test_id`, (
    CASE WHEN `completed_date` IS NOT NULL
    THEN NULL
    ELSE 0
END))

(dbfiddle.uk 上的演示)

雖然后來開始覺得有點臟;)

Although then it starts to feel a bit dirty ;)

如果您至少有 5.7 版本，您可以使用(虛擬)生成列 作為解決方法:

If you have at least version 5.7 you can use a (virtual) generated column as workaround:

CREATE TABLE `executed_tests` (
    `id` INTEGER AUTO_INCREMENT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `test_id` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `completed_date` DATE,
    `_helper` CHAR(11) AS (IFNULL(`completed_date`, -1)),
    PRIMARY KEY (`id`),
    UNIQUE(`user_id`, `test_id`, `_helper`)
);

(dbfiddle.uk 上的演示)

如果您堅持使用 5.6，那么結(jié)合使用常規(guī)(非虛擬)列和稍加修改的 INSERT 語句即可:

If you are stuck on 5.6 then a combination of a regular (non-virtual) column and slightly modified INSERT statements would work:

CREATE TABLE `executed_tests` (
    `id` INTEGER AUTO_INCREMENT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `test_id` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `completed_date` DATE,
    `is_open` BOOLEAN,
    PRIMARY KEY (`id`),
    UNIQUE(`user_id`, `test_id`, `is_open`)
);

在這種情況下，您可以將 is_open 設(shè)置為 true 以表示不完全執(zhí)行，并在完成后設(shè)置為 NULL，利用兩個 NULLs 被視為不相等.

In this case you would set is_open to true for incomplete executions and to NULL after completion, making use of the fact that two NULLs are treated as not equal.

(dbfiddle.uk 上的演示)

這篇關(guān)于僅在字段為空時設(shè)置唯一約束的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！