mysql - 唯一(多列)和一列中的空值

Question

我有简单的类别表。类别可以有父类别(par_cat 列)，如果它是主类别并且具有相同的父类别，则不应有 2 个或多个具有相同名称或 url 的类别。

此表的代码:

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(10) unsigned NOT NULL,
  `par_cat` int(10) unsigned DEFAULT NULL,
  `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'pl',
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `accepted` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `priority` int(10) unsigned NOT NULL DEFAULT '1000',
  `entries` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;



ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`), 
  ADD UNIQUE KEY `categories_name_par_cat_unique` (`name`,`par_cat`), 
  ADD UNIQUE KEY `categories_url_par_cat_unique` (`url`,`par_cat`), 
  ADD KEY `categories_par_cat_foreign` (`par_cat`);


ALTER TABLE `categories`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

ALTER TABLE `categories`ADD CONSTRAINT `categories_par_cat_foreign` 
  FOREIGN KEY (`par_cat`) REFERENCES `categories` (`id`);

问题是，即使我有唯一的键，它也不起作用。如果我尝试将 par_cat 设置为 null 且名称和 url 相同的数据库 2 个类别插入到数据库中，那么这两个类别可以毫无问题地插入数据库(它们应该't)。但是，如果我为这些类别选择其他 par_cat(例如 1 假设存在 id 1 的类别)，则只会插入第一条记录(这是所需的行为)。

问题 - 如何处理这种情况？我读到:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

但是，如果我在多个列上具有唯一性，我预计情况并非如此(只有 par_cat 可以为空，name 和 url 不能无效的)。因为 par_cat 引用了同一张表的 id 但某些类别没有父类别，它应该允许 null 值。

Answer 1

这按 SQL 标准的定义工作。 NULL 表示未知。如果你有两条 par_cat = NULL 和 name = 'X' 的记录，那么这两个 NULL 不被认为持有相同的值。因此它们不会违反唯一键约束。 (好吧，有人可能会争辩说，NULL 仍然可能表示相同的值，但应用此规则将使使用唯一索引和可空字段几乎不可能，因为 NULL 也可能表示 1、2 或其他其他值。所以他们很好地定义了它，就像我认为的那样。)

由于 MySQL 不支持您可以在 ISNULL(par_cat,-1), name 上创建索引的功能索引，因此您唯一的选择是使 par_cat 成为一个具有 0 或 -1 的 NOT NULL 列或者“没有 parent ”的任何东西，如果你想让你的约束起作用。