MySQL唯一索引可以为null吗|极客笔记

MySQL唯一索引可以为null吗

在MySQL中，唯一索引是用来确保表中的某一列或一组列的值是唯一的。当我们在某一列上创建了唯一索引之后，系统会自动限制该列的值不能重复，并且该列的值可以为NULL或者为空字符串。

唯一索引和NULL值的关系

唯一索引在MySQL中对NULL值的处理有一定的特殊性。可以为NULL值的列在唯一索引中只允许有一行NULL记录，也就是说NULL在唯一索引中只能出现一次。这个规则的目的是为了确保NULL值在唯一索引列中仅有一个，因为如果允许多个NULL值出现在唯一索引列中，将无法满足唯一性约束。

当我们在创建唯一索引时，如果某一列允许为NULL值，系统会在索引中为该列的NULL值分配一个空间。这个空间只能有一个，用来存放该列的NULL值。在其他索引中，NULL值都被视为不同的值，但是在唯一索引中，NULL值则被视为相同的值。

示例

下面我们通过一个示例来验证在MySQL中唯一索引中对NULL值的处理。

创建测试表

首先我们创建一个名为test_table的表，表中包含一个允许为NULL值的字段col1：

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 VARCHAR(50) NULL,
    UNIQUE INDEX unique_col1 (col1)
);

插入数据

接着我们插入一些数据，包括NULL值和其他值：

INSERT INTO test_table (col1) VALUES 
(NULL),
('value1'),
('value2'),
(NULL),
('value3');

查询数据

然后我们查询表中的数据，并查看唯一索引unique_col1中的内容：

SELECT * FROM test_table;
SHOW INDEX FROM test_table;

运行以上查询语句后，我们可以看到表中的数据如下：

+----+--------+
| id | col1   |
+----+--------+
| 1  | NULL   |
| 2  | value1 |
| 3  | value2 |
| 4  | NULL   |
| 5  | value3 |
+----+--------+

唯一索引unique_col1中的内容如下：

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_table | 0          | unique_col1 | 1            | col1        | A         | 3           | NULL     | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

从上面的结果可以看到，唯一索引unique_col1中NULL值只出现了一次，同时其他不为NULL的值也都满足唯一性约束。这再次证明了在MySQL中唯一索引可以包含NULL值，但NULL值在唯一索引中只能出现一次。

总结

在MySQL中，唯一索引可以包含NULL值，但是对于允许为NULL值的列，在唯一索引中只能出现一次。通过唯一索引的方式，我们可以有效地保障表中某一列或一组列的数据的唯一性，提高数据的完整性和一致性。在实际开发中，我们可以根据业务需求和数据特性来选择是否在某一列上创建唯一索引，以达到最佳的性能和约束效果。