MySQL的sql_mode模式說明及設置

来源:https://www.cnblogs.com/wenxin1120/archive/2019/07/23/11234990.html
-Advertisement-
Play Games

MySQL的sql_mode合理設置 sql_mode是個很容易被忽視的變數,預設值是空值,在這種設置下是可以允許一些非法操作的,比如允許一些非法數據的插入。在生產環境必須將這個值設置為嚴格模式,所以開發、測試環境的資料庫也必須要設置,這樣在開發測試階段就可以發現問題. sql model 常用來解 ...


MySQL的sql_mode合理設置

    sql_mode是個很容易被忽視的變數,預設值是空值,在這種設置下是可以允許一些非法操作的,比如允許一些非法數據的插入。在生產環境必須將這個值設置為嚴格模式,所以開發、測試環境的資料庫也必須要設置,這樣在開發測試階段就可以發現問題.

    

sql model 常用來解決下麵幾類問題

  (1) 通過設置sql mode, 可以完成不同嚴格程度的數據校驗,有效地保障數據準備性。

  (2) 通過設置sql model 為寬鬆模式,來保證大多數sql符合標準的sql語法,這樣應用在不同資料庫之間進行遷移時,則不需要對業務sql 進行較大的修改。

  (3) 在不同資料庫之間進行數據遷移之前,通過設置SQL Mode 可以使MySQL 上的數據更方便地遷移到目標資料庫中。

    

sql_mode常用值如下: 

    ONLY_FULL_GROUP_BY:

    對於GROUP BY聚合操作,如果在SELECT中的列,沒有在GROUP BY中出現,那麼這個SQL是不合法的,因為列不在GROUP BY從句中

 

    NO_AUTO_VALUE_ON_ZERO:

    該值影響自增長列的插入。預設設置下,插入0或NULL代表生成下一個自增長值。如果用戶 希望插入的值為0,而該列又是自增長的,那麼這個選項就有用了。

 

    STRICT_TRANS_TABLES:

    在該模式下,如果一個值不能插入到一個事務表中,則中斷當前的操作,對非事務表不做限制

    NO_ZERO_IN_DATE:

    在嚴格模式下,不允許日期和月份為零

 

    NO_ZERO_DATE:

    設置該值,mysql資料庫不允許插入零日期,插入零日期會拋出錯誤而不是警告。

 

    ERROR_FOR_DIVISION_BY_ZERO:

    在INSERT或UPDATE過程中,如果數據被零除,則產生錯誤而非警告。如 果未給出該模式,那麼數據被零除時MySQL返回NULL

 

    NO_AUTO_CREATE_USER:

    禁止GRANT創建密碼為空的用戶

 

    NO_ENGINE_SUBSTITUTION:

    如果需要的存儲引擎被禁用或未編譯,那麼拋出錯誤。不設置此值時,用預設的存儲引擎替代,並拋出一個異常

 

    PIPES_AS_CONCAT:

    將"||"視為字元串的連接操作符而非或運算符,這和Oracle資料庫是一樣的,也和字元串的拼接函數Concat相類似

 

    ANSI_QUOTES:

    啟用ANSI_QUOTES後,不能用雙引號來引用字元串,因為它被解釋為識別符

 

    ORACLE的sql_mode設置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

     

    如果使用mysql,為了繼續保留大家使用oracle的習慣,可以對mysql的sql_mode設置如下:

     

    在my.cnf添加如下配置

    [mysqld]

    sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

    ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

      

      

註意:MySQL5.6和MySQL5.7預設的sql_mode模式參數是不一樣的,5.6的mode是NO_ENGINE_SUBSTITUTION,其實表示的是一個空值,相當於沒有什麼模式設置,可以理解為寬鬆模式。5.7的mode是STRICT_TRANS_TABLES,也就是嚴格模式。

 

    如果設置的是寬鬆模式,那麼我們在插入數據的時候,即便是給了一個錯誤的數據,也可能會被接受,並且不報錯,例如:我在創建一個表時,該表中有一個欄位為name,給name設置的欄位類型時char(10),如果我在插入數據的時候,其中name這個欄位對應的有一條數據的長度超過了10,例如'1234567890abc',超過了設定的欄位長度10,那麼不會報錯,並且取前十個字元存上,也就是說你這個數據被存為了'1234567890',而'abc'就沒有了,但是我們知道,我們給的這條數據是錯誤的,因為超過了欄位長度,但是並沒有報錯,並且mysql自行處理並接受了,這就是寬鬆模式的效果,其實在開發、測試、生產等環境中,我們應該採用的是嚴格模式,出現這種錯誤,應該報錯才對,所以MySQL5.7版本就將sql_mode預設值改為了嚴格模式,並且我們即便是用的MySQL5.6,也應該自行將其改為嚴格模式,而你記著,MySQL等等的這些資料庫,都是想把關於數據的所有操作都自己包攬下來,包括數據的校驗,其實好多時候,我們應該在自己開發的項目程式級別將這些校驗給做了,雖然寫項目的時候麻煩了一些步驟,但是這樣做之後,我們在進行資料庫遷移或者在項目的遷移時,就會方便很多,這個看你們自行來衡量。mysql除了數據校驗之外,你慢慢的學習過程中會發現,它能夠做的事情還有很多很多,將你程式中做的好多事情都包攬了。

 

改為嚴格模式後可能會存在的問題:

    若設置模式中包含了NO_ZERO_DATE,那麼MySQL資料庫不允許插入零日期,插入零日期會拋出錯誤而不是警告。例如表中含欄位TIMESTAMP列(如果未聲明為NULL或顯示DEFAULT子句)將自動分配DEFAULT '0000-00-00 00:00:00'(零時間戳),也或者是本測試的表day列預設允許插入零日期 '0000-00-00' COMMENT '日期';這些顯然是不滿足sql_mode中的NO_ZERO_DATE而報錯。

 

模式設置和修改(以解決上述問題為例):

    方式一:先執行select @@sql_mode,複製查詢出來的值並將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除,然後執行set sql_mode = '修改後的值'或者set session sql_mode='修改後的值';,例如:set session sql_mode='STRICT_TRANS_TABLES';改為嚴格模式

        此方法只在當前會話中生效,關閉當前會話就不生效了。

    方式二:先執行select @@global.sql_mode,複製查詢出來的值並將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除,然後執行set global sql_mode = '修改後的值'。

        此方法在當前服務中生效,重新MySQL服務後失效

 

    方法三:在mysql的安裝目錄下,或my.cnf文件(windows系統是my.ini文件),新增 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,

        添加my.cnf如下:

        [mysqld]

        sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

 

        然後重啟mysql。

    此方法永久生效.當然生產環境上是禁止重啟MySQL服務的,所以採用方式二加方式三來解決線上的問題,那麼即便是有一天真的重啟了MySQL服務,也會永久生效了。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 早前已發現有台機一直在報這麼個錯誤, 一用yum update 就報一堆: Error: unpacking rpm package ..... error: xxxx : install failed error : unpacking of archive failed on file /usr ...
  • grep:文本過濾工具(找到要找的文本) print lines matching a pattern例: 列印 行 匹配 一個 模式/樣式[root@oldboyedu /opt]# grep "oldboy" oldboy.txt Welcome to oldboy training.grep ...
  • 前言:方法為上傳本地jdk壓縮包文件到遠程linux系統 <<< 驗證系統位數 >>> 在安裝之前,先驗證Linux系統的位數。 運行結果如下,結果顯示當前系統為64位 <<< 官網下載jdk壓縮包 >>> 選擇箭頭指向的壓縮包 <<< 上傳壓縮包 >>> 首先通過在xshell上傳壓縮包 效果圖如 ...
  • 如何選擇適合自己的Linux版本: 1.Linux桌面系統,首選Ubuntu; 2.伺服器端的Linux系統,首選RHEL或CentOS,這兩種中首選CentOS,如果公司有錢,不在乎成本也可以選擇RHEL; 3.如果對安全要求很高,可選擇Debian或FreeBSD; 4.如果需要使用資料庫高級服 ...
  • [toc] 進程的優先順序 那麼在系統中如何給進程配置優先順序 在啟動進程時,為不同的進程使用不同的調度策略。 nice值越高:表示優先順序越低,例如19,該進程容易將CPU使用量讓給其他進程。 nice值越低:表示優先順序越高,例如 20,該進程更不傾向於讓出CPU。 遠程連接不上操作步驟 網路 埠 用 ...
  • 轉自:https://blog.csdn.net/maxsky/article/details/44905003 大家都知道在 Linux 下,執行 su 命令後輸入密碼即可切換到 root 用戶執行各類操作 但是 Mac 下,這樣行不通,只會返回你一句 Sorry! 解決方法很簡單,首先打開終端 ...
  • 1.查找/var目錄下不屬於root、lp、gdm的所有文件。find /var -not \ (-user root -o -user lp -user gdm) -ls 2.統計/etc/init.d/functions文件中的每個單詞出現次數,併排序,用grep和sed兩種方法實現。 sed方 ...
  • #!/bin/bash #History: #2019/07/23 Fsq #This Program will check Permissions on dir PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...