如何校驗 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])#39;);

	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 稽核工具。