詳解如何校驗(yàn)MySQL及Oracle時(shí)間字段合規(guī)性
更新時(shí)間：2023年06月27日 10:45:42 作者：愛(ài)可生云數(shù)據(jù)庫(kù)
這篇文章主要為大家介紹了如何校驗(yàn)MySQL及Oracle時(shí)間字段合規(guī)性詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪
背景信息
在數(shù)據(jù)遷移或者數(shù)據(jù)庫(kù)低版本升級(jí)到高版本過(guò)程中，經(jīng)常會(huì)遇到一些由于低版本數(shù)據(jù)庫(kù)參數(shù)設(shè)置過(guò)于寬松，導(dǎo)致插入的時(shí)間數(shù)據(jù)不符合規(guī)范的情況而觸發(fā)報(bào)錯(cuò)，每次報(bào)錯(cuò)再發(fā)現(xiàn)處理起來(lái)較為麻煩，是否有提前發(fā)現(xiàn)這類不規(guī)范數(shù)據(jù)的方法，以下基于 Oracle 和 MySQL 各提供一種可行性方案作為參考。
Oracle 時(shí)間數(shù)據(jù)校驗(yàn)方法
創(chuàng)建測(cè)試表并插?測(cè)試數(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)建對(duì)該表的錯(cuò)誤日志記錄
- Oracle 可以調(diào)用
DBMS_ERRLOG.CREATE_ERROR_LOG包對(duì) SQL 的錯(cuò)誤進(jìn)行記錄，用來(lái)記錄下異常數(shù)據(jù)的情況，十分好用。 參數(shù)含義如下
T1為表名T1_ERROR為對(duì)該表操作的錯(cuò)誤記錄臨時(shí)表DEMO為該表的所屬用戶
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERROR','DEMO');創(chuàng)建并插入數(shù)據(jù)到臨時(shí)表，驗(yàn)證時(shí)間數(shù)據(jù)有效性
-- 創(chuàng)建臨時(shí)表做數(shù)據(jù)校驗(yàn) CREATE TABLE T1_TMP(ID NUMBER,CREATE_DATE DATE); -- 插入數(shù)據(jù)到臨時(shí)表驗(yàn)證時(shí)間數(shù)據(jù)有效性（增加LOG ERRORS將錯(cuò)誤信息輸出到錯(cuò)誤日志表） 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;
校驗(yàn)錯(cuò)誤記錄
SELECT * FROM DEMO.T1_ERROR;
其中 ID 列為該表的主鍵，可用來(lái)快速定位異常數(shù)據(jù)行。
MySQL 數(shù)據(jù)庫(kù)的方法
創(chuàng)建測(cè)試表模擬低版本不規(guī)范數(shù)據(jù)
-- 創(chuàng)建測(cè)試表
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í)間數(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ìn)行數(shù)據(jù)規(guī)范性驗(yàn)證
-- 創(chuàng)建臨時(shí)表，只包含主鍵ID和需要校驗(yàn)的時(shí)間字段
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語(yǔ)法插入數(shù)據(jù)到臨時(shí)CHECK表，忽略插入過(guò)程中的錯(cuò)誤
SQL> INSERT IGNORE INTO T_ORDER_CHECK(ID,ORDER_TIME) SELECT ID,ORDER_TIME FROM T_ORDER;數(shù)據(jù)比對(duì)
將臨時(shí)表與正式表做關(guān)聯(lián)查詢，比對(duì)出不一致的數(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 |
+----+---------------------+---------------------+一個(gè)取巧的小方法
對(duì)時(shí)間字段用正則表達(dá)式匹配，對(duì)有嚴(yán)謹(jǐn)性要求的情況還是得用以上方式，正則匹配燒腦。
-- Oracle 數(shù)據(jù)庫(kù)
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ù)庫(kù)
-- 略，匹配規(guī)則還在調(diào)試中關(guān)于 SQLE
愛(ài)可生開(kāi)源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫(kù)使用者和管理者，支持多場(chǎng)景審核，支持標(biāo)準(zhǔn)化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫(kù)類型可擴(kuò)展的 SQL 審核工具。
SQLE 獲取
| 類型 | 地址 |
|---|---|
| 版本庫(kù) | https://github.com/actiontech/sqle |
| 文檔 | https://actiontech.github.io/sqle-docs/ |
| 發(fā)布信息 | https://github.com/actiontech/sqle/releases |
| 數(shù)據(jù)審核插件開(kāi)發(fā)文檔 | https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit... |
以上就是詳解如何校驗(yàn)MySQL及Oracle時(shí)間字段合規(guī)性的詳細(xì)內(nèi)容，更多關(guān)于MySQL Oracle時(shí)間字段合規(guī)性的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了mysql將多行數(shù)據(jù)合并成一行的方法,需要的朋友可以參考下2014-08-08
詳解MySQL安裝及MySQL8.0新密碼認(rèn)證方式
這篇文章主要介紹了詳解MySQL安裝及MySQL8.0新密碼認(rèn)證方式，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-09-09
這篇文章主要為大家詳細(xì)介紹了Windows下mysql 8.0.11安裝教程 ，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-05-05
show engine innodb status顯示信息不全如何解決
執(zhí)行 show engine innodb status\G 時(shí)，顯示的信息不全，DEADLOCK相關(guān)信息太多，后面的都沒(méi)了2012-11-11
解決mysql ERROR 1017:Can''t find file: ''/xxx.frm'' 錯(cuò)誤
如果重啟服務(wù)器前沒(méi)有關(guān)閉mysql，MySql的MyiSAM表很有可能會(huì)出現(xiàn) ERROR #1017 :Can't find file: '/xxx.frm' 的錯(cuò)誤2011-08-08
詳解MySQL如何實(shí)現(xiàn)數(shù)據(jù)批量更新
最近需要批量更新大量數(shù)據(jù),習(xí)慣了寫(xiě)sql,所以還是用sql來(lái)實(shí)現(xiàn),下面這篇文章主要給大家總結(jié)介紹了關(guān)于MySQL批量更新的方式,需要的朋友可以參考下2023-10-10
Windows系統(tǒng)下MySQL8.0.21安裝教程(圖文詳解)
這篇文章主要介紹了Windows系統(tǒng)下MySQL8.0.21安裝教程,本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-08-08
這篇文章主要為大家詳細(xì)介紹了MySQL之MyISAM存儲(chǔ)引擎的非聚簇索引，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來(lái)幫助2022-03-03
使MySQL查詢區(qū)分大小寫(xiě)的實(shí)現(xiàn)方法
我們?cè)贛ySQL中使用SELECT語(yǔ)句查詢時(shí)，可不可以使查詢區(qū)分大小寫(xiě)？今天從網(wǎng)絡(luò)上找到了方法，現(xiàn)總結(jié)如下。2010-12-12
如果直接用綠色版的mysql，則下載后解壓，只需對(duì)目錄下的my.ini文件的basedir（mysql的基本目錄）和datadir（mysql數(shù)據(jù)目錄）指定就可以，如下所示。2013-06-06