MySQL sql_mode修改不生效的原因及解决
网络编程 2021-07-05 14:37www.168986.cn编程入门
这篇文章主要介绍了MySQL sql_mode修改不生效的原因及解决,帮助大家更好的理解和学习使用MySQL,感兴趣的朋友可以了解下
前言
近期多次聊到sql_mode的话题,也是多次遇到相关问题,今天就趁热打铁,再给大家带来一个sql_mode的案例分享。
场景模拟
基于业务敏感性的考虑,狼蚁网站SEO优化涉及的表、存储过程等均非真实数据,但并不影响排查过程。
(1)客户侧开发童鞋创建了一个存储过程,该存储过程没有严格遵守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)客户侧开发童鞋调用该存储过程,报错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 'test.test.k'; this is inpatible 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 'test.test.k'; this is inpatible 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 'test.test.k'; this is inpatible 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,执行报错
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)这里我们也可以知道,系统变量修改只对新建对象有效,对已有对象不生效;解决办法很简单,重建该存储过程即可
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)
通过这个案例,我们可以知道,修改sql_mode系统变量,只对新建连接和新建对象(主要包括函数和存储过程)有效,对已有连接和已有对象不生效。
以上就是MySQL sql_mode修改不生效的原因及解决的详细内容,更多关于MySQL sql_mode修改不生效的资料请关注狼蚁SEO其它相关文章!
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南