MySQL 5.1参考手册 :: A. 问题和常见错误(4)

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行。

A.6. 与优化器有关的事宜

MySQL采用了基于开销的优化器，以确定处理查询的最解方式。在很多情况下，MySQL能够计算最佳的可能查询计划，但在某些情况下，MySQL没有关于数据的足够信息，不得不就数据进行“有教养”的估测。

当MySQL未能做“正确的”事时，可使用下述工具来帮助MySQL：

使用EXPLAIN语句获取关于MySQL如何处理查询的信息。要想使用它，可在SELECT语句前添加关键字EXPLAIN：

mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i;

关于EXPLAIN的详细讨论，请参见7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

使用ANALYZE TABLE tbl_name，为已扫描的表更新键分配。请参见13.5.2.1节，“ANALYZE TABLE语法”。

为已扫描的表使用FORCE INDEX，通知MySQL：与使用给定的索引相比，表扫描开销昂贵。请参见13.2.7节，“SELECT语法”。

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

WHERE t1.col_name=t2.col_name;

USE INDEX和IGNORE INDEX也有一定的帮助。

关于全局和表级别的STRAIGHT_JOIN。请参见13.2.7节，“SELECT语法”。

你可以调节全局或线程类系统变量。例如，用“--max-seeks-for-key=1000”选项启动mysqld，或使用“SET max_seeks_for_key=1000”来通知优化器：假定任何表扫描均不会导致1000个以上的键搜索。请参见5.3.3节，“服务器系统变量”。

A.7. 与表定义有关的事宜

A.7.1. 与ALTER TABLE有关的问题

A.7.2. 如何更改表中的列顺序

A.7.3. TEMPORARY TABLE问题

A.7.1. 与ALTER TABLE有关的问题

ALTER TABLE将表更改为当前字符集。如果在执行ALTER TABLE操作期间遇到重复键错误，原因在于新的字符集将2个键映射到了相同值，或是表已损坏。在后一种情况下，应在表上运行REPAIR TABLE。

如果ALTER TABLE失败并给出下述错误，问题可能是因为在ALTER TABLE操作的早期阶段出现MySQL崩溃，没有名为A-xxx或B-xxx的旧表：

Error on rename of './database/name.frm'

to './database/B-xxx.frm' (Errcode: 17)

在该情况下，进入MySQL数据目录，并删除其名称为以A-或B-开始的所有文件（或许你希望将它们移动到其他地方而不是删除它们）。

ALTER TABLE的工作方式如下：

• 用请求的结构变化创建名为A-xxx的新表。
• 将所有行从原始表拷贝到A-xxx。
• 将原始表重命名为B-xxx。
• 将A-xxx重命名为原始表的名称。
• 删除B-xxx。

如果在重命名操作中出错，MySQL将尝试撤销更改。如果错误很严重（尽管这不应出现），MySQL会将旧表保留为B-xxx。简单地在系统级别上重命名表文件，应能使数据复原。

如果在事务性表上使用ALTER TABLE，或正在使用Windows或OS/2操作系统，如果已在表上执行了LOCK TABLE操作，ALTER TABLE将对表执行解锁操作。这是因为InnoDB和这类操作系统不能撤销正在使用的表。

A.7.2. 如何更改表中的列顺序

首先，请考虑是否的确需要更改表中的列顺序。SQL的核心要点是从数据存储格式获取应用。总应指定检索数据的顺序。在下面的第1条语句中，以col_name1、col_name2、col_name3顺序返回列；在第2条语句中，以col_name1、col_name3、col_name2顺序返回列：

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;

mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

如果决定更改表列的顺序，可执行下述操作：

1. 用具有新顺序的列创建新表。
2. 执行该语句：

mysql> INSERT INTO new_table

    -> SELECT columns-in-new-order FROM old_table;

3. 撤销或重命名old_table。
4. 将新表重命名为原始名称：

mysql> ALTER TABLE new_table RENAME old_table;

SELECT *十分适合于测试查询。但是，在应用程序中，永远不要依赖SELECT *的使用，不要依赖根据其位置检索列。如果添加、移动或删除了列，所返回的列的顺序和位置不会保持相同。对表结构的简单更改也会导致应用程序失败。

A.7.3. TEMPORARY TABLE问题

下面介绍了对使用TEMPORARY表的限制：

• TEMPORARY表只能是HEAP、ISAM、MyISAM、MERGE、或InnoDB类型。
• 在相同的查询中，不能引用TEMPORARY表1次以上。例如，下例不能正常工作：

mysql> SELECT * FROM temp_table, temp_table AS t2;

错误1137：不能再次打开表：'temp_table'

• SHOW TABLES语句不会列出TEMPORARY表。
• 不能使用RENAME重命名TEMPORARY表。但能使用ALTER TABLE取而代之：

mysql> ALTER TABLE orig_name RENAME new_name;

A.8. MySQL中的已知事宜

A.8.1. MySQL中的打开事宜

在本节中，列出了当前MySQL版本中的已知事宜。

关于平台相关事宜的更多信息，请参见2.12节，“具体操作系统相关的注意事项”和附录E：移植到其他系统中的安装和移植说明。

A.8.1. MySQL中的打开事宜

下面列出了已知问题，更正它们具有较高的优先级：

• 如果将NULL值与使用ALL/ANY/SOME的子查询进行比较，而且子查询返回空的结果，比较操作会评估NULL的非标准结果而不是TRUE或FALSE。在MySQL 5.1中将更正该问题。
• 对于IN的线子查询优化不像“=”那样有效。
• 即使使用了lower_case_table_names=2（允许MySQL记住数据库名和表名使用的大小写），对于函数DATABASE()或在各种日志内（在不区分大小写的系统上），MySQL也不会记住数据库名使用的大小写情况。
• 在复制操作中，撤销FOREIGN KEY约束不工作，这是因为约束可能在从服务器上有另一个名称。
• REPLACE（以及具有REPLACE选项的LOAD DATA）不会触发ON DELETE CASCADE。
• 如果未使用所有列而且仅使用DISTINCT列表中的列，在GROUP_CONCAT()中，DISTINCT不能与ORDER BY一起工作。
• 如果1位用户拥有长时间运行的事务，而且另1位用户撤销了在事务中更新的某1表，那么在表用于事务本身之前，存在较小的机会，会在二进制日志中包含DROP TABLE命令。我们计划更正该问题，方法是让DROP TABLE命令等待，直至表未在任何事务中使用为止。
• 将大的整数值（介于2⁶³和2⁶⁴–1之间）插入数值或字符串列时，它将作为负值插入，这是因为该数值是在有符号整数环境下评估的。
• 如果服务器运行在不具备二进制日志功能的条件下，FLUSH TABLES WITH READ LOCK不能屏蔽COMMIT，执行完整备份时这可能会导致问题（表间的一致性问题）。
• 在某些情况下，作用在BDB表上的ANALYZE TABLE会导致表不可用，直至重启mysqld为止。如果出现该情况，请在MySQL错误文件中查找下述形式的错误：

001207 22:07:56  bdb:  log_flush: LSN past current end-of-log

• 在所有事务完成之前，不要在BDB表（正在其上运行多语句事务）上执行ALTER TABLE（可能会忽略事务）。
• 对于正在使用INSERT DELAYED的表，在其上执行ANALYZE TABLE、OPTIMIZE TABLE和REPAIR TABLE时，可能会导致问题。
• 在表上执行LOCK TABLE ...和FLUSH TABLES ...时，不保证没有完成一半的事务。
• BDB表打开的速度相对较慢。如果你在数据库上有很多BDB表，如果未使用“-A”选项或正使用再混编功能，要想在数据库上使用mysql客户端，需要花费较长的时间。当你有大的表高速缓冲时，这点尤其明显。
• 复制功能采用了查询级日志功能：主服务器将已执行的查询写入二进制日志。这是一种速度很快、简洁和有效的记录方法，在大多数情况下工作良好。

如果以特定的方式设计查询，使得数据更改是非决定性（通常不推荐，即使在复制之外也同样），主服务器和从服务器上的数据将变得不同。

例如：

• 将0或NULL值插入AUTO_INCREMENT列中的CREATE ... SELECT或INSERT ... SELECT语句。
• DELETE，如果从具有ON DELETE CASCADE属性的外键的表中删除行。
• REPLACE ... SELECT、INSERT IGNORE ... SELECT，如果在插入的数据中具有重复键。

当且仅当前述查询没有保证决定行顺序的ORDER BY子句时。

例如，对于不具有ORDER BY的INSERT ... SELECT，SELECT可能会以不同的顺序返回行（它会导致具有不同等级的行，从而导致AUTO_INCREMENT列中的不同数值），具体情况取决于优化器在主服务器和从服务器上所作的选择。

在主服务器和从服务器上，查询将进行不同的优化，仅当：

• 使用不同的存储引擎在主服务器上而不是从服务器上保存表。（能够在主服务器和从服务器上使用不同的存储引擎。例如，如果从服务器具有较少的可用磁盘空间，可以在主服务器上使用InnoDB，但在 从服务器桑使用MyISAM）。
• 在主服务器和从服务器上，MySQL缓冲区大小是不同的（key_buffer_size等）。
• 在主服务器和从服务器上运行不同的MySQL版本，版本间的优化器代码也不同。

该问题也会影响使用mysqlbinlog|mysql的数据库恢复。

避免该问题的最简单方法是，为前述的非决定性查询增加ORDER BY子句，以确保总是以相同的顺序保存或更改行。

在将来的MySQL版本中，需要时，我们将自动增加ORDER BY子句。

下面列出了已知的事宜，这些事宜将在恰当的时候更正：

• 日志文件名基于服务器主机名（如果未使用启动选项指定文件名的话）。如果更改了主机名，你将不得不使用诸如“--log-bin=old_host_name-bin”等选下美国。另一种选择是重命名旧文件，以反映主机名变更情况（如果是二进制日志，需要编辑二进制日志索引文件，并更正binlog名称）。请参见5.3.1节，“mysqld命令行选项”。
• Mysqlbinlog不删除执行LOAD DATA INFILE命令后遗留的临时文件。请参见8.6节，“mysqlbinlog：用于处理二进制日志文件的实用工具”。
• RENAME不能与TEMPORARY表一起工作，也不能与MERGE表中使用的表一起工作。
• 由于表定义文件的保存方式，不能在表名、列名或枚举中使用字符255（CHAR(255)）。按照安排，当我们实施了新的表定义格式文件时，将在5.1版中更正该问题。
• 使用SET CHARACTER SET时，不能在数据库、表和列名中使用转换的字符。
• 不能在LIKE ... ESCAPE中与ESCAPE一起使用‘_’或‘%’。
• 如果你有1个DECIMAL列，其中，相同的数值以不同的格式保存（例如，+01.00、1.00、01.00），GROUP BY可能会将每个值当作不同的值。
• 使用MIT-pthreads时，不能在另一个目录下创建服务器。这是因为它需要更改MIT-pthreads，我们不太会更正该问题。请参见2.8.5 “MIT-pthreads注意事项”。
• 在GROUP BY、ORDER BY或DISTINCT中，不能可靠地使用BLOB和TEXT值。在这类情况下，与BLOB值进行比较时，仅使用最前的max_sort_length字节。max_sort_length的默认值是1024，可在服务器启动时或运行时更改它。
• 数值计算是使用BIGINT或DOUBLE（正常情况下均为64位长）进行的。你所能获得的精度取决于函数。通用规则是位函数是按BIGINT精度执行的，IF和ELT()是按BIGINT或DOUBLE精度执行的，其余的函数是按DOUBLE精度执行的。对于除位字段外的其他数，如果大于63位（9223372036854775807），应避免使用无符号长long值。
• 在1个表中，最多能有255个ENUM和SET列。
• 在MIN()、MAX()以及其他聚合函数中，MySQL目前会根据其字符串值比较ENUM和SET列，而不是根据字符串在集合中的相对位置。
• mysqld_safe会将来自mysqld的所有消息再定向到mysqld日志。与之相关的1个问题是，如果你执行mysqladmin refresh关闭并再次打开日志，stdout和stderr仍会被重定向到旧的日志。如果你以广义方式使用“--log”，应编辑mysqld_safe以记录到host_name.err而不是host_name.log，以便通过删除它并执行mysqladmin refresh，方便地收回为旧日志分配的空间。
• 在UPDATE语句中，列从左向右更新。如果引用了已更新的列，你将得到更新值而不是原始值。例如，下述语句会将KEY增加2，而不是1：

mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;

• 你可以在相同查询中引用多个临时表，但不能引用任何给定的临时表1次以上。例如，下述语句不能正常工作：

mysql> SELECT * FROM temp_table, temp_table AS t2;

错误1137：不能再次打开表：'temp_table'

• 当你在联合操作中使用“隐含”列时，与未使用隐含列相比，优化器将以不同的方式处理DISTINCT。在联合操作中，隐含列将作为结果的组成部份计数（即使未显示），但在正常查询中，隐含列不参与DISTINCT比较。在以后，我们可能会更改该情况，在执行DISTINCT时不比较隐含列。

例如：

SELECT DISTINCT mp3id FROM band_downloads

       WHERE userid = 9 ORDER BY id DESC;

以及

SELECT DISTINCT band_downloads.mp3id

       FROM band_downloads,band_mp3

       WHERE band_downloads.userid = 9

       AND band_mp3.id = band_downloads.mp3id

       ORDER BY band_downloads.id DESC;

在第2种情况下，使用MySQL服务器3.23.x，可在结果集中获得2个等同行（这是因为，隐藏ID列中的值可能不同）。

注意，在结果集中，仅对不含ORDER BY列的查询才会出现该情况。

• 如果在返回空集的查询上执行PROCEDURE，在某些情况下，PROCEDURE不转换列。
• 创建具有MERGE类型的表时，不检查基本表是否具有兼容的类型。
• 如果使用ALTER TABLE为MERGE表中使用的表增加了UNIQUE索引，然后在MERGE表上增加了正常索引，如果在表中存在旧的、非UNIQUE键，对于这些表，键顺序是不同的。这是因为，ALTER TABLE会将UNIQUE索引放在正常索引之前，以便能尽早检测到重复的键。

 

---

这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问dev.mysql.com。原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。