MySQL能否设置唯一整数列：仅非空默认值可重复，其余值唯一？

这个需求完全可以实现，核心是利用MySQL唯一约束允许多个NULL值存在的特性，结合函数索引/生成列做条件判断即可。

实现原理 #

• MySQL的唯一约束不会对NULL值做重复校验，同个唯一键列可以存在任意多条NULL记录
• 我们只需要对目标字段做条件转换：值为1111时转换为NULL，其他值保留原值，再对转换后的结果加唯一约束即可

MySQL 8.0+ 最优实现（函数索引） #

直接在创建表时加条件唯一函数索引，不需要额外新增字段：

CREATE TABLE your_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- 目标字段设置非空+默认值1111
    target_num INT NOT NULL DEFAULT 1111,
    -- 其他业务字段
    other_col VARCHAR(20),
    -- 条件唯一索引：只有target_num不等于1111时才做唯一性校验
    UNIQUE KEY uk_target_num ((CASE WHEN target_num != 1111 THEN target_num END))
);

MySQL 5.7 兼容实现（生成列） #

如果使用5.7及以下不支持函数索引的版本，可以通过显式新增虚拟生成列实现，效果完全一致：

CREATE TABLE your_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    target_num INT NOT NULL DEFAULT 1111,
    -- 新增虚拟生成列，不占用存储空间
    unique_check INT GENERATED ALWAYS AS (CASE WHEN target_num != 1111 THEN target_num END) VIRTUAL,
    other_col VARCHAR(20),
    -- 对生成列加唯一约束
    UNIQUE KEY uk_unique_check (unique_check)
);

效果验证 #

你可以用以下语句测试是否符合要求：

-- 插入3条不指定target_num的记录，都会自动填充1111，全部插入成功
INSERT INTO your_table (other_col) VALUES ('test1'), ('test2'), ('test3');
-- 插入主动指定target_num为2222的记录，成功
INSERT INTO your_table (target_num, other_col) VALUES (2222, 'test4');
-- 再次插入target_num为2222的记录，触发唯一约束报错，符合要求
INSERT INTO your_table (target_num, other_col) VALUES (2222, 'test5');

内容的提问来源于stack exchange，提问作者arun