MySql查询相关问题

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

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 (19970505);

mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');

mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');

mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');

mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');

mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';

mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;

mysql> SELECT MOD(idate,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);

mysql> INSERT INTO my_table (phone) VALUES ('');

这两条语句均会将值插入phone（电话）列，但第1条语句插入的是NULL值，第2条语句插入的是空字符串。第1种情况的含义可被解释为“电话号码未知”，而第2种情况的含义可被解释为“该人员没有电话，因此没有电话号码”。

为了进行NULL处理，可使用IS NULL和IS NOT NULL操作符以及IFNULL()函数。

在SQL中，NULL值与任何其它值的比较（即使是NULL）永远不会为“真”。包含NULL的表达式总是会导出NULL值，除非在关于操作符的文档中以及表达式的函数中作了其他规定。下述示例中的所有列均返回NULL：

mysql> SELECT 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_1 ... tbl_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 (1, 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行。