仅在字段为空时设置唯一约束

2021-09-14 00:00:00 constraints mysql unique-constraint mysql-5.6

我有这张桌子:

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 设置唯一约束，但仅当 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.

因此每个用户和测试只会存在一个不完整的执行.

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 支持 功能关键部分 自 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 上的演示)

请注意，上述索引还将防止已完成执行的日期重复.如果这些应该是有效的，那么稍微修改的索引就可以工作:

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，那么结合使用常规(非虚拟)列和稍加修改的 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 设置为 true 以表示不完全执行，并在完成后设置为 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 上的演示)

相关文章