有个场景是用户表:
CREATE TABLE user_info
(
id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'row id',
country_id BIGINT NOT NULL COMMENT 'tel no country code',
tel_no CHAR(11) NOT NULL COMMENT 'tel no',
gender TINYINT NOT NULL COMMENT 'gender, 1 male, 2 female',
birthday DATE NOT NULL COMMENT 'birthday',
deleted BOOLEAN NOT NULL COMMENT 'deleted, value is TRUE if user delete, otherwise is false',
PRIMARY KEY (id),
FOREIGN KEY fk_ui_ci (country_id) REFERENCES country (id)
) ENGINE InnoDB COMMENT 'user unmodified info';
deleted
表示是否注销,现在需要使用 UNIQUE KEY 在 country_id
与 tel_no
上,确保手机号不会重复注册,尴尬的是,多次注销会产生问题。
MySQL 没什么好的解决方案,一个可行的是将 deleted
改为 VARCHAR 或者 TIMESTAMP 类型,为 NULL 的为未删除,否则已删除,UNIQUE KEY 加上 deleted
字段即可。
一个更好的方式是这样创建 UNIQUE KEY CREATE UNIQUE INDEX uk_ui_ci_tn ON user_info(country_id, tel_no) WHERE deleted = FALSE;
,不过这是 PostgreSQL 的语法,换言之这种方式是劝你换成 PG。
技术是有惯性的,不然我早就换了。