MySQL唯一集群约束未按预期进行约束 [英] MySQL unique clustered constraint not constraining as expected

问题描述

我正在创建一个表:

CREATE TABLE movies
(
 id     INT       AUTO_INCREMENT PRIMARY KEY,
 name   CHAR(255) NOT NULL,
 year   INT       NOT NULL,
 inyear CHAR(10), 
 CONSTRAINT UNIQUE CLUSTERED (name, year, inyear)
);

(这是jdbc SQL)

(this is jdbc SQL)

创建一个带有clustered索引的MySQL表，索引种类"是唯一的"，并且跨越三个聚集列:

Which creates a MySQL table with a clustered index, "index kind" is "unique", and spans the three clustered columns:

mysql 屏幕 http://img510.imageshack.us/img510/930/mysqlscreenshot.th.jpg
全尺寸

但是，一旦我转储我的数据(没有抛出异常)，我就会发现唯一性约束失败了:

However, once I dump my data (without exceptions thrown), I see that the uniqueness constraint has failed:

SELECT * FROM movies
WHERE name = 'Flawless' AND year = 2007 AND inyear IS NULL;

给出:

id,     name,       year, inyear
162169, 'Flawless', 2007, NULL
162170, 'Flawless', 2007, NULL

有谁知道我在这里做错了什么?

Does anyone know what I'm doing wrong here?

推荐答案

MySQL 不认为 N​​ULL 值相等；因此，为什么唯一约束似乎不起作用.为了解决这个问题，您可以向表中添加一个计算列，其定义为:

MySQL does not consider NULL values as equal; hence, why the unique constraint appears to not be working. To get around this, you can add a computed column to the table which is defined as:

nullCatch as (case when inyear is null then '-1' else inyear)

将此列替换为约束中的inyear":

Substitute this column in for 'inyear' in the constraint:

 CONSTRAINT UNIQUE CLUSTERED (name, year, nullCatch)

这篇关于MySQL唯一集群约束未按预期进行约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！