我正在创建一个表:
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 不认为 NULL 值相等;因此,为什么唯一约束似乎不起作用.为了解决这个问题,您可以向表中添加一个计算列,其定义为:
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唯一集群约束未按预期进行约束
发布评论