MySQL：唯一索引不尊重`null`值 [英] MySQL: unique index not respecting `null` values

问题描述

我无法在MySQL v5.6.20中创建一个唯一的索引 null - 值。我在这里检查了类似的答案。但不能解决我的问题。

I am having trouble creating a unique index respecting null-values in MySQL v5.6.20. I checked similar answers here. but couldn't solve my problem with them.

三个其他表（ date_list_assignment ）。表的目的是将 date_list -entries映射到课程类别和/或date_list类别。第一列因此是强制性的，而后两个不是。如果后两个 null ，日期列表条目将被声明为全局。

I want a table which has references to three other table (date_list_assignment). The purpose of the table is the mapping of date_list-entries to course categories and/or date_list categories. The first column is therefore mandatory, while the latter two are not. If the latter two are null the date list entries is declared global. If a date list entry has no entry within this table, it is not shown anywhere.

以下是一些条目及其含义的例子：

Here are some examples of entries and their meaning:

# entry which is global within course category 2
date_list_id: 1, course_category_id: 2, date_list_category_id: null

# entry which is global
date_list_id: 1, course_category_id: null, date_list_category_id: null

# entry which is only visible within course category 2 and date list category 17
date_list_id: 1, course_category_id: 2, date_list_category_id: 17

简短版本：我想确保

Short version: I want to make sure, that any combination of the three columns stays unique within the table...no matter if the values are null or not.

我有下面的表：

CREATE TABLE `date_list_assignment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_list_id` int(11) NOT NULL,
  `course_category_id` int(11) DEFAULT NULL,
  `date_list_category_id` int(11) DEFAULT NULL,
  `created` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `IN_relation_unique` (`date_list_id`,`course_category_id`,`date_list_category_id`),

  KEY `FK_date_list_assignment_user_created` (`created_by`),
  KEY `FK_date_list_assignment_user_updated` (`updated_by`),
  KEY `FK_date_list_assignment_course_category` (`course_category_id`),
  KEY `FK_date_list_assignment_date_list_category` (`date_list_category_id`),

  CONSTRAINT `FK_date_list_assignment_course_category` FOREIGN KEY (`course_category_id`) REFERENCES `course_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_date_list` FOREIGN KEY (`date_list_id`) REFERENCES `date_list` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_date_list_category` FOREIGN KEY (`date_list_category_id`) REFERENCES `date_list_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_user_created` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_user_updated` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

问题

我在三列上声明了一个唯一索引（ IN_relation_unique ）。但是，我仍然可以创建具有例如以下值的相同行：

Problem

As you can see, I declared a unique index (IN_relation_unique) on the three columns. However, I still can create to identical rows having for example these values:

date_list_id: 1, course_category_id: 2, date_list_category_id: null

我知道，这种行为在当前MySQL版本中有所改变，这也是我使用的原因一个索引而不是允许空值的复合PK。

I am aware that some of this behavior changed in current MySQL-versions which is also the reason I use an index and not a composite PK allowing null-values.

例如这个回答说明这是MySQL中的预期行为。如果是这样，你如何实现这一点，因为它也不再可能与复合PKs允许null值？？

For exmaple this answer states, that this is expected behavior in MySQL. If so, how can you achieve this since its also no longer possible with composite PKs allowing null-values either!?

感谢您的帮助！

推荐答案

是的，这是MySQL中的预期行为（实际上在ANSI-92中）。 NULL 值在唯一键中不视为相等的值，主键不能按定义包含 NULL p>

Yes, this is the expected behaviour in MySQL (in fact in ANSI-92 too). NULL values are not treated as equal values in unique keys and primary keys can not contain NULL values by definition.

当且仅当表中的两行在唯一列中具有相同的非空值时，才满足唯一约束。此外，如果使用PRIMARY KEY定义了唯一约束，则它要求指定列或列中的所有值都不是空值。

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In addition, if the unique constraint was defined with PRIMARY KEY, then it requires that none of the values in the specified column or columns be the null value.

（ http：//www.contrib.andrew。 cmu.edu/~shadow/sql/sql1992.txt ）

由于您对可空列有外键约束，我建议添加一个（父ID是0的记录），并将 NOT NULL 约束添加到列中。 （此外，您可以添加虚拟值 DEFAULT ）。

Since you have the foreign key constraint on the nullable column, I would suggest to add a dummy value to the parent tables which states the fact that the parent is irrelevant or not determined (the records where ID = 0 maybe) and add the NOT NULL constraint to the column. (Additionally you can add the dummy value as DEFAULT).

这篇关于MySQL：唯一索引不尊重`null`值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！