我正在创建一个包含以下内容的表:
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)
它创建一个带有clustered索引的索引表,“index kind”是“唯一的”,并跨越三个聚集列:
mysql screen http://img510.imageshack.us/img510/930/mysqlscreenshot.th.jpg
然而,一旦我转储了我的数据(没有抛出异常),我看到唯一性约束失败了:
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有人知道我哪里做错了吗?
发布于 2010-03-26 11:11:58
MySQL不认为空值相等;因此,为什么唯一约束看起来不起作用。要解决此问题,可以向表中添加一个计算列,其定义如下:
nullCatch as (case when inyear is null then '-1' else inyear)将此列替换为约束中的'inyear‘:
CONSTRAINT UNIQUE CLUSTERED (name, year, nullCatch)https://stackoverflow.com/questions/2520894
复制相似问题