最近遇到个问题是关于mysql 数据库 unique key 唯一约束索引添加的问题。
问题场景:
根据业务需要给数据库表添加一个多字段联合唯一索引 unique index。使用表的phone, shop_code 字段关联创建。
其中表phone字段允许null,默认为null。
操作:
使用sql执行脚本执行添加索引sql。执行时报错。提示有创建唯一约束字段存在重复值。
排查问题:
SELECT COUNT(DISTINCT `phone`, `shop_code`) from customer;
1. 使用该sql查询需要创建unique index 的字段的过滤重复总数是否 等于 count(*) 全表总数。发现不相等,脚本报错。查看数据,发现phone值存在大量为null的数据。
2. 然后查询mysql使用手册,官方设定mysql 的 unique索引时允许包含多个null值。
以下时官方在常见unique的文档的解释,大意是说:
唯一索引
创建一个UNIQUE索引时,创建字段的值必须不能重复。但是一个UNIQUE 索引,可以包含NULL值
原文:
Unique Indexes
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.
If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows:
_rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.
Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.
官方文档连接: https://dev.mysql.com/doc/refman/5.6/en/create-index.html.
得到结论,mysql 唯一索引是支持多个null值存在的。
解决:
修复脚本的重复数据校验逻辑,过滤空值。
SELECT COUNT(DISTINCT `phone`, `shop_code`) FROM customer WHERE phone is not null;
脚本成功执行,唯一索引成功添加。