MySQL怪现象:select `*` from table 竟不报错的背后

MySQL中`select `*` from table`在特定版本下竟不报错?本文带你探寻这一怪异现象背后的原因,并get快速排查问题的方法。

原文标题:select `*` from table 语法居然不报错?

原文作者:阿里云开发者

冷月清谈:

本文通过一个实际案例,探讨了MySQL中`select `*` from table`语句在特定版本下不报错的异常行为。文章首先展示了该语句在某些MySQL版本中能够成功执行,但在其他版本中则会报错。通过一系列的测试,确认该问题与sql_mode和库表字符集无关。随后,采用二分法定位问题,发现MySQL官方在5.7.31版本中修复了此问题。文章深入分析了MySQL 5.7.31的Release Notes,找到了对应的Bug #30528450,并通过Github上的patch进一步了解了修复方案。文章还简要介绍了问题产生的根源,即MySQL对select语句解析的重构导致对`*`的处理异常。最后,文章强调了问题分析方法的重要性,特别是二分法在定位问题关键节点上的作用。

怜星夜思:

1、文章中提到MySQL 5.7.31修复了`select `*``的问题,那么在你的实际工作中,有没有遇到过类似MySQL版本差异导致的行为不一致的情况?你是如何解决的?
2、文章中使用了二分法来定位问题版本,你觉得在排查数据库问题时,还有哪些高效的排查方法?
3、文章提到了`dbdeployer`工具可以快速构建不同版本的数据库,你有没有使用过类似的工具?或者你有什么其他推荐的快速搭建数据库环境的方法?

原文内容

阿里妹导读


从数据库基础的对照测试实验入手,一点点分析数据库的异常行为,降低DBA/运维对于bug分析的难度,也不需要一些诸如strace/jstack/gdb等分析工具,好的技术需要用好方法,能力有限,欢迎指点。

一、前言

在MySQL中,反引号 ` 实际上是用来引用数据库对象的名称,如表名、列名等,以区分MySQL的保留关键字和特殊字符。而星号 * 用作通配符,它不需要用反引号引用。下面语句的反引号 ` 通常用于引用标识符,不适用于表中所有列输出,在标准的MySQL中,这实际上会产生一个错误,因为没有列名为 * 的列。

select `*` from table;

但是在客户的数据库中,该SQL却执行成功了,但是我在另外一台测试数据库中,却是报错,理论上报错是符合预期的,那客户的数据库是怎么回事呢?

这里不建议大家关注业务影响,这个问题可大可小,主要是兴趣所致分析该问题的原因:

  • 如果只是客户业务不经常调用,且业务代码可以随意调整,让客户优化掉这个问题反引号的问题,正确使用即可;

  • 可如果客户业务代码写死,客户将同样的数据已经迁移到另外一套数据库中,业务频繁使用 select `*` ,频繁报错,那么这个问题就很严重了,生产业务中就是一堆报错。



二、测试验证

本着深入探究问题的动机,作为小白,我们应该做大量单变量测试,不断向问题原因靠拢,最终定位。

  • 经过一系列的排查测试,对齐相关因素后,当前问题与sql_mode、库表字符集均无关。
  • ANSI_QUOTES 限制的是双引号的使用,与反引号的使用无关。
  • 在网上 Google 也不容易搜到类似的问题和报错信息,问题一度陷入困境。

本地尝试相同版本的数据库,发现问题复现了。但是在高版本的数据库中就没有出现,从而推断,这个问题在高版本数据库已经解决了。然后我们使用二分法,从低/高版本分别测试,验证问题复现版本,寻找交叉点,来判断下这个问题的解决版本。

create table t1(id int,name varchar(255));insert into t1 value(1,'chenyuan'),(2,'liu');

mysql [localhost:5712] {root} (test) > select * from t1;
±-----±---------+
| id   | name     |
±-----±---------+
|    1 | chenyuan |
|    2 | liu      |
±-----±---------+
2 rows in set (0.00 sec)

mysql [localhost:5720] {root} (test) > select * from t1;
±-----±---------+
| id   | name     |
±-----±---------+
|    1 | chenyuan |
|    2 | liu      |
±-----±---------+
2 rows in set (0.00 sec)

mysql [localhost:5725] {root} (test) > select * from t1;
±-----±---------+
| id   | name     |
±-----±---------+
|    1 | chenyuan |
|    2 | liu      |
±-----±---------+
2 rows in set (0.00 sec)

mysql [localhost:5729] {root} (test) > select * from t1;
±-----±---------+
| id   | name     |
±-----±---------+
|    1 | chenyuan |
|    2 | liu      |
±-----±---------+
2 rows in set (0.00 sec)

mysql [localhost:5730] {root} (test) > select * from t1;
±-----±---------+
| id   | name     |
±-----±---------+
|    1 | chenyuan |
|    2 | liu      |
±-----±---------+
2 rows in set (0.00 sec)

mysql [localhost:5731] {root} (test) > select * from t1;
ERROR 1054 (42S22): Unknown column ‘*’ in ‘field list’

mysql [localhost:5732] {root} (test) > select * from t1;
ERROR 1054 (42S22): Unknown column ‘*’ in ‘field list’

mysql [localhost:5733] {root} (test) > select * from t1;
ERROR 1054 (42S22): Unknown column ‘*’ in ‘field list’

mysql [localhost:5741] {root} (test) >  select * from t1;
ERROR 1054 (42S22): Unknown column ‘*’ in ‘field list’

三、结论

从上面的测试可以得出,当前这个缺陷,MySQL官方应该在5.7.31的发布中修复了,那么究竟触发了什么bug呢?

MySQL 5.7.31 Release Notes:https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html

我们可以看下5731做了哪些变更。当前这个问题还是很有区分度的,直接就扫到了原因和Bug号。

It is possible to define a column named * (single asterisk character), but SELECT `*` was treated identically to SELECT *, making it impossible to select only this column in a query; in other words, the asterisk character was expanded to a list of all table columns even when it was surrounded by backticks. (Bug #30528450)

  • 进一步在Github上找到对应的patch
  • 5.7的patch【修复版本5731】:https://github.com/mysql/mysql-server/commit/5c8c085ba96d30d697d0baa54d67b102c232116b
  • 8.0的patch【修复版本8021】:https://github.com/mysql/mysql-server/commit/bdc8816873ff891b17d1ed6c5266f0ecfc2fa1f5

就当前的问题而言,我们命中了5.7 patch中的Issue 1。





简单总结:在MySQL的 WL#7200WorkLog中,官方对select语句的解析进行了重构,导致对 `*` 的处理发生了异常,无效的*会被Item_field::itemize()类处理,而该方法没有处理 `*` 的逻辑,致使反引号没有被正确应用。





5731之后的优化:删除 Item_field::itemize() 类中检查 select结构体中 field_name 为 * 的处理方法,新增一个类Item_asterisk用于处理SQL查询中的通配符 *。它在构造时记录通配符的使用,并在itemize成员函数中进行进一步的处理。

四、写在最后


这篇文章并不是对标数据库的研发同学,而是针对大多数不懂C++代码的售后/DBA同学,说的是一种问题分析方法,我们遇到类似的bug行为应该如何判断?二分法判断问题的关键节点,从而能更进一步的确认到问题原因。


特别感谢前老板三年的栽培,让我从数据库慢慢入门,无数加班的夜晚,讨论并指导了我很多技术问题,慢慢淬炼,以后的路再接再厉。

彩蛋:快速构建不同版本数据库,大家可以考虑使用 dbdeployer 工具。


楼上说的很有道理,关注release notes确实很重要。我之前经历过一个更坑的,是关于group by的行为变化。MySQL 5.7以后默认开启了ONLY_FULL_GROUP_BY模式,导致一些原本在低版本上可以正常运行的SQL在高版本上报错。解决方法要么修改sql_mode,要么优化SQL语句,确保group by的字段包含所有非聚合字段。总之,升级前一定要做好充分的测试,避免踩坑。

同意楼上,对比法确实很实用。我补充一个,遇到复杂的性能问题,可以使用performance schema来做深入分析。它可以提供非常详细的数据库内部运行状态信息,帮助我们找到问题的根源。当然,前提是要熟悉performance schema的各种表和指标。

我用过Docker,真的是神器!几分钟就能拉起一个指定版本的MySQL容器,而且环境完全隔离,互不影响。配合Docker Compose,可以快速搭建复杂的数据库集群环境,非常方便。

除了Docker,Vagrant + VirtualBox 也是一个不错的选择。Vagrant可以自动化创建和配置虚拟机,搭配Provisioning工具如Ansible,可以快速搭建各种数据库环境。而且Vagrantfile可以版本控制,方便团队协作。

我之前遇到过一个线上问题,一个复杂的存储过程在高版本MySQL上运行速度明显变慢,但低版本却正常。后来发现是高版本优化器对某些函数的处理方式改变了,导致执行计划偏差。解决方案是针对高版本MySQL,重写了存储过程中相关的函数调用,并强制指定了索引,最终解决了性能问题。版本差异真是个大坑!

确实,MySQL版本差异有时候挺让人头疼的。我这边有个案例,不同版本MySQL对于JSON字段的处理方式不一样,导致同样的数据在不同版本导出来后,再导入回去,部分数据会丢失。最后还是通过升级到最新版本,并修改了数据处理逻辑来解决的。所以说,及时关注官方release notes很重要啊,能避免很多坑。

我觉得还可以利用一些专业的数据库诊断工具。这些工具通常集成了多种诊断方法,可以自动分析数据库的健康状况,并给出优化建议。比如阿里云的DMS,或者一些开源的数据库监控工具,都能帮DBA省不少事儿。

除了二分法,我觉得对比法也很有效。比如对比出问题的数据库和正常数据库的配置、SQL_MODE、版本信息等等,逐一排除差异,缩小问题范围。另外,慢查询日志分析也是必不可少的,可以帮助我们快速定位到性能瓶颈。

如果只是为了测试简单的SQL语句,其实在线的SQL fiddle或者菜鸟工具也够用了。虽然功能有限,但胜在方便快捷,不用安装任何东西。当然,对于复杂的场景,还是得用Docker或者虚拟机。