快乐学习
前程无忧、中华英才非你莫属!

Day2-解决MySQL_ERROR参考指南(SELECT返回错误结果)

一、SELECT返回错误结果
 原因:1、你的select有误。2、数据库中的数据和你想象的不同。
 解决方案1、如果一个SELECT查询没有按照预期工作,可以将其拆分成小段语句,然后分析每一部分直到你找到错误行为的原因。
                 
 为什么没有报错,并且返回了错误的结果?  
SELECT count(*) FROM items WHERE items.id IN (SELECT id  FROM items_links)
解答:因为items表有一个名为id的列,mysql认为我们想要执行一个“依赖子查询”,结果实际上是从items_links表中查询了 ‘items.id.
通过命令: >EXPLAIN EXTENDED   SELECT count(*) FROM items WHERE items.id IN (SELECT id  FROM items_links)G
 
 获得select type  = DEPENDENT SUBQUERY,就要打起精神了!
  DEPENDENT SUBQUERY意思是:子查询中的第一个SELECT, 取决于外面的查询。
    >SHOW  warningsG 
                                
        
   2、通过表名.列名的格式指定完整的列名,一开始就避免mysql隐藏的错误,让数据库直接吐出error信息。
    严谨写法
    SELECT count(*) FROM items WHERE items.id IN (SELECT items_links.id  FROM items_links)
      ERROR 1054:Unknown column ‘items_links.id‘ in ‘field list’
         

        
这是用户反馈的另一个非常常见的问题,主要的现象有:用户看不到更新的结果、展示的顺序错误或者查询到了非预期的结果。
这个问题主要有两方面的原因:一方面是你的SELECT查询有误;另一方面是数据库中的数据和你想象的不同。我先介绍第一种情况。
在我规划本节示例的时候,我考虑要么使用真实的示例,要么使用我自己设计的小场景。真实的示例可能占用大量篇幅,但是我自己设计的示例可能对你没有什么帮助,因为没有人会写出那样的代码。因此,我选择使用典型的真实示例作为示例,只是大幅简化了它们。
第一个示例是用户在大量使用join时的常见错误。我们将使用之前介绍的例1-1中的表。这张表包含了MySQL中会引起一些常见使用错误的特性,这些特性是我在MySQL支持团队中收集的。每个错误在items表中都有一行记录。我还有一张关联资源信息的links表。因为条目和关联信息之间是多对多的关系,所以我通过items_links关联表把它们联系起来。下面是items表和items_links表的定义(在这个示例中不需要links表):
我编写的第一条查询正常运行,并且似乎返回了合理的结果:
……直到我把返回的数值与关联总数进行比较的时候,我才发现:
查询到的关联信息数比关联表的记录还多,这怎么可能?
我们再来检查一下我特意编写的这个查询。它很简单,仅仅包含两部分,一个子查询:
和一个外部查询:
子查询是开始错误排查的好切入点,因为它可以独立运行。因此,我们可以预期一个完整的结果集:
令人惊讶的是,我们居然有一个输入错误,事实上items_links表中并没有id字段,而是iid字段(代表items的ID)。如果我们重写该条查询,让它使用正确的标识符,它便可正常运行:
我们刚刚学习了一个新的调试技巧。如果一个SELECT查询没有按预期工作,可以将其拆分成小段语句,然后分析每一部分直到你找到产生错误行为的原因。
提示
如果你通过表名.列名的格式指定完整的列名,那么你可以从一开始就避免这个错误,因为你会立即获得错误:
MySQL命令行客户端是一个非常好的测试工具,该工具包含在MySQL的安装包中。第6章将讨论这个重要的工具。
然而,为什么MySQL在执行原始查询语句的时候没有返回同样的错误呢?这是因为在items表有一个名为id的列,因此MySQL认为我们想要执行一个依赖子查询,结果实际上从items_links表中查询了items.id。“依赖子查询”是指引用外部查询中字段的查询。
我们也可以借助EXPLAIN EXTENDS命令,通过SHOW WARNINGS来查找这个错误。如果我们用该命令运行原始查询,会得到:
EXPLAIN EXTENDED输出的2.row表明该子查询实际上是依赖的:select_type是DEPENDENT SUBQUERY。
在结束这个示例之前,我想再介绍一个可以在请求语句涉及很多表的时候,帮助你避免迷茫的小技巧。要知道当你面对10个甚至更多表的连接时,即使你很了解它们应该怎么连接,你也会感到迷茫。
上面示例中一个值得注意的地方是SHOW WARNINGS的输出信息。MySQL服务器不是总按照语句输入的顺序执行它,而是调用优化器去构造一个更好的执行计划,因此用户通常都会很快得到返回结果。在EXPLAIN EXTENDED之后,SHOW WARNINGS命令展示的就是优化后的查询。
在该示例中,SHOW WARNINGS的输出包含两个主要信息。第一个是:
这条信息明确指出服务器是通过items表而不是items_links表解析id的值。
第二条信息包含了优化过的语句:
这个输出信息也指出服务器是从items表接受id的值。
现在我们来对比一下正确的查询和之前列出的错误查询的EXPLAIN EXTENDED的结果:
这次优化过的语句看起来完全不同了,并且确实像我们预期的那样比较items.id和items_links.iid的值。
我们刚刚学习了另一教训:在EXPLAIN EXTENDED命令之后使用SHOW WARNINGS命令查看查询是如何优化(与执行)的。
在正确的查询中,select_type的值仍然是DEPENDENT SUBQUERY。我们已经通过items_links表来解析字段的名称了,为什么结果仍是那样?答案从SHOW WARNINGS中下面这部分输出开始:
子查询仍然显示是依赖的,因为外部查询子句中的id需要子查询去检查与内部查询对应的每行里的iid值。这个问题在MySQL社区bug数据库的12106号报告的讨论中提出。
这个bug报告给我们了另一个重要的教训:如果你怀疑你的查询的执行行为,可以通过有效的资源去获取信息。社区bug数据库就是这样的一种资源。
SELECT查询运行异常可能有很多不同的原因,但是查找问题的一般方法总是相同的。
将查询分解成小段,然后依次执行它们直到你发现问题的原因。
使用EXPLAIN EXTENDED,然后使用SHOW WARNINGS命令去获得查询执行计刬及其实际运行方式的相关信息。
如果你不理解MySQL服务器的执行状况,可以使用互联网和其他有效的资源去获得信息。附录提供了非常有用的资源列表。
              

 
打赏

未经允许不得转载:同乐学堂 » Day2-解决MySQL_ERROR参考指南(SELECT返回错误结果)

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!