mysql - 仅当字段为空时设置唯一约束

Question

我有这张表:

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 为 null 时。如果 conclusion_date 不为空，则约束不适用。

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

类似这样的事情:

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

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

Answer 1

MySQL支持functional key parts自 8.0.13 起。

• 如果您的版本足够新，您可以将索引定义为:

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

  ( Demo on dbfiddle.uk )

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

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

  ( Demo on dbfiddle.uk )

  尽管这样就开始感觉有点脏;)

• 如果您的版本至少为 5.7，则可以使用(虚拟)generated column作为解决方法:

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

  ( Demo on dbfiddle.uk )

• 如果您卡在 5.6 上，那么常规(非虚拟)列和稍作修改的 INSERT 语句的组合将起作用:

  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，利用两个 NULL 被视为不相等。

  ( Demo on dbfiddle.uk )