MySQL实现条件唯一性

create unique index xpto_table_idx1 on xpto_table(case when status_x <>5 then obj_x else null end);

-- 创建示例表并添加数据
create table t1 (a int primary key, b int, c int);
insert into t1 values (1,1,10),(2,2,20),(3,3,30);

-- 创建虚拟列
alter table t1 add flag int generated always as (if(b =1,  b, null)) virtual;
-- 创建唯一约束
alter table t1 add constraint unique (flag);

mysql> insert into t1 (a,b,c) values (5,2,200),(6,3,300);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 (a,b,c) values (4,1,100);
ERROR 1062 (23000): Duplicate entry '1' for key 'flag'
mysql> select * from t1;
+---+------+------+------+
| a | b    | c    | flag |
+---+------+------+------+
| 1 |    1 |   10 |    1 |
| 2 |    2 |   20 | NULL |
| 3 |    3 |   30 | NULL |
| 5 |    2 |  200 | NULL |
| 6 |    3 |  300 | NULL |
+---+------+------+------+
5 rows in set (0.00 sec)