MySQL唯一键与NULL值

在MySQL中，唯一键用于确保表中某一列或多列的值是唯一的。当我们在插入或更新数据时，MySQL会自动检查唯一键是否出现冲突。如果是，MySQL就会拒绝添加记录或更新这个具有冲突的记录。但是，如果是一个唯一键列含有NULL值，该怎么办呢？

阅读更多：MySQL 教程

唯一键和NULL值

让我们来看看一个示例表：

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(50),
  UNIQUE KEY (username, email)
);

这个表有三个列：id，username，password和email。username和email组成了唯一键，确保表中任何一组具有相同的username和email的记录都是唯一的。

现在，我们插入几条数据来测试一下：

INSERT INTO user (username, password, email) VALUES ('jenny', '123456', 'jenny@gmail.com');
INSERT INTO user (username, password, email) VALUES ('jenny', '456789', 'jenny@gmail.com');

插入第二条数据时，MySQL会拒绝插入该数据，因为其中一个唯一键列（即username列）已经存在了。这是因为username是非NULL值，所以唯一键规则适用。

但是，如果我们插入一个具有NULL值的记录：

INSERT INTO user (username, password, email) VALUES ('jenny', '654321', NULL);

MySQL并不会拒绝这个插入，因为唯一键规则在遇到NULL值时不适用。因此，可以有多个NULL行。

忽略NULL的方式

如果你想忽略NULL值，这里有两种可行的方法：

增加一个辅助列

可以增加一个辅助列来存储非NULL值。例如，在上面的示例表中，我们可以添加一个名为unique_id的列：

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(50),
  unique_id VARCHAR(50),
  UNIQUE KEY (username, unique_id)
);

这个新列将存储唯一值，即使原始唯一键列中一列或多列包含NULL值。对于NULL值，可以将unique_id列设置为固定值或空字符串。

使用COALESCE函数

另一种忽略NULL值的方法是使用COALESCE函数。COALESCE函数返回参数列表中的第一个非NULL值，如果所有值都是NULL，则返回NULL。因此，我们可以使用COALESCE函数将NULL值转换为空字符串，例如：

INSERT INTO user (username, password, email) VALUES ('jenny', '987654', COALESCE(NULL, ''));

总结

MySQL的唯一键和NULL值可以一起使用，但需要注意唯一键规则在遇到NULL值时不适用。如果需要忽略NULL值，请增加一个辅助列或使用COALESCE函数。希望此篇文章对你有所帮助！