MySQL如何实现跨多行多列的手机号全局唯一性校验？

原有方案的核心缺陷 #

为Main number、Number 2、Number 3三个列分别设置唯一约束，仅能拦截同一列下的重复值，无法跨列比对手机号是否已在其他行的任意手机号列存储，因此会出现跨列重复的违规数据，和示例中给出的违规行完全吻合。

可落地的纯数据库层实现方案 #

不需要依赖应用层预查即可完成校验，按方案可靠性从高到低排序：

1. 独立手机号绑定表拆分（最优方案） #

从根本上修正表结构设计缺陷，彻底消除多列存储手机号带来的跨列校验难题：

• 保留原用户主表结构，将三个手机号列从主表移除，单独建立手机号关联表存储所有绑定关系
• 核心表结构参考：

  -- 用户主表：仅存储用户核心属性，不再冗余存储手机号
  CREATE TABLE user_member (
    member_id INT PRIMARY KEY AUTO_INCREMENT
    -- 其他用户业务字段...
  );
  -- 手机号绑定表：存储所有用户的已绑定手机号
  CREATE TABLE user_phone_bind (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL COMMENT '所属用户ID',
    phone_number VARCHAR(20) NOT NULL COMMENT '绑定的手机号',
    bind_type TINYINT NOT NULL COMMENT '绑定类型：1为主号，2为副号1，3为副号2',
    -- 全局唯一索引：拦截任意位置的手机号重复
    UNIQUE KEY uk_global_phone (phone_number),
    -- 联合约束：保证每个用户最多绑定3个不同类型的号码
    UNIQUE KEY uk_member_bind_type (member_id, bind_type)
  );
  

• 校验逻辑完全由数据库约束承接：
  • 所有写入该表的手机号会被全局唯一索引校验，不管是主号还是副号，一旦和已有手机号重复直接抛错拦截
  • 配合联合唯一约束，自然实现每个用户最多绑定3个手机号的业务规则
  • 从结构上完全杜绝跨列重复问题，后续如果需要调整绑定手机号数量的上限，只需要调整bind_type的枚举值即可，无需改表结构

2. 原表结构不动的触发器兜底方案 #

如果因为历史原因无法调整原表结构，可以在原用户表上配置BEFORE INSERT/UPDATE触发器，实现跨列的全局手机号校验：

• 触发器核心逻辑：在数据写入或更新前，遍历校验本次操作涉及的所有手机号，和全表所有行的三列手机号值做比对，一旦发现重复就直接抛出事务回滚错误
• 核心逻辑示例：

  DELIMITER //
  CREATE TRIGGER trg_check_phone_repeat BEFORE INSERT ON user_member
  FOR EACH ROW
  BEGIN
    -- 校验新写入的主号是否在全表任意手机号列存在
    IF EXISTS (
      SELECT 1 FROM user_member
      WHERE Main_number = NEW.Main_number
         OR Number_2 = NEW.Main_number
         OR Number_3 = NEW.Main_number
    ) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '手机号已被注册，禁止重复绑定';
    END IF;
    -- 同理补充对NEW.Number_2、NEW.Number_3的全局重复校验逻辑
    -- 额外校验：同一用户自身绑定的三个手机号不能互相重复
    IF NEW.Main_number = NEW.Number_2 
       OR NEW.Main_number = NEW.Number_3 
       OR NEW.Number_2 = NEW.Number_3 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '同一用户绑定的手机号不能重复';
    END IF;
  END //
  DELIMITER ;
  

• 注意：触发器需要同时配置INSERT和UPDATE场景的校验逻辑，避免用户更新已有手机号时绕过校验。

不推荐的实现方式 #

• 仅靠应用层预先查询手机号是否存在、再执行写入的方案：在并发场景下，查询和写入操作存在时间窗口，会出现两个请求同时查到同一手机号未被占用、先后写入成功的竞态问题，最终依然会产生重复数据。如果要使用应用层方案，也必须搭配数据库层的兜底校验，不能完全依赖应用逻辑。
• 给三个原列分别加独立唯一索引的方式：本质和最初的方案没有区别，依然无法拦截跨列的重复值，解决不了根本问题。
• 给三个原列分别加虚拟生成列、每个生成列单独加唯一索引的方案：本质和原方案无差异，因为每个唯一索引只校验对应列的存储值，不会跨列扫描比对，依然会被跨列重复的数据绕过，因此不具备实际防护能力。

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