共計(jì) 3418 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

這篇文章主要介紹了 MySQL 中 sql_mode 合理設(shè)置的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

MySQL 的 sql_mode 合理設(shè)置

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

1.sql model 常用來(lái)解決下面幾類問(wèn)題：

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

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

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

2.sql_mode 常用值如下:

  1. ONLY_FULL_GROUP_BY:

  對(duì)于 GROUP BY 聚合操作, 如果在 SELECT 中的列, 沒(méi)有在 GROUP BY 中出現(xiàn), 那么這個(gè) SQL 是不合法的, 因?yàn)榱胁辉?GROUP BY 從句中。

  2. NO_AUTO_VALUE_ON_ZERO:

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

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

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

  4. NO_ZERO_IN_DATE:

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

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

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

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

  8. NO_ENGINE_SUBSTITUTION:

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

  9. PIPES_AS_CONCAT:

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

  10. ANSI_QUOTES:

  啟用 ANSI_QUOTES 后, 不能用雙引號(hào)來(lái)引用字符串, 因?yàn)樗唤忉尀樽R(shí)別符

  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í)慣, 可以對(duì) 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)格模式 的注意事項(xiàng)：

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

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

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

【方式一】：

  先執(zhí)行 select @@sql_mode, 復(fù)制查詢出來(lái)的值并將其中的 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)前會(huì)話中生效，關(guān)閉當(dāng)前會(huì)話就不生效了。

【方式二】：
  先執(zhí)行 select @@global.sql_mode, 復(fù)制查詢出來(lái)的值并將其中的 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。以管理員身份運(yùn)行 CMD, 執(zhí)行 net stop mysql , 再執(zhí)行 net start mysql。

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

感謝你能夠認(rèn)真閱讀完這篇文章，希望丸趣 TV 小編分享的“MySQL 中 sql_mode 合理設(shè)置的示例分析”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持丸趣 TV，關(guān)注丸趣 TV 行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!