`

MySQL的10件事—它们也许和你预想的不一样

阅读更多
#10. 搜索一个“NULL”值 
SELECT  *  FROM    a  WHERE   a.column = NULL 
在SQL中,NULL什么也不等于,而且NULL也不等于NULL。这个查询不会返回任何结果的,实际上,当构建那个plan的时候,优化器会把这样的语句优化掉。

当搜索NULL值的时候,应该使用这样的查询:

SELECT  *  FROM    a  WHERE   a.column IS NULL 

#9. 使用附加条件的LEFT JOIN

SELECT  *  FROM    a  LEFT JOIN         b  ON      b.a = a.id  WHERE   b.column = 'something' 
除了从a返回每个记录(至少一次),当没有真正匹配的记录的时候,用NULL值代替缺失的字段之外,LEFT JOIN和INNER JOIN都是一样的。

但是,在LEFT JOIN之后才会检查WHERE条件,所以,上面这个查询在连接之后才会检查column。就像我们刚才了解到的那样,非NULL值才可以满足相等条件,所以,在a的记录中,那些在b中没有对应的条目的记录不可避免地要被过滤掉。

从本质上来说,这个查询是一个INNER JOIN,只是效率要低一些。

为了真正地匹配满足b.column = 'something'条件的记录(这时要返回a中的全部记录,也就是说,不过滤掉那些在b中没有对应的条目的记录),这个条件应该放在ON子句中:

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

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

我经常看到这样的查询:

SELECT  *  FROM    b  WHERE   b.column < 'something'        AND b.column IS NOT NULL 
实际上,这并不是一个错误:这个查询是有效的,是故意这样做的。但是,这里的IS NOT NULL是冗余的。

如果b.column是NULL,那么无法满足b.column < 'something'这个条件,因为任何一个和NULL进行的比较都会被判定为布尔NULL,是不会通过过滤器的。

有趣的是,这个附加的NULL检查不能和“大于”查询(例如:b.column > 'something')一起使用。

这是因为,在MySQL中,在ORDER BY的时候,NULL会排在前面,因此,一些人错误地认为NULL比任何其他的值都要小。

这个查询可以被简化:

SELECT  *  FROM    b  WHERE   b.column < 'something' 
在b.column中,不可能返回NULL

#7. 按照NULL来进行连接

SELECT  *  FROM    a  JOIN    b  ON      a.column = b.column 

在两个表中,当column是nullable的时候,这个查询不会返回两个字段都是NULL的记录,原因如上所述:两个NULL并不相等。

这个查询应该这样来写:

SELECT  *  FROM    a  JOIN    b  ON      a.column = b.column         OR (a.column IS NULL AND b.column IS NULL) 

MySQL的优化器会把这个查询当成一个“等值连接”,然后提供一个特殊的连接条件:ref_or_null

#6. NOT IN和NULL值

SELECT  a.*  FROM    a  WHERE   a.column NOT IN         (          SELECT column         FROM    b          ) 
如果在b.column中有一个NULL值,那么这个查询是不会返回任何结果的。和其他谓词一样,IN  和 NOT IN 遇到NULL也会被判定为NULL。

你应该使用NOT EXISTS重写这个查询:

SELECT  a.*  FROM    a  WHERE   NOT EXISTS          (          SELECT NULL         FROM    b         WHERE   b.column = a.column        ) 

不像IN,EXISTS总是被判定为true或false的。

#5. 对随机的样本进行排序

SELECT  *  FROM    a  ORDER BY         RAND(), column LIMIT 10 
这个查询试图选出10个随机的记录,按照column来排序。

ORDER BY会按照自然顺序来对输出结果进行排序:这就是说,当第一个表达式的值相等的时候,这些记录才会按照第二个表达式来排序。

但是,RAND()的结果是随机的。要让RAND()的值相等是行不通的,所以,按照RAND()排序以后,再按照column来排序也是没有意义的。

要对随机的样本记录进行排序,可以使用这个查询:

SELECT  *  FROM    (          SELECT  *          FROM    mytable          ORDER BY                 RAND()          LIMIT 10         ) q  ORDER BY        column 

#4. 通过一个组来选取任意的记录

这个查询打算通过某个组(定义为grouper来)来选出一些记录

SELECT  DISTINCT(grouper), a.*  FROM    a 

DISTINCT不是一个函数,它是SELECT子句的一部分。它会应用到SELECT列表中的所有列,实际上,这里的括号是可以省略的。所以,这个查询可能会选出grouper中的值都相同的记录(如果在其他列中,至少有一个列的值是不同的)。

有时,这个查询可以正常地使用( 这主要依赖于MySQL对GROUP BY的扩展):

SELECT  a.*  FROM    a  GROUP BY         grouper 
在某个组中返回的非聚合的列可以被任意地使用。

首先,这似乎是一个很好的解决方案,但是,它存在着一个很严重的缺陷。它依赖于这样一个假设:虽然可以通过组来任意地获取,但是返回的所有值都要属于一条记录。

虽然当前的实现似乎就是这样的,但是它并没有文档化,无论何时,它都有可能被改变(尤其是,当MySQL学会了在GROUP BY的后面使用index_union的时候)。所以依赖于这个行为并不安全。

如果MySQL支持分析函数的话,这个查询可以很容易地用另一种更清晰的方式来重写。但是,如果这张表拥有一个PRIMARY KEY的话,即使不使用分析函数,也可以做到这一点:

SELECT  a.*  FROM    (          SELECT  DISTINCT grouper          FROM    a          ) ao  JOIN    a  ON      a.id =          (          SELECT  id         FROM    a ai          WHERE   ai.grouper = ao.grouper          LIMIT 1          ) 

#3. 通过一个组来选取第一条记录

把前面那个查询稍微变化一下:

SELECT  a.*  FROM    a  GROUP BY         grouper  ORDER BY         MIN(id) DESC 
和前面那个查询不同,这个查询试图选出id值最小的记录。

同样:无法保证通过a.*返回的非聚合的值都属于id值最小的那条记录(或者任意一条记录)

这样做会更清晰一些:

SELECT  a.*  FROM    (          SELECT  DISTINCT grouper         FROM    a          ) ao  JOIN    a  ON      a.id =          (          SELECT  id          FROM    a ai          WHERE   ai.grouper = ao.grouper          ORDER BY                 ai.grouper, ai.id          LIMIT 1          ) 
这个查询和前面那个查询类似,但是使用额外的ORDER BY可以确保按id来排序的第一条记录会被返回。

#2. IN和‘,’——值的分隔列表

这个查询试图让column的值匹配用‘,’分隔的字符串中的任意一个值:

SELECT  *  FROM    a  WHERE   column IN ('1, 2, 3')
这不会正常发挥作用的,因为在IN列表中,那个字符串并不会被展开。

如果列column是一个VARCHAR,那么它(作为一个字符串)会和整个列表(也作为一个字符串)进行比较,当然,这不可能匹配。如果 column是某个数值类型,那么这个列表会被强制转换为那种数值类型(在最好的情况下,只有第一项会匹配)

处理这个查询的正确方法应该是使用合适的IN列表来重写它:

SELECT  *  FROM    a  WHERE   column IN (1, 2, 3) 
或者,也可以使用内联:

SELECT  *  FROM    (          SELECT  1 AS id          UNION ALL         SELECT  2 AS id          UNION ALL         SELECT  3 AS id          ) q  JOIN    a  ON      a.column = q.id 
但是,有时这是不可能的。

如果不想改变那个查询的参数,可以使用FIND_IN_SET:

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

但是,这个函数不可以利用索引从表中检索行,会在a上执行全表扫描。

#1. LEFT JOIN和COUNT(*)

SELECT  a.id, COUNT(*)  FROM    a  LEFT JOIN         b  ON      b.a = a.id  GROUP BY         a.id 
这个查询试图统计出对于a中的每条记录来说,在b中匹配的记录的数目。

问题是,在这样一个查询中,COUNT(*)永远不会返回一个0。对于a中某条记录来说,如果没有匹配的记录,那么那条记录还是会被返回和计数。

只有需要统计b中的记录数目的时候才应该使用COUNT。既然可以使用COUNT(*),那么我们也可以使用一个参数来调用它(忽略掉NULL),我们可以把b.a传递给它。在这个例子中,作为一个连接主键,它不可以为空,但是如果不想匹配,它也可以为空。
分享到:
评论

相关推荐

    银河麒麟v10安装MySQL5.7 含教程

    在银河麒麟v10中,你可以使用以下命令安装它们: ``` sudo apt-get install libaio1 libaio-dev jemalloc-dev ``` ### 二、下载MySQL 5.7二进制包 由于银河麒麟v10是arm架构,你需要找到支持aarch64的MySQL 5.7...

    mysql8和mysql5的连接驱动jar包

    这两个JAR文件是MySQL提供的Java Database Connectivity (JDBC) 驱动,它们允许Java程序与MySQL数据库进行交互。 1. **MySQL5.1.30驱动**: 这个版本的驱动是针对MySQL5.x系列的,它支持JDBC 4.0规范,发布于2013年...

    mysql驱动和MSsql驱动

    MySQL驱动和MS SQL驱动是两种不同的数据库连接器,它们分别用于连接到MySQL数据库服务器和Microsoft SQL Server数据库。在本文中,我们将深入探讨这两种驱动的工作原理、特性以及如何在应用程序中使用它们。 首先,...

    mysql8、mysql5两个版本驱动

    总的来说,了解不同版本的MySQL驱动与JDK的兼容性以及它们在功能上的差异,有助于开发者更好地选择和配置适合项目的数据库连接方案。在给定的场景下,如果项目使用的是JDK 1.5,那么必须使用`mysql-connector-java-...

    mysql下载,mysql工具

    还有MySQL CLI(命令行界面)和MySQL Utilities,它们是用于数据库管理的命令行工具集合。 6. 桌面快捷方式: 在安装过程中,你可以选择创建桌面快捷方式,方便快速启动MySQL Server或管理工具。这有助于提高工作...

    MySQL链接数据库jar包

    MySQL链接数据库jar包是Java应用程序与MySQL数据库进行交互时必不可少的组件。这两个jar包,mysql-connector-8.0.11和mysql-connector-java-5.1.13-bin,都是MySQL官方提供的驱动程序,用于实现Java应用程序的JDBC...

    MYSQL

    9.4 MySQL数据库表类型 10 从 MySQL 得到最大的性能 10.1 优化概述 10.2 系统/编译时和启动参数的调节 10.2.1 编译和链接如何影响 MySQL 的速度 10.2.2 磁盘问题 10.2.2.1 为数据库和...

    MySQL高可用学习笔记mysqlrouter_MHA.docx

    本文档主要讲解了 MySQL Router 和 MHA 的高可用配置和测试步骤。MySQL Router 是一个提供高可用和负载均衡的工具,而 MHA 是一个提供高可用和自动failover 的工具。下面我们将逐步讲解 MySQL Router 和 MHA 的配置...

    mysql 5.5版 头文件(mysql.h等)

    虽然在编写直接与MySQL交互的应用时可能用到不多,但它们对于了解MySQL底层工作原理非常有帮助。 7. `mysql_library_init()`和`mysql_library_end()`:这两个函数分别用于启动和结束MySQL库的使用,确保资源的正确...

    新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar

    │ 第二十课MySQL索引和调优.pdf │ 第二课MySQL入门介绍.pdf │ 第五课MySQL常用函数介绍.pdf │ 第八课InnoDB内核.pdf │ 第六课SQL高级应用.pdf │ 第十一课MySQL表分区8.0.pdf │ 第十七课Elasticsearch分享-...

    mysql4.0和5.0驱动

    在本文中,我们将深入探讨MySQL 4.0和5.0驱动,以及它们在使用PowerBuilder(PB)进行数据库导入时的角色。 首先,MySQL 4.0是在2003年发布的一个稳定版本,它提供了基本的SQL标准支持、事务处理和存储过程等功能。...

    mysql 服务端和客户端

    除此之外,还有许多图形化的MySQL客户端工具,如MySQL Workbench、Navicat、phpMyAdmin等,它们提供了更直观的界面和丰富的功能,适合不熟悉命令行或者需要更高效管理数据库的用户。如果对MySQL自带的客户端不习惯,...

    教你怎样安装MYSQL

    - 安装MySQL Administrator和MySQL Query Browser,解压缩后运行可执行文件,它们将提供图形化的数据库管理和查询界面。 4. **进一步配置** - 考虑到安全性和性能,你可能需要调整MySQL的配置,如修改my.ini文件...

    mysql mysql客户端工具

    本文将深入探讨MySQL客户端工具,特别是“Navicat for MySQL”,它是一款强大的数据库管理和开发工具。 MySQL客户端工具允许用户创建、修改和管理数据库,执行SQL查询,以及进行其他数据库维护任务。这些工具可以是...

    mysql基本操作 1 控制台打开和关闭Mysql服务

    这些是最基本的MySQL操作,熟练掌握它们将为你在数据库管理方面打下坚实的基础。 总结: 本教程介绍了如何在Windows环境下通过控制台来启动、停止MySQL服务,以及如何使用MySQL客户端进行基本的数据库操作。熟悉...

    Mysql安装及Windows11无法安装解决

    而`navicat15带激活码 (2).zip` 和 `SQLyog_Enterprise+8.zip` 文件是两个数据库管理工具,Navicat是一款强大的数据库管理和开发工具,SQLyog则提供图形化的MySQL管理界面,它们可以帮助你更方便地操作MySQL数据库。...

    mysql 的头文件和lib文件

    本文将详细解析标题和描述中提及的“mysql的头文件”(mysql.h)和“lib文件”(libmysql.lib),以及它们在C++中与MySQL数据库交互时的重要作用。 首先,`mysql.h`是MySQL C API的头文件,它提供了所有必要的函数...

    oracle驱动包和mysql驱动包

    Oracle驱动包和MySQL驱动包是数据库连接的重要组成部分,它们使得Java或其他编程语言能够与数据库进行交互,执行查询、更新和事务处理等操作。在本文中,我们将深入探讨这两个驱动包的特点、用途以及如何在实际开发...

    qt 5.13.2和5.15.2的mysql驱动

    对于QT 5.13.2和5.15.2这两个版本,它们各自包含的MySQL驱动确保了与这些特定版本的QT兼容性,以便开发者能利用最新的特性。 QT 5.13.2的MySQL驱动可能包含了对当时最新MySQL服务器版本的支持,以及对QT 5.x系列的...

    基于php和mysql的网站模板

    MySQL是一款开源、关系型数据库管理系统,以其高性能、高可用性和易于管理著称。它是LAMP(Linux, Apache, MySQL, PHP/Perl/Python)堆栈的重要组成部分,常被用于构建动态网站和Web应用程序。 在基于PHP和MySQL的...

Global site tag (gtag.js) - Google Analytics