目錄背景信息Oracle 時間數(shù)據(jù)校驗方法創(chuàng)建測試表并插?測試數(shù)據(jù)創(chuàng)建對該表的錯誤日志記錄創(chuàng)建并插入數(shù)據(jù)到臨時表，驗證時間數(shù)據(jù)有效性校驗錯誤記錄MySQL 數(shù)據(jù)庫的方法創(chuàng)建測試表模擬低版本不規(guī)范數(shù)據(jù)創(chuàng)建臨時表進行數(shù)據(jù)規(guī)范性驗證數(shù)據(jù)比對一個取巧的小方法關(guān)于 SQLESQLE 獲取背景信息

在數(shù)據(jù)遷移或者數(shù)據(jù)庫低版本升級到高版本過程中，經(jīng)常會遇到一些由于低版本數(shù)據(jù)庫參數(shù)設(shè)置過于寬松，導(dǎo)致插入的時間數(shù)據(jù)不符合規(guī)范的情況而觸發(fā)報錯，每次報錯再發(fā)現(xiàn)處理起來較為麻煩，是否有提前發(fā)現(xiàn)這類不規(guī)范數(shù)據(jù)的方法，以下基于 Oracle 和 MySQL 各提供一種可行性方案作為參考。

Oracle 時間數(shù)據(jù)校驗方法創(chuàng)建測試表并插?測試數(shù)據(jù)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; -- 異常數(shù)據(jù)INSERT INTO T1 SELECT 3, '2007-12-31' FROM DUAL;INSERT INTO T1 SELECT 4, '2007-12-99' FROM DUAL; -- 異常數(shù)據(jù)INSERT INTO T1 SELECT 5, '2005-12-29 03:-1:119' FROM DUAL; -- 異常數(shù)據(jù)INSERT INTO T1 SELECT 6, '2015-12-29 00:-1:49' FROM DUAL; -- 異常數(shù)據(jù)創(chuàng)建對該表的錯誤日志記錄Oracle 可以調(diào)用 DBMS_ERRLOG.CREATE_ERROR_LOG 包對 SQL 的錯誤進行記錄，用來記錄下異常數(shù)據(jù)的情況，十分好用。

參數(shù)含義如下

T1 為表名T1_ERROR 為對該表操作的錯誤記錄臨時表DEMO 為該表的所屬用戶EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERROR','DEMO');創(chuàng)建并插入數(shù)據(jù)到臨時表，驗證時間數(shù)據(jù)有效性-- 創(chuàng)建臨時表做數(shù)據(jù)校驗CREATE TABLE T1_TMP(ID NUMBER,CREATE_DATE DATE);-- 插入數(shù)據(jù)到臨時表驗證時間數(shù)據(jù)有效性（增加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 列為該表的主鍵，可用來快速定位異常數(shù)據(jù)行。

MySQL 數(shù)據(jù)庫的方法創(chuàng)建測試表模擬低版本不規(guī)范數(shù)據(jù)-- 創(chuàng)建測試表SQL> CREATE TABLE T_ORDER( ID BIGINT AUTO_INCREMENT PRIMARY KEY, ORDER_NAME VARCHAR(64), ORDER_TIME DATETIME);-- 設(shè)置不嚴(yán)謹(jǐn)?shù)腟QL_MODE允許插入不規(guī)范的時間數(shù)據(jù)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');-- 數(shù)據(jù)示例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 |+----+------------+---------------------+創(chuàng)建臨時表進行數(shù)據(jù)規(guī)范性驗證-- 創(chuàng)建臨時表，只包含主鍵ID和需要校驗的時間字段SQL> CREATE TABLE T_ORDER_CHECK( ID BIGINT AUTO_INCREMENT PRIMARY KEY, ORDER_TIME DATETIME);-- 設(shè)置SQL_MODE為5.7或8.0高版本默認(rèn)值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語法插入數(shù)據(jù)到臨時CHECK表，忽略插入過程中的錯誤SQL> INSERT IGNORE INTO T_ORDER_CHECK(ID,ORDER_TIME) SELECT ID,ORDER_TIME FROM T_ORDER;數(shù)據(jù)比對

將臨時表與正式表做關(guān)聯(lián)查詢，比對出不一致的數(shù)據(jù)即可。

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 |+----+---------------------+---------------------+一個取巧的小方法

對時間字段用正則表達式匹配，對有嚴(yán)謹(jǐn)性要求的情況還是得用以上方式，正則匹配燒腦。

-- Oracle 數(shù)據(jù)庫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 數(shù)據(jù)庫-- 略，匹配規(guī)則還在調(diào)試中關(guān)于 SQLE

愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者，支持多場景審核，支持標(biāo)準(zhǔn)化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴展的 SQL 審核工具。

SQLE 獲取類型地址版本庫https://github.com/actiontech/sqle文檔https://actiontech.github.io/sqle-docs/發(fā)布信息https://github.com/actiontech/sqle/releases數(shù)據(jù)審核插件開發(fā)文檔https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit...

以上就是詳解如何校驗MySQL及Oracle時間字段合規(guī)性的詳細(xì)內(nèi)容，更多關(guān)于MySQL Oracle時間字段合規(guī)性的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！