关于mysql:如何校验-MySQLOracle-时间字段合规性

作者：余振兴

爱可生 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_TMPFROM T_ORDER T INNER JOIN T_ORDER_CHECK TC ON T.ID=TC.IDWHERE 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 获取