MYSQL，组合的唯一索引中，处理NULL值的问题

renheshi

最近碰到个问题，用下面的测试表来描述,

create table TEST
(
col_1   INT,
col_2   INT,
col_3   VARCHAR(128) not null,
col_4   VARCHAR(128) not null,
UNIQUE KEY (col_1, col_2, col_3),
CONSTRAINT FOREIGN KEY (col_1) REFERENCES T2(ID) ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY (col_2) REFERENCES T3(ID) ON DELETE CASCADE
)
comment='测试表'
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

本意是要求如下：

INSERT INTO TEST (col_1, col_2, col_3, col_4) VALUES (1, NULL, 'TEST', 'VALID'); //OK
INSERT INTO TEST (col_1, col_2, col_3, col_4) VALUES (1, NULL, 'TEST', 'VALID'); //ERROR

但是实际上MYSQL却可以插入数据，没有提示违反了UNIQUE KEY (col_1, col_2, col_3)，这个是MYSQL对NULL处理的方法引起的。虽然很多用户在给MYSQL提了N个关于这个的BUG，但是MYSQL死抱着SQL specs不放，就是不改或者提供可选的解决方案。
那究竟有没方法可以满足我的本意呢？有人提供了这样的一个方法：

create table TEST
(
ID      INT not null AUTO_INCREMENT,
col_1   INT,
col_2   INT,
col_3   VARCHAR(128) not null,
col_4   VARCHAR(128) not null,
PRIMARY KEY (ID),
CONSTRAINT FOREIGN KEY (col_1) REFERENCES T2(ID) ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY (col_2) REFERENCES T3(ID) ON DELETE CASCADE
)
comment='测试表'
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

首先，去掉了组合的唯一索引，而是加入了一个自增长PK：ID
其次，为此表创建一个triigger:

CREATE TRIGGER `TEST_BEFORE_INS`
BEFORE INSERT ON `TEST` FOR EACH ROW
BEGIN
SET NEW.id=(SELECT id FROM TEST WHERE col_1<=>NEW.col_1 AND col_2<=>NEW.col_2 AND col_3<=>NEW.col_3);
END

再来测试下：

INSERT INTO TEST (col_1, col_2, col_3, col_4) VALUES (1, NULL, 'TEST', 'VALID'); //OK
INSERT INTO TEST (col_1, col_2, col_3, col_4) VALUES (1, NULL, 'TEST', 'VALID'); //ERROR：Duplicate entry '1' for key 'PRIMARY'

报主键重复错误，看来，基本满足了我的本意需求。