在数据迁移或者数据库低版本升级到高版本过程中,经常会遇到一些由于低版本数据库参数设置过于宽松,导致插入的时间数据不符合规范的情况而触发报错,每次报错再发现处理起来较为麻烦,是否有提前发现这类不规范数据的方法,以下基于 Oracle 和 MySQL 各提供一种可行性方案作为参考。
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; -- 异常数据
DBMS_ERRLOG.CREATE_ERROR_LOG
包对 SQL 的错误进行记录,用来记录下异常数据的情况,十分好用。参数含义如下
T1
为表名T1_ERROR
为对该表操作的错误记录临时表DEMO
为该表的所属用户EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERROR','DEMO');
-- 创建临时表做数据校验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;
SELECT * FROM DEMO.T1_ERROR;
其中 ID 列为该表的主键,可用来快速定位异常数据行。
-- 创建测试表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 |+----+------------+---------------------+
-- 创建临时表,只包含主键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;
将临时表与正式表做关联查询,比对出不一致的数据即可。
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)dd)-(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 是一款面向数据库使用者和管理者,支持多场景审核,支持标准化上线流程,原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。
茶杯头甜蜜终章dlc 官方手机版v1.0.0.3
下载火柴人传说暗影格斗内置菜单 最新版v3.0.1
下载荒野乱斗测试服 安卓版v61.10.3
下载荒野乱斗彩虹服 安卓版v61.10.3
下载寒霜启示录 安卓版v1.25.10
寒霜启示录是一款生存模拟游戏,不少玩家可能对于末日都有着自己
末日城堡免广告版 安卓最新版v0.7.1
末日城堡免广告版是一款非常好玩的模拟经营类游戏,内部可以不看
甜蜜人生模拟器 最新版v1.4.5
甜蜜人生模拟器是一款非常好玩的模拟恋爱手游,玩家在这里能够对
武器锻造师内置功能菜单 v10.4
武器锻造师内置菜单版是游戏的破解版本,在该版本中为玩家提供了
开放空间overfield 安卓版v1.0.5
开放空间Overfield是一款箱庭养成经营手游,让你在广阔