如何校验 MySQL&Oracle 时间字段合规性？
作者：余振兴 爱可生 DBA 团队成员，热衷技术分享、编写技术文档。 本文来源：原创投稿 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 背景信息 在数据迁移或者数据库低版本升级到高版本过程中，经常会遇到一些由于低版本数据库参数设置过于宽松，导致插入的时间数据不符合规范的情况而触发报错，每次报错再发现处理起来较为麻烦，是否有提前发现这类不规范数据的方法，以下基于 Oracle 和 MySQL 各提供一种可行性方案作为参考。 Oracle 时间数据校验方法 2.1 创建测试表并插⼊测试数据 CREATE TABLE T1(ID NUMBER,CREATE_DATE VARCHAR2(20)); INSERT INTO T1 SELECT 1, '2007-01-01' FROM DUAL; INSERT INTO T1 SELECT 2, '2007-99-01' FROM DUAL; -- 异常数据 INSERT INTO T1 SELECT 3, '2007-12-31' FROM DUAL; INSERT INTO T1 SELECT 4, '2007-12-99' FROM DUAL; -- 异常数据 INSERT INTO T1 SELECT 5, '2005-12-29 03:-1:119' FROM DUAL; -- 异常数据 INSERT INTO T1 SELECT 6, '2015-12-29 00:-1:49' FROM DUAL; -- 异常数据 2.2 创建对该表的错误日志记录 Oracle 可以调用 DBMS_ERRLOG.CREATE_ERROR_LOG 包对 SQL 的错误进行记录，用来记录下异常数据的情况，十分好用。 参数含义如下 T1 为表名 T1_ERROR 为对该表操作的错误记录临时表 DEMO 为该表的所属用户 EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERROR','DEMO'); 2.3 创建并插入数据到临时表，验证时间数据有效性 -- 创建临时表做数据校验 CREATE TABLE T1_TMP(ID NUMBER,CREATE_DATE DATE); -- 插入数据到临时表验证时间数据有效性（增加LOG ERRORS将错误信息输出到错误日志表） INSERT INTO T1_TMP SELECT ID, TO_DATE(CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') FROM T1 LOG ERRORS INTO T1_ERROR REJECT LIMIT UNLIMITED; 2.4 校验错误记录 SELECT * FROM DEMO.T1_ERROR; 其中 ID 列为该表的主键，可用来快速定位异常数据行。 MySQL 数据库的方法 3.1 创建测试表模拟低版本不规范数据 -- 创建测试表 SQL> CREATE TABLE T_ORDER( ID BIGINT AUTO_INCREMENT PRIMARY KEY, ORDER_NAME VARCHAR(64), ORDER_TIME DATETIME); -- 设置不严谨的SQL_MODE允许插入不规范的时间数据 SQL> SET SQL_MODE='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES'; SQL> INSERT INTO T_ORDER(ORDER_NAME,ORDER_TIME) VALUES ('MySQL','2022-01-01'), ('Oracle','2022-02-30'), ('Redis','9999-00-04'), ('MongoDB','0000-03-00'); -- 数据示例 SQL> SELECT * FROM T_ORDER; +----+------------+---------------------+ | ID | ORDER_NAME | ORDER_TIME | +----+------------+---------------------+ | 1 | MySQL | 2022-01-01 00:00:00 | | 2 | Oracle | 2022-02-30 00:00:00 | | 3 | Redis | 9999-00-04 00:00:00 | | 4 | MongoDB | 0000-03-00 00:00:00 | +----+------------+---------------------+ 3.2 创建临时表进行数据规范性验证 -- 创建临时表，只包含主键ID和需要校验的时间字段 SQL> CREATE TABLE T_ORDER_CHECK( ID BIGINT AUTO_INCREMENT PRIMARY KEY, ORDER_TIME DATETIME); -- 设置SQL_MODE为5.7或8.0高版本默认值 SQL> SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; -- 使用INSERT IGNORE语法插入数据到临时CHECK表，忽略插入过程中的错误 SQL> INSERT IGNORE INTO T_ORDER_CHECK(ID,ORDER_TIME) SELECT ID,ORDER_TIME FROM T_ORDER; 3.3 数据比对 将临时表与正式表做关联查询，比对出不一致的数据即可。 SQL> SELECT T.ID, T.ORDER_TIME AS ORDER_TIME, TC.ORDER_TIME AS ORDER_TIME_TMP FROM T_ORDER T INNER JOIN T_ORDER_CHECK TC ON T.ID=TC.ID WHERE T.ORDER_TIME<>TC.ORDER_TIME; +----+---------------------+---------------------+ | ID | ORDER_TIME | ORDER_TIME_TMP | +----+---------------------+---------------------+ | 2 | 2022-02-30 00:00:00 | 0000-00-00 00:00:00 | | 3 | 9999-00-04 00:00:00 | 0000-00-00 00:00:00 | | 4 | 0000-03-00 00:00:00 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+ 一个取巧的小方法 对时间字段用正则表达式匹配，对有严谨性要求的情况还是得用以上方式，正则匹配烧脑。 -- Oracle 数据库 SELECT * FROM T1 WHERE NOT REGEXP_LIKE(CREATE_DATE,'^((?:19|20)\d\d)-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$'); ID CREATE_DATE ---------- -------------------- 2 2007-99-01 4 2007-12-99 5 2005-12-29 03:-1:119 6 2015-12-29 00:-1:49 -- MySQL 数据库 -- 略，匹配规则还在调试中 关于 SQLE 爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。 SQLE 获取 类型 地址 版本库 https://github.com/actiontech/sqle 文档 https://actiontech.github.io/sqle-docs/ 发布信息 https://github.com/actiontech/sqle/releases 数据审核插件开发文档 https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/auditplugin_development.html
