MySQL中包含NULL值的Unique key|极客教程

MySQL中包含NULL值的Unique key

在MySQL中，Unique key通常用于确保表中某个字段的唯一性，以避免数据重复插入。但是，当表中包含NULL值时，这种情况会变得有些棘手，因为NULL值在MySQL中不被视为一个普通的值。

举个例子，我们有一个“students”表来存储学生信息，其中包含学生ID和邮箱。由于每个学生只能有一个邮箱，我们想要为“email”字段创建一个Unique key：

CREATE TABLE students (
  id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE,
  PRIMARY KEY (id)
);

但是，如果我们在插入新学生信息时，留空了“email”字段，那么我们会遇到一个问题：MySQL将NULL视为唯一的，因此我们只能插入一个NULL值，之后在尝试插入其他学生时就会出现唯一性约束错误：

INSERT INTO students (email) VALUES (NULL);  -- 成功
INSERT INTO students (email) VALUES (NULL);  -- 失败

这是因为Unique key在MySQL中被设计为不允许有重复的值，而NULL被视为一个独特的值。因此，我们需要解决这个问题并确保我们可以插入任意数量的NULL值。

阅读更多：MySQL 教程

解决方法

要解决这个问题，我们可以向Unique key添加一个特殊的索引，该索引允许多个NULL值。我们称之为“Partial Index”。

CREATE TABLE students (
  id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(255),
  UNIQUE email_nullable (email),
  PRIMARY KEY (id)
);

-- 允许重复的email（不包括NULL）
INSERT INTO students (email) VALUES ('john@example.com');
INSERT INTO students (email) VALUES ('jane@example.com');

-- 允许重复的email（包括NULL）
INSERT INTO students (email) VALUES ('david@example.com');
INSERT INTO students (email) VALUES ('daniel@example.com');
INSERT INTO students (email) VALUES (NULL);
INSERT INTO students (email) VALUES (NULL);  -- 这里不会失败