A.5. 与查询有关的事宜 - MySQL 中文手册

返回介绍

A.5. 与查询有关的事宜

发布于 2019-06-13 字数 11304 浏览 949 评论 0

A.5.1. 搜索中的大小写敏感性
A.5.2. 使用DATE列方面的问题
A.5.3. 与NULL值有关的问题
A.5.4. 与列别名有关的问题
A.5.5. 非事务表回滚失败
A.5.6. 从相关表删除行
A.5.7. 解决与不匹配行有关的问题
A.5.8. 与浮点比较有关的问题

A.5.1. 搜索中的大小写敏感性

在默认情况下,MySQL搜索不区分大小写(但某些字符集始终区分大小写,如czech)。这意味着,如果你使用col_name LIKE 'a%'进行搜索,你将获得以A或a开始的所有列。如果打算使搜索区分大小写,请确保操作数之一具有区分大小写的或二进制校对。例如,如果你正在比较均适用latin1字符集的列和字符串,可使用COLLATE操作符,使1个操作数具有latin1_general_cs或latin1_bin校对特性。例如:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

如果希望总是以区分大小写的方式处理列,可使用区分大小写的或二进制校对声明它。请参见13.1.5节,“CREATE TABLE语法”。

简单的比较操作(>=, >, =, <, <=, 排序和分组)基于每个字符的“排序值”。具有相同排序值的字符(如‘E’,‘e’,和‘é’)将被当作相同的写字符。

A.5.2. 使用DATE列方面的问题

DATE值的格式是'YYYY-MM-DD'。按照标准的SQL,不允许其他格式。在UPDATE表达式以及SELECT语句的WHERE子句中应使用该格式。例如:

mysql> SELECT * FROM tbl_name WHERE date >= '2003-05-05';

为了方便,如果日期是在数值环境下使用的,MySQL会自动将日期转换为数值(反之亦然)。它还具有相当的智能,在更新时或在与TIMESTAMP、DATE或DATETIME列比较日期的WHERE子句中,允许“宽松的”字符串形式(“宽松形式”表示,任何标点字符均能用作各部分之间的分隔符。例如,'2004-08-15'和'2004#08#15'是等同的)。MySQL还能转换不含任何分隔符的字符串(如'20040815'),前体是它必须是有意义的日期。

使用<、<=、=、>=、>、或BETWEEN操作符将DATE、TIME、DATETIME或TIMESTAMP与常量字符串进行比较时,MySQL通常会将字符串转换为内部长整数,以便进行快速比较(以及略为“宽松”的字符串检查)。但是,该转换具有下述例外:

比较两列时

将DATE、TIME、DATETIME或TIMESTAMP列与表达式进行比较时

使用其他比较方法时,如IN或STRCMP()。

对于这些例外情形,会将对象转换为字符串并执行字符串比较,采用该方式进行比较。

为了保持安全,假定按字符串比较字符串,如果你打算比较临时值和字符串,将使用恰当的字符串函数。

对于特殊日期'0000-00-00',能够以'0000-00-00'形式保存和检索。在MyODBC中使用'0000-00-00'日期时,对于MyODBC 2.50.12或更高版本,该日期将被自动转换为NULL,这是因为ODBC不能处理这类日期。

由于MySQL能够执行前面所介绍的转换,下述语句均能正常工作:

mysql> INSERT INTO tbl_name (idate) VALUES (NULL,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

但是,下述语句不能正常工作:

mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'20030505')=0;

STRCMP()是一种字符串函数,它能将idate转换为'YYYY-MM-DD'格式的字符串,并执行字符串比较。它不能将'20030505'转换为日期'2003-05-05'并进行日期比较。

如果你正在使用ALLOW_INVALID_DATES SQL模式,MySQL允许以仅执行给定的有限检查方式保存日期:MySQL仅保证天位于1~31的范围内,月位于1~12的范围内。

这样就使得MySQL很适合于Web应用程序,其中,你能获得三个不同字段中的年、月、日值,也能准确保存用户插入的值(无日期验证)。

如果未使用NO_ZERO_IN_DATE SQL模式,“天”和“月”部分可能为0。如果你打算将生日保存在DATE列而且仅知道部分日期,它十分方便。

如果未使用NO_ZERO_DATE SQL模式,MySQL也允许你将'0000-00-00'保存为“伪日期”。在某些情况下,它比使用NULL值更方便。

如果无法将日期转换为任何合理值,“0”将保存在DATE列中,并被检索为'0000-00-00'。这是兼顾速度和便利性的事宜。我们认为,数据库服务器的职责是检索与你保存的日期相同的日期(即使在任何情况下,数据在逻辑上不正确也同样)。我们认为,对日期的检查应由应用程序而不是服务器负责。

如果你希望MySQL检查所有日期并仅接受合法日期(除非由IGNORE覆盖),应将sql_mode设置为"NO_ZERO_IN_DATE,NO_ZERO_DATE"。

A.5.3. 与NULL值有关的问题

对于SQL的新手,NULL值的概念常常会造成混淆,他们常认为NULL是与空字符串''相同的事。情况并非如此。例如,下述语句是完全不同的:

mysql> INSERT INTO my_table (phone) VALUES (NULL, 1+NULL, CONCAT('Invisible',NULL);

如果打算搜索列值为NULL的列,不能使用expr = NULL测试。下述语句不返回任何行,这是因为,对于任何表达式,expr = NULL永远不为“真”:

mysql> SELECT * FROM my_table WHERE phone = NULL;

要想查找NULL值,必须使用IS NULL测试。在下面的语句中,介绍了查找NULL电话号码和空电话号码的方式:

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';

更多信息和示例,请参见3.3.4.6节,“使用NULL值”。

如果你正在使用MyISAM、InnoDB、BDB、或MEMORY存储引擎,能够在可能具有NULL值的列上增加1条索引。如不然,必须声明索引列为NOT NULL,而且不能将NULL插入到列中。

用LOAD DATA INFILE读取数据时,对于空的或丢失的列,将用''更新它们。如果希望在列中具有NULL值,应在数据文件中使用\N。在某些情况下,也可以使用文字性单词“NULL”。请参见13.2.5“LOAD DATA INFILE语法”。

使用DISTINCT、GROUP BY或ORDER BY时,所有NULL值将被视为等同的。

使用ORDER BY时,首先将显示NULL值,如果指定了DESC按降序排列,NULL值将最后显示。

对于聚合(累计)函数,如COUNT()、MIN()和SUM(),将忽略NULL值。对此的例外是COUNT(*),它将计数行而不是单独的列值。例如,下述语句产生两个计数。首先计数表中的行数,其次计数age列中的非NULL值数目:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

对于某些列类型,MySQL将对NULL值进行特殊处理。如果将NULL插入TIMESTAMP列,将插入当前日期和时间。如果将NULL插入具有AUTO_INCREMENT属性的整数列,将插入序列中的下一个编号。

A.5.4. 与列别名有关的问题

可以使用别名来引用GROUP BY、ORDER BY或HAVING子句中的列。别名也能用于为列提供更好的名称:

SELECT SQRT(a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

标准SQL不允许在WHERE子句中已用列别名。这是因为,执行WHERE代码时,可能尚未确定列值。例如,下述查询是非法的:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

执行WHERE语句以确定哪些行应被包含在GROUP BY部分中,而HAVING用于确定应使用结果集中的哪些行。

A.5.5. 非事务表回滚失败

执行ROLLBACK(回滚)时,如果收到下述消息,表示事务中使用的1个或多个表不支持事务:

警告:某些更改的非事务性表不能被回滚。

这些非事务性表不受ROLLBACK语句的影响。

如果在事务中意外地混合了事务性表和非事务性表,导致该消息的最可能原因是,你认为本应是事务性的表实际上不是。如你试图使用mysqld服务器不支持的事务性存储引擎(或用启动选项禁止了它)创建表,就可能出现该情况。如果mysqld不支持存储引擎,它将以MyISAM表创建表,这是非事务性表。

可使用下述语句之一检查表的标类型:

SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;

请参见13.5.4.18节,“SHOW TABLE STATUS语法以及13.5.4.5节,“SHOW CREATE TABLE语法”。

使用下述语句,可检查mysqld服务器支持的存储引擎:

SHOW ENGINES;

也可以使用下述语句,检查与你感兴趣的存储引擎有关的变量值:

SHOW VARIABLES LIKE 'have_%';

例如,要想确定InnoDB存储引擎是否可用,可检查have_innodb变量的值。

请参见13.5.4.8节,“SHOW ENGINES语法”和13.5.4.21节,“SHOW VARIABLES语法”。

A.5.6. 从相关表删除行

如果针对related_table的DELETE语句的总长度超过1MB(系统变量max_allowed_packet的默认值),应将其分为较小的部分,并执行多个DELETE语句。如果related_column是索引列,为每条语句指定100~1000个related_column值,或许能获得更快的DELETE速度。如果related_column不是索引列,速度与IN子句中的参量数目无关。

A.5.7. 解决与不匹配行有关的问题

如果有使用了很多表的复杂查询,但未返回任何行,应采用下述步骤找出什么出错:

用EXPLAIN测试查询,以检查是否发现某事显然出错。请参见7.2.1节,“EXPLAIN语法(获取关于SELECT的信息)”。

仅选择在WHERE子句中使用的列。

从查询中1次删除1个表,直至返回了某些行为止。如果表很大,较好的主意是在查询中使用LIMIT 10。

对于具有与上次从查询中删除的表匹配的行的列,发出SELECT查询。

如果将FLOAT或DOUBLE列与具有数值类型的数值进行比较,不能使用等式(=)比较。在大多数计算机语言中,该问题很常见,这是因为,并非所有的浮点值均能以准确的精度保存。在某些情况下,将FLOAT更改为DOUBLE可更正该问题。请参见A.5.8节,“与浮点比较有关的问题”。

如果仍不能找出问题之所在,请创建能与显示问题的“mysql test < query.sql”一起运行的最小测试。通过使用mysqldump –quick db_name tbl_name_1tbl_name_n > query.sql转储表,可创建测试文件。在编辑器中打开文件,删除某些插入的行(如果有超出演示问题所需的行),并在文件末尾添加SELECT语句。

通过执行下述命令,验证测试文件能演示问题:

shell> mysqladmin create test2
shell> mysql test2 < query.sql

使用mysqlbug将测试文件张贴到哟娜通用MySQL邮件列表。请参见1.7.1.1节,“The MySQL邮件列表”。

A.5.8. 与浮点比较有关的问题

注意,下述部分主要与DOUBLE和FLOAT列相关,原因在于浮点数的不准确本质。MySQL使用64位十进制数值的精度执行DECIMAL操作,当处理DECIMAL列时,应能解决大多数常见的不准确问题。

浮点数有时会导致混淆,这是因为它们无法以准确值保存在计算机体系结构中。你在屏幕上所看到的值通常不是数值的准确值。对于FLOAT和DOUBLE列类型,情况就是如此。DECIMAL列能保存具有准确精度的值,这是因为它们是由字符串表示的。

在下面的示例中,介绍了使用DOUBLE时的问题:

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (NULL, 101.40, 21.40), (1, -80.00, 0.00),
    -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
    -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
    -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
    -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
    -> (6, 0.00, 0.00), (6, -51.40, 0.00);
 
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
 
+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

结果是正确的。尽管前5个记录看上去不应能进行比较测试(a和b的值看上去没有什么不同),但它们能进行比较,这是因为显示的数值间的差异在十分位左右,具体情况取决于计算机的体系结构。

如果列d1和d2定义为DECIMAL而不是DOUBLE,SELECT查询的结果仅包含1行,即上面显示的最后1行。

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

目前还没有任何评论,快来抢沙发吧!