主頁 > 知識庫 > 關(guān)于MySQL的sql_mode合理設(shè)置詳解

關(guān)于MySQL的sql_mode合理設(shè)置詳解

熱門標(biāo)簽:400電話可以免費申請嗎 福建外呼系統(tǒng)定制化 悟空科技電話機器人 美國反騷擾電話機器人 真人語音電話機器人 電銷卡外呼系統(tǒng)供應(yīng)商 銅陵防封電銷卡 騰訊地圖標(biāo)注提升 怎么在地圖標(biāo)注位置生成圖片

MySQL的sql_mode合理設(shè)置

sql_mode是個很容易被忽視的變量,默認(rèn)值是空值,在這種設(shè)置下是可以允許一些非法操作的,比如允許一些非法數(shù)據(jù)的插入。在生產(chǎn)環(huán)境必須將這個值設(shè)置為嚴(yán)格模式,所以開發(fā)、測試環(huán)境的數(shù)據(jù)庫也必須要設(shè)置,這樣在開發(fā)測試階段就可以發(fā)現(xiàn)問題.

1.sql model 常用來解決下面幾類問題:

  (1) 通過設(shè)置sql mode, 可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗,有效地保障數(shù)據(jù)準(zhǔn)備性。

  (2) 通過設(shè)置sql model 為寬松模式,來保證大多數(shù)sql符合標(biāo)準(zhǔn)的sql語法,這樣應(yīng)用在不同數(shù)據(jù)庫之間進(jìn)行遷移時,則不需要對業(yè)務(wù)sql 進(jìn)行較大的修改。

  (3) 在不同數(shù)據(jù)庫之間進(jìn)行數(shù)據(jù)遷移之前,通過設(shè)置SQL Mode 可以使MySQL 上的數(shù)據(jù)更方便地遷移到目標(biāo)數(shù)據(jù)庫中。

2.sql_mode常用值如下:

    1. ONLY_FULL_GROUP_BY:

       對于GROUP BY聚合操作,如果在SELECT中的列,沒有在GROUP BY中出現(xiàn),那么這個SQL是不合法的,因為列不在GROUP BY從句中。

   2. NO_AUTO_VALUE_ON_ZERO:

      該值影響自增長列的插入。默認(rèn)設(shè)置下,插入0或NULL代表生成下一個自增長值。如果用戶希望插入的值為0,而該列又是自增長的,那么這個選項就有用了。

   3. STRICT_TRANS_TABLES:嚴(yán)格模式

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

   4. NO_ZERO_IN_DATE:

      在嚴(yán)格模式下,不允許日期和月份為零。
 
   5. NO_ZERO_DATE:

      設(shè)置該值,mysql數(shù)據(jù)庫不允許插入零日期,插入零日期會拋出錯誤而不是警告。
 
   6. ERROR_FOR_DIVISION_BY_ZERO:

      在INSERT或UPDATE過程中,如果數(shù)據(jù)被零除,則產(chǎn)生錯誤而非警告。如果未給出該模式,那么數(shù)據(jù)被零除時MySQL返回NULL
 
   7. NO_AUTO_CREATE_USER:

      禁止GRANT創(chuàng)建密碼為空的用戶

   8. NO_ENGINE_SUBSTITUTION:

      如果需要的存儲引擎被禁用或未編譯,那么拋出錯誤。不設(shè)置此值時,用默認(rèn)的存儲引擎替代,并拋出一個異常

   9. PIPES_AS_CONCAT:

      將"||"視為字符串的連接操作符而非或運算符,這和Oracle數(shù)據(jù)庫是一樣的,也和字符串的拼接函數(shù)Concat相類似

   10. ANSI_QUOTES:

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

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

    如果使用mysql,為了繼續(xù)保留大家使用oracle的習(xí)慣,可以對mysql的sql_mode設(shè)置如下:

    在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' 

3.寬松模式 和 嚴(yán)格模式 的注意事項:

【1】注意:MySQL5.6和MySQL5.7默認(rèn)的sql_mode模式參數(shù)是不一樣的。
    5.6的mode是NO_ENGINE_SUBSTITUTION,其實表示的是一個空值,相當(dāng)于沒有什么模式設(shè)置,可以理解為寬松模式。
    5.7的mode是STRICT_TRANS_TABLES,也就是嚴(yán)格模式。
   
【2】如果設(shè)置的是寬松模式,那么我們在插入數(shù)據(jù)的時候,即便是給了一個錯誤的數(shù)據(jù),也可能會被接受,并且不報錯。
   
    例如:我在創(chuàng)建一個表時,該表中有一個字段為name,給name設(shè)置的字段類型時char(10),如果我在插入數(shù)據(jù)的時候,其中name這個字段對應(yīng)的有一條數(shù)據(jù)的長度超過了10,例如'1234567890abc',超過了設(shè)定的字段長度10,那么不會報錯,并且取前十個字符存上,也就是說你這個數(shù)據(jù)被存為了'1234567890',而'abc'就沒有了,但是我們知道,我們給的這條數(shù)據(jù)是錯誤的,因為超過了字段長度,但是并沒有報錯,并且mysql自行處理并接受了,這就是寬松模式的效果。
   
    其實在開發(fā)、測試、生產(chǎn)等環(huán)境中,我們應(yīng)該采用的是嚴(yán)格模式,出現(xiàn)這種錯誤,應(yīng)該報錯才對,所以MySQL5.7版本就將sql_mode默認(rèn)值改為了嚴(yán)格模式,并且我們即便是用的MySQL5.6,也應(yīng)該自行將其改為嚴(yán)格模式。
   
    另外,MySQL等等的這些數(shù)據(jù)庫,都是想把關(guān)于數(shù)據(jù)的所有操作都自己包攬下來,包括數(shù)據(jù)的校驗,其實好多時候,我們應(yīng)該在自己開發(fā)的項目程序級別將這些校驗給做了,雖然寫項目的時候麻煩了一些步驟,但是這樣做之后,我們在進(jìn)行數(shù)據(jù)庫遷移或者在項目的遷移時,就會方便很多。mysql除了數(shù)據(jù)校驗之外,你慢慢的學(xué)習(xí)過程中會發(fā)現(xiàn),它能夠做的事情還有很多很多,將你程序中做的好多事情都包攬了。
   
【3】改為嚴(yán)格模式后可能會存在的問題:

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

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


【方式一】:

    先執(zhí)行select @@sql_mode,復(fù)制查詢出來的值并將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除,然后執(zhí)行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';
   
    例如:set session sql_mode='STRICT_TRANS_TABLES';改為嚴(yán)格模式
   
    注意:此方法只在當(dāng)前會話中生效,關(guān)閉當(dāng)前會話就不生效了。


【方式二】:
    先執(zhí)行select @@global.sql_mode,復(fù)制查詢出來的值并將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除,然后執(zhí)行set global sql_mode = '修改后的值'。

    注意:此方法在當(dāng)前服務(wù)中生效,重新MySQL服務(wù)后失效
 
【方法三】:
    1.在mysql的安裝目錄下,或my.cnf文件(windows系統(tǒng)是my.ini文件),新增 sql_mode =
    "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    添加my.ini如下:
   
    [mysqld]
   
    sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    2.然后重啟mysql。以管理員身份運行CMD,執(zhí)行 net stop mysql ,再執(zhí)行 net start mysql。

    注意:此方法永久生效.當(dāng)然生產(chǎn)環(huán)境上是禁止重啟MySQL服務(wù)的,所以采用‘方式二+方式三'來解決線上的問題,那么即便是有一天真的重啟了MySQL服務(wù),也會永久生效了。

總結(jié)

以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,謝謝大家對腳本之家的支持。

您可能感興趣的文章:
  • MySQL sql_mode修改不生效的原因及解決
  • 詳解MySQL的sql_mode查詢與設(shè)置
  • MySQL中SQL Mode的查看與設(shè)置詳解
  • mysql中的sql_mode模式實例詳解
  • Django2 連接MySQL及model測試實例分析
  • MySQL關(guān)于sql_mode解析與設(shè)置講解
  • MySQL5.7中的sql_mode默認(rèn)值帶來的坑及解決方法
  • MySql版本問題sql_mode=only_full_group_by的完美解決方案
  • 解決MySQL 5.7.9版本sql_mode=only_full_group_by問題
  • Mysql之SQL Mode用法詳解
  • mysql sql_mode="" 的作用說明
  • MySQL sql_mode的使用詳解

標(biāo)簽:烏海 臨汾 湖北 武威 白銀 云浮 湖南 聊城

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《關(guān)于MySQL的sql_mode合理設(shè)置詳解》,本文關(guān)鍵詞  關(guān)于,MySQL,的,sql,mode,合理,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《關(guān)于MySQL的sql_mode合理設(shè)置詳解》相關(guān)的同類信息!
  • 本頁收集關(guān)于關(guān)于MySQL的sql_mode合理設(shè)置詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章