MySQL sql_mode修改不生效的原因及解決
更新時(shí)間：2021年05月07日 17:01:42 作者：王文安
這篇文章主要介紹了MySQL sql_mode修改不生效的原因及解決，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下
前言
近期多次聊到sql_mode的話題，也是多次遇到相關(guān)問(wèn)題，今天就趁熱打鐵，再給大家?guī)?lái)一個(gè)sql_mode的案例分享。
場(chǎng)景模擬
基于業(yè)務(wù)敏感性的考慮，下面涉及的表、存儲(chǔ)過(guò)程等均非真實(shí)數(shù)據(jù)，但并不影響排查過(guò)程。
（1）客戶側(cè)開(kāi)發(fā)童鞋創(chuàng)建了一個(gè)存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程沒(méi)有嚴(yán)格遵守group by標(biāo)準(zhǔn)語(yǔ)法
session 1：
mysql> delimiter //
mysql> create procedure test_for_group_by()
-> begin
-> select k,pad,count(*) from test.test group by k;
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
（2）客戶側(cè)開(kāi)發(fā)童鞋調(diào)用該存儲(chǔ)過(guò)程，報(bào)錯(cuò)ERROR 1140；因?yàn)楫?dāng)時(shí)存儲(chǔ)過(guò)程比較復(fù)雜，改造起來(lái)比較麻煩，所以客戶側(cè)選擇修改sql_mode
session 1： mysql> call test_for_group_by(); ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by
（3）客戶側(cè)修改完sql_mode，再次執(zhí)行，發(fā)現(xiàn)仍然報(bào)錯(cuò)ERROR 1140
session 2： mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) session 1： mysql> call test_for_group_by(); ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by
（4）此時(shí)想到，修改系統(tǒng)變量，只對(duì)新建連接有效，對(duì)已有連接不起作用；于是，讓客戶側(cè)重新建立連接，確認(rèn)系統(tǒng)變量已生效，再次調(diào)用存儲(chǔ)過(guò)程，但仍然報(bào)錯(cuò)ERROR 1140，重復(fù)嘗試幾次都是這個(gè)結(jié)果
session 3： mysql> show variables like 'sql_mode'; +---------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> call test_for_group_by(); ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by
（5）進(jìn)一步排查，讓客戶側(cè)在該會(huì)話，執(zhí)行非標(biāo)準(zhǔn)的group by語(yǔ)句，發(fā)現(xiàn)可以正常執(zhí)行
session 3： mysql> select user,host,count(*) From mysql.user group by user; +---------------+-----------+----------+ | user | host | count(*) | +---------------+-----------+----------+ | mysql.session | localhost | 1 | | mysql.sys | localhost | 1 | | root | localhost | 1 | | rpl_user | % | 1 | | test | % | 1 | +---------------+-----------+----------+ 5 rows in set (0.00 sec)
（6）繼續(xù)排查發(fā)現(xiàn)，該存儲(chǔ)過(guò)程的sql_mode，還是包括ONLY_FULL_GROUP_BY，因此執(zhí)行報(bào)錯(cuò)
session 2： mysql> select routine_catalog,routine_schema,routine_name,routine_type,created,last_altered,sql_mode from routines where routine_name='test_for_group_by'; +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | routine_catalog | routine_schema | routine_name | routine_type | created | last_altered | sql_mode | +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | def | test | test_for_group_by | PROCEDURE | 2020-12-24 12:12:10 | 2020-12-24 12:12:10 | 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 | +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
（7）這里我們也可以知道，系統(tǒng)變量修改只對(duì)新建對(duì)象有效，對(duì)已有對(duì)象不生效；解決辦法很簡(jiǎn)單，重建該存儲(chǔ)過(guò)程即可
session 3：
mysql> drop procedure test_for_group_by;
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter //
mysql> create procedure test_for_group_by()
-> begin
-> select k,pad,count(*) from test.test group by k;
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call test_for_group_by();
+--------+-------------------------------------------------------------+----------+
| k | pad | count(*) |
+--------+-------------------------------------------------------------+----------+
| 393975 | 35227182905-15234265621-59793845249-15413569710-23749555118 | 1 |
| 495688 | 09512147864-77936258834-40901700703-13541171421-15205431759 | 1 |
| 497896 | 13152283289-69561545685-52868757241-04245213425-69280254356 | 1 |
| 498573 | 43131080328-59298106536-35954612339-97546855884-75769514803 | 1 |
| 500775 | 27590239742-20204899609-34345212327-79811525340-24267764271 | 1 |
| 501885 | 63188288836-92351140030-06390587585-66802097351-49282961843 | 1 |
| 503330 | 01495266405-82925129145-92643983850-90243995398-18709399387 | 1 |
| 503666 | 40929980986-33813039690-13155419391-97985458477-39771362212 | 1 |
| 504353 | 00505722282-72931248925-57037623248-81117963809-88658076981 | 1 |
| 514246 | 21979564480-87492594656-60524686334-78820761788-57684966682 | 1 |
+--------+-------------------------------------------------------------+----------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
總結(jié)
通過(guò)這個(gè)案例，我們可以知道，修改sql_mode系統(tǒng)變量，只對(duì)新建連接和新建對(duì)象(主要包括函數(shù)和存儲(chǔ)過(guò)程)有效，對(duì)已有連接和已有對(duì)象不生效。
以上就是MySQL sql_mode修改不生效的原因及解決的詳細(xì)內(nèi)容，更多關(guān)于MySQL sql_mode修改不生效的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
在SQL查詢中,經(jīng)常需要按多個(gè)字段對(duì)結(jié)果進(jìn)行排序,本文主要介紹了MySql查詢中按多個(gè)字段排序的方法,具有一定的參考價(jià)值,感興趣的可以了解一下2024-03-03
這篇文章主要介紹了sqlite遷移到mysql腳本的方法,需要的朋友可以參考下2017-08-08
10個(gè)MySQL性能調(diào)優(yōu)的方法
本文介紹了10個(gè)MySQL性能調(diào)優(yōu)的方法，每個(gè)方法的講解都很細(xì)致，非常實(shí)用，,需要的朋友可以參考下2015-07-07
MySQL數(shù)據(jù)庫(kù)存儲(chǔ)引擎的應(yīng)用
存儲(chǔ)引擎是MySQL將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)中的存儲(chǔ)方式,本文主要介紹了MySQL數(shù)據(jù)庫(kù)的存儲(chǔ)引擎的應(yīng)用,具有一定的參考價(jià)值,感興趣的可以了解一下2024-03-03
這篇文章主要介紹了CentOS7離線安裝MySQL的教程，在安裝之前需要我們先刪除原有的mariadb，文中也通過(guò)命令給大家介紹了，需要的朋友跟隨小編也看看吧2019-07-07
MySQL和Redis實(shí)現(xiàn)二級(jí)緩存的方法詳解
這篇文章主要給大家介紹了關(guān)于MySQL和Redis實(shí)現(xiàn)二級(jí)緩存的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
mysql 添加索引 mysql 如何創(chuàng)建索引
本文將介紹mysql 如何創(chuàng)建索引，需要的朋友可以參考下
2012-11-11 
MySql官方手冊(cè)學(xué)習(xí)筆記1 MySql簡(jiǎn)單上手
這是我學(xué)習(xí)MySql 5.1時(shí)做的一些整理與筆記，希望能理一理自己學(xué)到的東西，如果能有助于各位同道學(xué)習(xí)MySql那就更是意外之喜了，呵呵
2012-10-10 
這篇文章主要介紹了mysql索引對(duì)排序的影響,結(jié)合實(shí)例形式分析了mysql使用索引提高排序速度的相關(guān)操作技巧,需要的朋友可以參考下
2019-07-07