mysql数据库中的约束有哪些_mysql完整约束解析

mysql中真正生效的约束有五类：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、default；CHECK在8.0.16+才生效，5.7及以前完全忽略。

MySQL 中真正生效的约束有哪些

MySQL（尤其 InnoDB 引擎）实际严格 enforce 的约束只有五类：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、DEFAULT。注意：CHECK 约束在 MySQL 8.0.16+ 才真正生效，而 5.7 及更早版本中写 CHECK 会被解析但**完全忽略**——你加了也白加，不会报错，也不会拦截非法数据。

• NOT NULL：字段值强制非空，NULL 值插入直接报错 Error 1048 (23000): column 'xxx' cannot be null
• UNIQUE：单列或组合列值全局唯一，允许任意多个 NULL（因为 NULL != NULL）
• PRIMARY KEY = NOT NULL + UNIQUE，且每张表最多一个；自动创建名为 PRIMARY 的聚簇索引
• FOREIGN KEY：仅 InnoDB 支持，要求关联字段类型严格一致（含无符号、字符集、排序规则），且被引用列必须有索引（通常是主键或唯一键）
• DEFAULT：只在 INSERT 时缺省字段值才触发，对 UPDATE 无效；支持常量或表达式（如 CURRENT_TIMESTAMP），但不支持函数调用如 NOW() 在老版本中可能被静默转为常量

为什么 UNIQUE 允许 NULL 却仍能建唯一索引

这是 MySQL 对 SQL 标准的实现选择：唯一索引只对**非 NULL 值**做去重判断。所以插入两行 email = NULL 不会冲突，但插入 email = 'a@b.com' 两次就会失败。

• 实际效果等价于：索引项只包含非 NULL 值，NULL 值不落索引 B+Tree 叶子节点
• 这意味着 select ... WHERE email IS NULL 无法走该唯一索引（全表扫描），而 WHERE email = 'x' 可以
• 如果业务真需要“邮箱为空也算唯一”，得用 COALESCE(email, 'NULL_PLACEHOLDER') 配合函数索引（MySQL 8.0.13+）或额外加计算列

外键不是“加了就安全”，这些配置必须同步打开

InnoDB 默认启用外键检查，但有两个隐藏开关决定它是否真起作用：

• 必须确保 FOREIGN_KEY_CHECKS = 1（默认开启，但批量导入时常被设为 0 后忘记恢复）
• 主表和从表都必须是 ENGINE=InnoDB；MyISAM 表加 FOREIGN KEY 语法能通过，但实际不生效
• 被引用字段（如 dept.id）必须有索引，否则建外键会报错 ERROR 1005 (HY000): Can't create table ... (errno: 150)
• 字段类型要完全一致：比如主表是 int UNSIGNED，从表外键也必须是 INT UNSIGNED，差一个 UNSIGNED 就失败

约束名不能随便改，删约束必须知道默认名

MySQL 对约束命名很“懒”：没显式指定名时，会按规则自动生成。删约束时若名字不对，ALTER TABLE ... DROP CONSTRAINT 会报错 ERROR 1025 (HY000)。

• 主键约束名永远是 PRIMARY（不可改，也不用写）
• UNIQUE 约束默认名 = 列名（如 UNIQUE(email) → 约束名 email）
• FOREIGN KEY 默认名由系统生成（形如 fk_user_dept_id），查法：SELECT constraint_name FROM information_schema.TABLE_CONSTRAINTS WHERE table_name = 'users' AND constraint_type = 'FOREIGN KEY';
• 推荐建表时主动命名：CONSTRAINT uk_user_email UNIQUE(email)，后续维护清晰可控

约束的底层本质不是“校验逻辑”，而是索引与事务机制的协同产物。比如 UNIQUE 依赖唯一索引实现 O(log n) 查重，FOREIGN KEY 依赖事务的加锁行为防止脏写——理解这点，才能预判高并发下约束带来的锁竞争和性能拐点。