MySQLsql_mode修改不⽣效的原因及解决
⽬录
前⾔
场景模拟
总结
前⾔
近期多次聊到sql_mode的话题，也是多次遇到相关问题，今天就趁热打铁，再给⼤家带来⼀个sql_mode的案例分享。
场景模拟
基于业务敏感性的考虑，下⾯涉及的表、存储过程等均⾮真实数据，但并不影响排查过程。
（1）客户侧开发童鞋创建了⼀个存储过程，该存储过程没有严格遵守group by标准语法
session 1：
mysql> delimiter //
mysql> create procedure test_for_group_by()
-> begin
-> select k,pad,count(*) st group by k;
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
（2）客户侧开发童鞋调⽤该存储过程，报错ERROR 1140；因为当时存储过程⽐较复杂，改造起来⽐较⿇烦，所以客户侧选择修改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 'st.k'; this is incompatible with sql_mode=only_full_group_by
（3）客户侧修改完sql_mode，再次执⾏，发现仍然报错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 'st.k'; this is incompatible with sql_mode=only_full_group_by
（4）此时想到，修改系统变量，只对新建连接有效，对已有连接不起作⽤；于是，让客户侧重新建⽴连接，确认系统变量已⽣效，再次调⽤存储过程，但仍然报错ERROR 1140，重复尝试⼏次都是这
个结果
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 'st.k'; this is incompatible with sql_mode=only_full_group_by
（5）进⼀步排查，让客户侧在该会话，执⾏⾮标准的group by语句，发现可以正常执⾏
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）继续排查发现，该存储过程的sql_mode，还是包括ONLY_FULL_GROUP_BY，因此执⾏报错mysql下载不了什么原因
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_CREA +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
（7）这⾥我们也可以知道，系统变量修改只对新建对象有效，对已有对象不⽣效；解决办法很简单，重建该存储过程即可
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(*) st 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-152********-59793845249-15413569710-23749555118 | 1 |
| 495688 | 0951*******-77936258834-40901700703-135********-152******** | 1 |
| 497896 | 131********-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-187******** | 1 |
| 503666 | 40929980986-33813039690-131********-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)
总结
通过这个案例，我们可以知道，修改sql_mode系统变量，只对新建连接和新建对象(主要包括函数和存储过程)有效，对已有连接和已有对象不⽣效。以上就是MySQL sql_mode修改不⽣效的原因及解决的详细内容，更多关于MySQL sql_mode修改不⽣效的资料请关注其它相关⽂章！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论