MySQL唯一集群约束未按预期进行约束

本文介绍了MySQL唯一集群约束未按预期进行约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在创建一个表:

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 屏幕 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)