SQL 如何设计数据校验报表？_技术教程

优先写在 SQL 里。数据库层校验能早暴露问题、避免脏数据入库，且批量校验更高效；但复杂业务规则应放应用层，SQL 适合 CHECK 约束、格式、范围、外键等校验，需注意性能、兼容性与可维护性。

优先写在 SQL 里。数据库层校验能早暴露问题、避免脏数据入库，且对批量校验（比如每日跑一次全量数据比对）更高效。但要注意：复杂业务规则（如“用户等级变更需同步更新积分有效期”）不适合塞进 SQL，容易变成难以维护的嵌套 CASE WHEN 或临时表链。

适合 SQL 校验的场景：CHECK 约束、NOT NULL、字段格式（用 REGEXP_LIKE 或 LIKE）、数值范围（WHERE amount ）、外键缺失（LEFT JOIN ... WHERE ref_id IS NULL）
SQL 校验的代价：全表扫描可能锁表或拖慢查询；某些函数（如 REGEXP_LIKE）在 MySQL 8.0 前不支持，PostgreSQL 需开启 pg_trgm
别把校验逻辑硬编码进报表 SQL —— 把规则抽成视图或 CTE，例如建一个 v_data_quality_rules 视图存各字段预期值域，再 JOIN 校验

直接 SELECT * FROM

t WHERE status NOT IN ('active', 'inactive') 只告诉你“有异常”，但不知道哪条记录、哪个时间点、谁触发的。必须带上下文。

每条校验结果至少包含四列：check_name（如 'order_amount_negative'）、table_name、record_id（主键或业务唯一键）、check_time（用 NOW() 或调度时间戳）
用 UNION ALL 合并多条校验，避免多个独立查询难聚合 —— 但注意各子查询列名和类型要严格一致，否则 PostgreSQL 会报错，MySQL 可能静默转类型

示例片段：

SELECT 'null_phone' AS check_name, 'users' AS table_name, id AS record_id, NOW() AS check_time
FROM users 
WHERE phone IS NULL OR TRIM(phone) = ''

校验逻辑随业务增长不断叠加，全表扫一遍可能从 2 秒涨到 15 分钟。关键不是加索引，而是控制校验粒度。

区分「强校验」和「弱校验」：强校验（如主键重复、金额为负）走实时触发器或事务内检查；报表只跑弱校验（如“近 7 天订单中，95% 的收货地址应含省名”），且加 WHERE create_time >= CURRENT_DATE - INTERVAL '7 days'
对大表，用采样校验代替全量：PostgreSQL 可用 TABLESAMPLE SYSTEM (1)，MySQL 8.0+ 支持 SELECT ... FROM t TABLESAMPLE (1)
禁止在 WHERE 中对字段做函数操作（如 WHERE UPPER(email) = 'A@B.COM'），会导致索引失效 —— 应该在校验前统一清洗，或建函数索引

报表跑出 127 条异常，没人看等于没校验。得把结果导出成结构化数据，而不是截图发钉钉。

输出格式优先选 CSV 或 JSON：CSV 方便 Excel 打开排查，JSON 适合程序解析（比如自动创建 Jira issue）；避免 HTML 表格，解析成本高且易被邮件客户端过滤
在 SQL 中用 STRING_AGG 拼接问题详情（如 STRING_AGG(DISTINCT CONCAT('id=', id, ', phone=', phone), '; ')），避免一行一记录导致结果集过大
最常被忽略的一点：校验 SQL 必须带 timeout 控制（如 PostgreSQL 的 statement_timeout = '30s'），否则某条慢校验卡住整个调度任务，后续日报全断