如何验证MySQL和Oracle时间字段的一致性？

背景信息

在数据迁移或者从低版本数据库升级到高版本的过程中，我们经常会遇到低版本数据库的参数设置过于宽松，导致插入的情况时间数据不符合规范，导致错误。每次报错都很难解决。有没有办法提前检测出这种不规则的数据呢？以下是基于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是该表的表操作的错误记录。创建临时表并插入数据，验证时态数据的有效性

  -- 创建临时表做数据校验
  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 数据库
  -- 略，匹配规则还在调试中

  作者：于振兴

  艾克森DBA团队成员，喜欢分享技术和撰写技术文档。