詳解如何校驗MySQL及Oracle時間字段合規(guī)性
更新時間：2023年06月27日 10:45:42 作者：愛可生云數(shù)據(jù)庫
這篇文章主要為大家介紹了如何校驗MySQL及Oracle時間字段合規(guī)性詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
背景信息
在數(shù)據(jù)遷移或者數(shù)據(jù)庫低版本升級到高版本過程中，經(jīng)常會遇到一些由于低版本數(shù)據(jù)庫參數(shù)設置過于寬松，導致插入的時間數(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 可以調用
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ù)腟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);
-- 設置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語法插入數(shù)據(jù)到臨時CHECK表，忽略插入過程中的錯誤
SQL> INSERT IGNORE INTO T_ORDER_CHECK(ID,ORDER_TIME) SELECT ID,ORDER_TIME FROM T_ORDER;數(shù)據(jù)比對
將臨時表與正式表做關聯(lián)查詢，比對出不一致的數(shù)據(jù)即可。
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 數(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ī)則還在調試中關于 SQLE
愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者，支持多場景審核，支持標準化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴展的 SQL 審核工具。
SQLE 獲取
以上就是詳解如何校驗MySQL及Oracle時間字段合規(guī)性的詳細內容，更多關于MySQL Oracle時間字段合規(guī)性的資料請關注腳本之家其它相關文章！
相關文章
MySQL數(shù)據(jù)庫查詢性能優(yōu)化策略
這篇文章主要介紹了MySQL數(shù)據(jù)庫查詢性能優(yōu)化的策略，幫助大家的工作學習提高MySQL數(shù)據(jù)庫的性能，感興趣的朋友可以了解下2020-08-08
文章介紹了MySQL數(shù)據(jù)庫的一些關鍵配置參數(shù),包括InnoDB緩沖池、redolog緩沖區(qū)、日志緩沖區(qū)等,并解釋了這些參數(shù)在數(shù)據(jù)庫性能和事務一致性中的作用,文章通過比喻和實際建議幫助讀者理解這些配置參數(shù)的合理設置2025-12-12
MySQL中字符串長度一般指數(shù)據(jù)庫表中一個字段或列中存儲的字符串的最大長度,有時我們需要測量字符串長度,來保證表結構及數(shù)據(jù)庫性能穩(wěn)定,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫怎么正確查詢字符串長度的相關資料,需要的朋友可以參考下2023-06-06
在數(shù)據(jù)庫的設計中, 我們通常都是會有很多張表 , 通過表與表之間的關系建立我們想要的數(shù)據(jù)關系, 所以在多張表的前提下, 多表的關聯(lián)查詢就尤為重要2022-04-04
MySQL表的增刪查改(CRUD)是數(shù)據(jù)庫中非?；A的部分,也是后端開發(fā)日常工作中,最重要的一項工作,這篇文章主要介紹了MySQL表的增刪查改操作的相關資料,需要的朋友可以參考下2025-07-07
在SQL中數(shù)據(jù)類型轉換是基礎且關鍵的操作,分為隱式和顯式轉換,隱式轉換由系統(tǒng)自動完成,雖便捷但可能帶來性能損耗、索引失效及數(shù)據(jù)準確性風險,這篇文章主要介紹了數(shù)據(jù)庫類型轉換導致SQL不走索引的案例,需要的朋友可以參考下2026-05-05
Mysql查詢日期timestamp格式的數(shù)據(jù)實現(xiàn)
本文主要介紹了Mysql查詢日期timestamp格式的數(shù)據(jù)實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
這篇文章主要介紹了一文說透什么是MySQL的預編譯，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-11-11
這篇文章主要為大家介紹了MySQL擴展VARCHAR長度遭遇問題匯總分析,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2024-02-02
今天我們就來看一下數(shù)據(jù)庫的各種命令，以下命令全部是從CMD命令窗口下的命令行輸入指令，首先如果如果輸入mysql，系統(tǒng)提示“mysql不是內部命 令或外部命令2016-04-04