别把数据一致性只交给业务代码：MySQL 约束的设计、上线与排错实践
不少系统只在业务代码中校验数据：创建用户前查询邮箱是否存在，提交订单前判断金额是否为正数，删除部门前检查是否仍有关联员工。单个请求下，这些逻辑看起来足够可靠，但只要出现并发写入、脚本导入、后台补数或多个服务共用数据库，应用层校验就可能被绕过。 以“邮箱不能重复”为例，两个请求可能同时执行查询，都得到“不存在”，随后各自插入一行。如果表上没有唯一约束，两次写入都能成功。问题不在于查询写错了，而在于“先检查、再写入”不是一个不可分割的动作。 数据库约束的价值，是把不可违反的数据规则放到最终写入边界。应用层仍应提供友好的参数校验和错误提示，但数据库必须能够拒绝破坏一致性的结果。二者并非互相替代：应用校验负责体验和提前失败，数据库约束负责并发条件下的最终正确性。 ## 约束分别解决什么问题 MySQL 表设计中常用的约束包括： - `NOT NULL`：字段必须有值，适合表达“缺失即非法”。 - `DEFAULT`：调用方未提供字段时使用默认值。它是补全规则，不等于合法性校验。 - `UNIQUE`：一列或多列的组合不能重复，适合用户名、业务编号以及租户内唯一键。 - `PRIMARY KEY`：唯一标识一行，同时隐含非空和唯一性。一张表只能有一个主键，但主键可以包含多列。 - `CHECK`：要求行内表达式成立，例如金额非负、结束时间不早于开始时间。 - `FOREIGN KEY`：限制子表引用的值必须存在于父表，用于维护引用完整性。 约束应表达稳定、局部且能够由数据库判断的事实。诸如“会员在活动期间最多购买三件商品”这类涉及统计、时间窗口或外部状态的规则，通常不能只靠简单约束实现，需要事务、锁、条件更新或专门的库存模型。 ## 建立一组可执行的示例表 下面以用户和订单为例。示例使用 InnoDB，并显式命名约束，便于后续定位错误和执行迁移。 ```sql CREATE DATABASE IF NOT EXISTS constraint_lab CHARACTER SET utf8mb4; USE constraint_lab; CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL, email VARCHAR(254) NOT NULL, display_name VARCHAR(100) NOT NULL, status VARCHAR(16) NOT NULL DEFAULT 'active', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), CONSTRAINT uq_users_tenant_email UNIQUE (tenant_id, email), CONSTRAINT chk_users_status CHECK (status IN ('active', 'disabled')) ) ENGINE=InnoDB; CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL, amount_cent BIGINT UNSIGNED NOT NULL, status VARCHAR(16) NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), CONSTRAINT uq_orders_order_no UNIQUE (order_no), CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'paid', 'cancelled')), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE=InnoDB; ``` 这里用整数分保存金额，避免浮点数表示误差。`BIGINT UNSIGNED` 已经排除了负数，因此不必再添加“金额大于等于零”的重复检查；如果业务要求订单金额必须大于零，则可增加 `CHECK (amount_cent > 0)`。 用户邮箱采用 `(tenant_id, email)` 组合唯一约束，表达“同一租户内唯一”，而不是错误地把邮箱设计成全局唯一。约束设计必须忠实反映业务作用域。 ## 理解几个容易误判的细节 ### UNIQUE 与 NULL 唯一约束并不等同于“必填且唯一”。在 MySQL 中，可空唯一列通常允许出现多个 `NULL`，因为 `NULL` 表示未知，彼此不按普通值判等。如果字段必须存在，应同时声明 `NOT NULL`： ```sql ALTER TABLE users MODIFY email VARCHAR(254) NOT NULL; ``` 不要用空字符串代替未知值。空字符串是一个确定的值，会参与唯一性比较，而且容易把“未填写”和“确实为空”混为一谈。 ### DEFAULT 不会修正显式输入 默认值只在插入语句省略该列时生效。调用方显式传入非法值时，`DEFAULT` 不会替它纠正： ```sql -- 使用默认状态 INSERT INTO users (tenant_id, email, display_name) VALUES (1, 'alice@example.test', 'Alice'); -- 仍会被 CHECK 拒绝，而不是自动改成 active INSERT INTO users (tenant_id, email, display_name, status) VALUES (1, 'bob@example.test', 'Bob', 'unknown'); ``` ### CHECK 适合行内规则 `CHECK` 适合只依赖当前行字段的确定性关系。例如优惠后价格不能高于原价： ```sql CREATE TABLE products ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, price_cent BIGINT UNSIGNED NOT NULL, sale_price_cent BIGINT UNSIGNED NULL, PRIMARY KEY (id), CONSTRAINT chk_sale_price CHECK (sale_price_cent IS NULL OR sale_price_cent 1; ``` 重复数据如何合并不能由通用 SQL 擅自决定。需要明确保留哪一行、其他表引用哪一个用户，以及被合并记录是否需要审计留痕。 ### 2. 查找空值和非法枚举 ```sql SELECT id, tenant_id, email, status FROM users WHERE email IS NULL OR email = '' OR status NOT IN ('active', 'disabled') OR status IS NULL; ``` 修改为 `NOT NULL` 前，必须为现存空值确定真实业务含义。批量填充一个虚假的统一邮箱只会把数据问题隐藏起来，还会与唯一约束冲突。 ### 3. 查找孤儿引用 ```sql SELECT o.id, o.user_id FROM orders AS o LEFT JOIN users AS u ON u.id = o.user_id WHERE u.id IS NULL; ``` 孤儿订单可能需要关联到恢复后的用户、转入异常数据表或阻止迁移。处理方式应由业务规则决定，不能简单删除。 ### 4. 添加约束 完成清理并在预发布环境验证后，再执行变更： ```sql ALTER TABLE users MODIFY email VARCHAR(254) NOT NULL, ADD CONSTRAINT uq_users_tenant_email UNIQUE (tenant_id, email); ``` 如果唯一索引已经存在，就不要重复添加约束。上线前可通过元数据确认当前定义： ```sql SHOW CREATE TABLE users; SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'; ``` 对数据量较大的表，应先在接近生产规模的环境评估耗时、临时空间、锁等待和复制延迟。是否能够在线执行取决于 MySQL 版本、存储引擎以及具体变更类型，不能仅凭 `ALTER TABLE` 语法作保证。 ## 应用程序如何处理约束冲突 约束失败是可预期的业务分支，不应全部转换成含糊的“数据库异常”。以创建用户为例，正确流程是直接尝试插入，并把唯一冲突映射为“邮箱已存在”。可以提前查询改善交互，但最终仍以插入结果为准。 下面是使用 Python DB-API 风格驱动的示意代码。不同驱动的异常类型和属性可能不同，应以实际依赖为准： ```python import os import mysql.connector from mysql.connector import errorcode conn = mysql.connector.connect( host=os.environ['DB_HOST'], user=os.environ['DB_USER'], password=os.environ['DB_PASSWORD'], database=os.environ['DB_NAME'], ) try: with conn.cursor() as cursor: cursor.execute( ''' INSERT INTO users (tenant_id, email, display_name) VALUES (%s, %s, %s) ''', (42, 'alice@example.test', 'Alice'), ) conn.commit() except mysql.connector.IntegrityError as exc: conn.rollback() if exc.errno == errorcode.ER_DUP_ENTRY: raise ValueError('该租户中邮箱已存在') from exc raise finally: conn.close() ``` 参数必须通过驱动占位符传入，不要拼接 SQL。日志中可以记录错误码、约束名、请求标识和表名，但不应直接记录密码、完整个人信息或未经筛选的 SQL 参数。 ## 常见问题 ### 有了数据库约束，还需要应用校验吗？ 需要。应用校验可以更早发现格式错误并返回可理解的提示；数据库约束负责阻止竞态条件、遗漏路径和外部脚本写入非法数据。关键规则应形成两层防线。 ### 所有表都应该使用外键吗？ 不一定。单体系统或边界清晰的关系型数据通常能从外键中受益；高吞吐写入、跨库分片、异步清理等架构可能选择在服务层维护引用关系。但取消外键意味着团队必须补上孤儿检测、删除保护、修复流程和持续审计，而不是取消一致性要求。 ### 唯一约束能否代替幂等设计？ 只能完成其中一部分。唯一约束可以保证同一幂等键不会写入两次，但应用还需要在冲突后读取已有结果，并判断请求参数是否与第一次一致。否则，相同幂等键携带不同内容时可能被错误地当成成功重试。 ### 为什么添加约束后写入报错了？ 这通常说明新约束揭示了原本被默许的数据问题，也可能是约束作用域设计错误。先根据错误码和约束名定位失败规则，再检查真实输入。不要在应用中捕获所有完整性错误后统一忽略，否则外键失败、非空失败和重复键会被混为一谈。 ### 约束名称重要吗？ 重要。显式名称能够让迁移脚本、监控日志和异常映射稳定地指向具体规则。名称可采用 `pk_表名`、`uq_表名_字段`、`fk_子表_父表`、`chk_表名_规则` 等一致格式。 ## 总结 可靠的数据模型不是“表能建出来”就结束，而是要把稳定的不变量写成数据库能够执行的规则。`NOT NULL` 管缺失，`UNIQUE` 管重复，`CHECK` 管行内合法性，`FOREIGN KEY` 管引用关系；应用层则负责输入体验、复杂业务规则和清晰的错误映射。 给旧系统补约束时，应遵循“盘点规则、扫描脏数据、确定修复策略、预演 DDL、分批上线、持续审计”的顺序。约束不是对业务代码的不信任，而是让所有写入入口在并发和故障条件下仍遵守同一套底线。
