MySQL sql_mode修改不生效的原因及解決

更新時間：2021年05月07日 17:01:42 作者：王文安

這篇文章主要介紹了MySQL sql_mode修改不生效的原因及解決，幫助大家更好的理解和學習使用MySQL，感興趣的朋友可以了解下

前言

近期多次聊到sql_mode的話題，也是多次遇到相關問題，今天就趁熱打鐵，再給大家?guī)硪粋€sql_mode的案例分享。

場景模擬

基于業(yè)務敏感性的考慮，下面涉及的表、存儲過程等均非真實數(shù)據(jù)，但并不影響排查過程。

（1）客戶側(cè)開發(fā)童鞋創(chuàng)建了一個存儲過程，該存儲過程沒有嚴格遵守group by標準語法

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è)開發(fā)童鞋調(diào)用該存儲過程，報錯ERROR 1140；因為當時存儲過程比較復雜，改造起來比較麻煩，所以客戶側(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)仍然報錯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）此時想到，修改系統(tǒng)變量，只對新建連接有效，對已有連接不起作用；于是，讓客戶側(cè)重新建立連接，確認系統(tǒng)變量已生效，再次調(diào)用存儲過程，但仍然報錯ERROR 1140，重復嘗試幾次都是這個結(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）進一步排查，讓客戶側(cè)在該會話，執(zhí)行非標準的group by語句，發(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)，該存儲過程的sql_mode，還是包括ONLY_FULL_GROUP_BY，因此執(zhí)行報錯

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)變量修改只對新建對象有效，對已有對象不生效；解決辦法很簡單，重建該存儲過程即可

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)