非欧几何的灵感

——限量版Hello World
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

MySQL中10个出人意料的操作结果

Posted on 2010-11-19 23:54  Neon∮Light  阅读(212)  评论(1)  编辑  收藏  举报

#10. 查找一个NULL值

1.SELECT  *
2.FROM    a
3.WHERE   a.column = NULL

在SQL中，NULL不等于任何其他值，也不等于另外一个NULL。上面的查询不会返回任何值，而且事实上优化器会把这个查询删除掉。

如果想要查询NULL值，用下面的查询语句代替:

1.SELECT  *
2.FROM    a
3.WHERE   a.column IS NULL


#9. 附加条件的左连接

1.SELECT  *
2.FROM    a
3.LEFT JOIN
4.        b
5.ON      b.a = a.id
6.WHERE   b.column = 'something'

左连接很像内连接，但是会返回a中每条记录至少一次，如果没有找到匹配项，会将b中缺失的字段用NULL替代。

但是，WHERE条件会在左连接后执行，所以上面的查询语句在做了链接之后才检查列。根据我们刚才知道的，NULL值是不能满足“=”条件的，所以a中有而b中没有的记录将会不可避免的被过滤掉。

事实上，这个查询除了效率不够之外，完全就是个内连接。

为了只查找b.column='something'的记录（还是返回a中的所有记录），这个条件应该写成带ON的子句。

SELECT  *
 FROM    a
 LEFT JOIN
         b
 ON      b.a = a.id
         AND b.column = 'something'


#8. 小于一个值但不是NULL

我经常看到这样的查询语句：

1.SELECT  *
2.FROM    b
3.WHERE   b.column < 'something'
4.        AND b.column IS NOT NULL

这其实不是一个错误：这个查询语句正确并且会返回期望的结果。但是，这儿的IS NOT NULL其实不需要。

如果b.column是一个NULL值，则b.column< 'something' 就不会被满足，因为任何与NULL的比较都会被评估成一个boolean类型的NULL，故不会被过滤条件通过。

很有意思的是，这个多余的NULL检查条件在大于的查询语句中从不会出现（就像b.column > 'something'）。

这是因为在MySQL的ORDER BY条件中，NULL会被排在第一个所以一些人错误的认为NULL比其他任何值都少。

这个查询其实可以简化成:

1.SELECT  *
2.FROM    b
3.WHERE   b.column < 'something'
而且不会在b.column中返回NULL。

#7. 与NULL值做连接

1.SELECT  *
2.FROM    a
3.JOIN    b
4.ON      a.column = b.column

当在两个表的列中都运行NULL值时，这个查询不会返回当两个列都为NULL的情况，原因就是NULL和NULL不相等。

下面是正确的查询语句:

1.SELECT  *
2.FROM    a
3.JOIN    b
4.ON      a.column = b.column
5.        OR (a.column IS NULL AND b.column IS NULL)

MySQL的优化器将这视为equijoin并且提供一个特殊的连接条件，ref_or_null。

#6. NOT IN条件中的NULL值 

1.SELECT  a.*
2.FROM    a
3.WHERE   a.column NOT IN
4.        (
5.        SELECT column
6.        FROM    b
7.        )

如果b中只有一个NULL值，这个查询不会返回任何值。像在其他断言一样，IN和NOT IN断言中的NULL值会使得断言赋值为NULL。

这个语句应该用NOT EXISTS改写成:

1.SELECT  a.*
2.FROM    a
3.WHERE   NOT EXISTS
4.        (
5.        SELECT NULL
6.        FROM    b
7.        WHERE   b.column = a.column
8.        )

不像IN关键字， EXISTS 会用true和false来赋值。

#5. 随机排序样本

1.SELECT  *
2.FROM    a
3.ORDER BY
4.        RAND(), column
5.LIMIT 10

这个查询尝试在一个排序列中随机选出10个记录。

ORDER BY将输出结果顺序向量排列：即，如果第一个表达式相同，则以第二表达式的值排序。

但是，RAND（）的结果是随机的，所以RAND（）的结果值匹配是不可行的。所以在RAND（）操作后做排序是无用的。

为了排序随机抽样记录，用这个查询语句:

01.SELECT  *
02.FROM    (
03.        SELECT  *
04.        FROM    mytable
05.        ORDER BY
06.                RAND()
07.        LIMIT 10
08.        ) q
09.ORDER BY
10.        column

#4. 从一个分组中抽样任意一条记录

这个查询语句试图选择每个分组中的一列（grouper定义）

1.SELECT  DISTINCT(grouper), a.*
2.FROM    a

DISTINCT不是一个函数，他是SELECT子句的一部分。它作用于SELECT语句中的所有列，而且这里的括弧可以省略。这个查询会选择grouper的副本（如果至少在其他一列中有不同值）。

有时，用这个查询行得通（这个查询依赖于MySQL对GROUP BY的扩展）：

1.SELECT  a.*
2.FROM    a
3.GROUP BY
4.        grouper

每个分组中独立的列是随机抽取的。

一开始，这看起来是个好的解决方案，但是它有一个很严重的缺点。它依赖与所有的值都会返回这样一个假设条件，虽然从分组中任意抽取，但仍然属于一个记录。

虽然目前的MySQL中看起来是这样，当时并没有相应的文档记录，并且可能在任何时候发生改变（特别是如果MySQL打算在GROUP BY后应用index_union）。所以依赖这种行为是不安全的。

如果MySQL支持分析性函数，这个查询语句可以很容易以一种更清晰的方式改写。但是，如果没有这些，还是有办法做这样的操作，只有这个表有定义好的主键：

01.SELECT  a.*
02.FROM    (
03.        SELECT  DISTINCT grouper
04.        FROM    a
05.        ) ao
06.JOIN    a
07.ON      a.id =
08.        (
09.        SELECT  id
10.        FROM    a ai
11.        WHERE   ai.grouper = ao.grouper
12.        LIMIT 1
13.        )

#3. 从一个分组中抽样第一个记录

这是之前查询语句的一个变种：

1.SELECT  a.*
2.FROM    a
3.GROUP BY
4.        grouper
5.ORDER BY
6.        MIN(id) DESC

与上面的查询语句不同，这个语句尝试选择一个有最小id号的记录。

再次申明：不能保证a.*返回的独立值会属于一个有MIN(id)的记录（或者说属于一个记录）。

下面是怎样以一种清晰的形式操作:

01.SELECT  a.*
02.FROM    (
03.        SELECT  DISTINCT grouper
04.        FROM    a
05.        ) ao
06.JOIN    a
07.ON      a.id =
08.        (
09.        SELECT  id
10.        FROM    a ai
11.        WHERE   ai.grouper = ao.grouper
12.        ORDER BY
13.                ai.grouper, ai.id
14.        LIMIT 1
15.        )


这个查询和之前那个差不多，只是加上了ORDER BY来保证以id号排序的第一个记录会返回。

This query is just like the previous one but with ORDER BY added to ensure that the first record in id order will be returned.

#2. IN和逗号分隔符的一组值列表

这个查询想要查找能够匹配下面逗号分隔符所列字符串中任何一个值的结果：

1.SELECT  *
2.FROM    a
3.WHERE   column IN ('1, 2, 3')

这样做不会成功，因为字符串不能在IN列表中展开。

如果列值是VARCHAR类型的，它（以一个字符串的形式）可以和这个列表（也是以一个字符串的形式）比对，并且很显然不可能匹配上。如果该列是数值类型，这个列表会自动转型成数值类型（最好情况是第一个项目可以转型匹配成功）。

正确的处理这个查询的方法是在合适的In列表中重写这个查询。

1.SELECT  *
2.FROM    a
3.WHERE   column IN (1, 2, 3)

, 或者以内联视图:

01.SELECT  *

02.FROM    (
03.        SELECT  1 AS id
04.        UNION ALL
05.        SELECT  2 AS id
06.        UNION ALL
07.        SELECT  3 AS id
08.        ) q
09.JOIN    a
10.ON      a.column = q.id

, 但这并不总是可能实现。

想不改变查询参数解决这个问题我们可以用FIND_IN_SET:

1.SELECT  *
2.FROM    a
3.WHERE   FIND_IN_SET(column, '1,2,3')


但是这个函数不可优化搜索，并且要扫描整个表a。

#1. 带COUNT(*)的左连接

1.SELECT  a.id, COUNT(*)
2.FROM    a
3.LEFT JOIN
4.        b
5.ON      b.a = a.id
6.GROUP BY
7.        a.id

这个查询希望计算每个a中记录匹配b的数量。

但问题是在这样的查询中COUNT(*) 不会返回0值。如果a中记录没有匹配项，这个记录仍将返回并计数。

COUNT应该只记b中实际记录数。当代参数调用时， COUNT(*)忽略NULL值，我们可以传递b.a给它。当它是一个join条件的key值时，理论上在一个实际匹配中是不能为空，但实际情况下，在没有匹配结果时会是NULL:

1.SELECT  a.id, COUNT(b.a)
2.FROM    a
3.LEFT JOIN
4.        b
5.ON      b.a = a.id
6.GROUP BY
7.        a.id

P.S. 也许您会猜测: 不, 这些图片没有什么特殊的含义，我只是喜欢。

（这篇文章专业性太强，对我来说翻译难度较大，欢迎拍砖）

添加新评论