IN 的子查询优化效果不如 =。Subquery optimization for IN is not as effective as for =.

即使您使用 lower_case_table_names=2（它允许 MySQL 记住数据库和表名的大小写），MySQL 也不会记住 DATABASE() 函数或各种日志中使用的数据库名的大小写（在不区分大小写的系统上）。Even if you use lower_case_table_names=2 (which enables MySQL to remember the case used for databases and table names), MySQL does not remember the case used for database names for the function DATABASE() or within the various logs (on case-insensitive systems).

在复制中删除 FOREIGN KEY 约束不起作用，因为该约束在副本上可能有不同的名称。Dropping a FOREIGN KEY constraint does not work in replication because the constraint may have another name on the replica.

REPLACE（以及带有REPLACE选项的LOAD DATA）不会触发ON DELETE CASCADE。REPLACE (and LOAD DATA with the REPLACE option) does not trigger ON DELETE CASCADE.

如果不使用DISTINCT列表中所有且唯一的列，那么带有ORDER BY的DISTINCT在GROUP_CONCAT()中无法工作。DISTINCT with ORDER BY does not work inside GROUP_CONCAT() if you do not use all and only those columns that are in the DISTINCT list.

当将一个大整数值（介于 2⁶³ 和 2⁶⁴−1 之间）插入到 decimal 或 string 列时，它会被作为负值插入，因为这个数字是在有符号整数上下文中进行评估的。When inserting a big integer value (between 2⁶³ and 2⁶⁴−1) into a decimal or string column, it is inserted as a negative value because the number is evaluated in signed integer context.

使用基于语句的二进制日志时，源服务器会将执行的查询写入二进制日志。这是一种非常快速、紧凑且高效的日志记录方法，在大多数情况下都能完美工作。然而，如果查询的设计方式导致数据修改是不可确定的，则源服务器和副本上的数据可能会不同（通常即使在非复制环境下，这种做法也不推荐）。With statement-based binary logging, the source server writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works perfectly in most cases. However, it is possible for the data on the source and replica to become different if a query is designed in such a way that the data modification is nondeterministic (generally not a recommended practice, even outside of replication).

例如：For example:

当且仅当前面的查询没有ORDER BY子句来保证确定的顺序时。If and only if the preceding queries have no ORDER BY clause guaranteeing a deterministic order.

例如，对于没有ORDER BY的INSERT ... SELECT，SELECT可能以不同的顺序返回行（这会导致某行具有不同的排名，从而在AUTO_INCREMENT列中得到不同的值），具体取决于源端和副本端优化器所做的选择。For example, for INSERT ... SELECT with no ORDER BY, the SELECT may return rows in a different order (which results in a row having different ranks, hence getting a different number in the AUTO_INCREMENT column), depending on the choices made by the optimizers on the source and replica.

查询在源端和副本端被优化不同，只有在以下情况下：A query is optimized differently on the source and replica only if:

这个问题也可能影响使用 mysqlbinlog|mysql 进行的数据库恢复。This problem may also affect database restoration using mysqlbinlog|mysql.

避免此问题的最简单方法是在上述非确定性查询中添加ORDER BY子句，以确保行始终以相同的顺序存储或修改。使用基于行的或混合的日志格式也可以避免此问题。The easiest way to avoid this problem is to add an ORDER BY clause to the aforementioned nondeterministic queries to ensure that the rows are always stored or modified in the same order. Using row-based or mixed logging format also avoids the problem.

如果您在启动选项中未指定文件名，日志文件名将基于服务器主机名。要在更改主机名后保留相同的日志文件名，必须显式使用诸如 --log-bin=old_host_name-bin 之类的选项。参见 第 7.1.7 节，“服务器命令选项”。或者，将旧文件重命名以反映您的主机名更改。如果这些是二进制日志，您还必须编辑二进制日志索引文件，并在那里修正二进制日志文件名。（对于副本上的中继日志也同样适用。）Log file names are based on the server host name if you do not specify a file name with the startup option. To retain the same log file names if you change your host name to something else, you must explicitly use options such as --log-bin=old_host_name-bin. See Section 7.1.7, “Server Command Options”. Alternatively, rename the old files to reflect your host name change. If these are binary logs, you must edit the binary log index file and fix the binary log file names there as well. (The same is true for the relay logs on a replica.)

mysqlbinlog 不会删除在执行 LOAD DATA 语句后留下的临时文件。见 第 6.6.9 节，“mysqlbinlog —— 用于处理二进制日志文件的实用程序”。mysqlbinlog does not delete temporary files left after a LOAD DATA statement. See Section 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”.

RENAME 无法用于 TEMPORARY 表或用于 MERGE 表的表。RENAME does not work with TEMPORARY tables or tables used in a MERGE table.

在使用 SET CHARACTER SET 时，不能在数据库、表和列名中使用翻译后的字符。When using SET CHARACTER SET, you cannot use translated characters in database, table, and column names.

服务器在比较数据值时只使用前 max_sort_length 字节。这意味着，如果值在前 max_sort_length 字节之后才不同，它们将不能可靠地用于 GROUP BY、ORDER BY 或 DISTINCT。为了解决此问题，可以增加该变量的值。max_sort_length 的默认值为 1024，可以在服务器启动时或运行时进行更改。The server uses only the first max_sort_length bytes when comparing data values. This means that values cannot reliably be used in GROUP BY, ORDER BY, or DISTINCT if they differ only after the first max_sort_length bytes. To work around this, increase the variable value. The default value of max_sort_length is 1024 and can be changed at server startup time or at runtime.

数值计算使用 BIGINT 或 DOUBLE（通常都是 64 位）。你得到的精度取决于函数。一般规则是，位函数使用 BIGINT 精度，IF() 和 ELT() 使用 BIGINT 或 DOUBLE 精度，其余使用 DOUBLE 精度。除位字段外，如果无符号长整型值可能超过 63 位（9223372036854775807），应尽量避免使用。Numeric calculations are done with BIGINT or DOUBLE (both are normally 64 bits long). Which precision you get depends on the function. The general rule is that bit functions are performed with BIGINT precision, IF() and ELT() with BIGINT or DOUBLE precision, and the rest with DOUBLE precision. You should try to avoid using unsigned long long values if they resolve to be larger than 63 bits (9223372036854775807) for anything other than bit fields.

在一个表中，你最多可以有 255 个 ENUM 和 SET 列。You can have up to 255 ENUM and SET columns in one table.

在 MIN()、MAX() 以及其他聚合函数中，MySQL 当前通过字符串值比较 ENUM 和 SET 列，而不是通过字符串在集合中的相对位置进行比较。In MIN(), MAX(), and other aggregate functions, MySQL currently compares ENUM and SET columns by their string value rather than by the string's relative position in the set.

在UPDATE语句中，列是从左到右更新的。如果你引用已更新的列，你将获得更新后的值，而不是原始值。例如，下面的语句将KEY增加2，而不是1：In an UPDATE statement, columns are updated from left to right. If you refer to an updated column, you get the updated value instead of the original value. For example, the following statement increments KEY by 2, not 1:

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

你可以在同一个查询中引用多个临时表，但不能多次引用同一个临时表。例如，下面的做法是不可行的：You can refer to multiple temporary tables in the same query, but you cannot refer to any given temporary table more than once. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

在使用连接时，如果使用“隐藏”列，优化器可能会以不同方式处理DISTINCT，而不使用时则不同。在连接中，隐藏列会被计入结果（即使它们未显示），而在普通查询中，隐藏列不会参与DISTINCT比较。The optimizer may handle DISTINCT differently when you are using “hidden” columns in a join than when you are not. In a join, hidden columns are counted as part of the result (even if they are not shown), whereas in normal queries, hidden columns do not participate in the DISTINCT comparison.

例如：An example of this is:

SELECT DISTINCT mp3id FROM band_downloads
       WHERE userid = 9 ORDER BY id DESC;

以及and

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;

在第二种情况下，你可能会在结果集中得到两行相同的记录（因为隐藏的id列中的值可能不同）。In the second case, you may get two identical rows in the result set (because the values in the hidden id column may differ).

注意，这种情况只发生在结果中没有ORDER BY列的查询。Note that this happens only for queries that do not have the ORDER BY columns in the result.

如果你在返回空集的查询上执行PROCEDURE，在某些情况下，PROCEDURE不会转换这些列。If you execute a PROCEDURE on a query that returns an empty set, in some cases the PROCEDURE does not transform the columns.

创建类型为 MERGE 的表时，不会检查底层表是否是兼容类型。Creation of a table of type MERGE does not check whether the underlying tables are compatible types.

如果你使用 ALTER TABLE 向用于 MERGE 表的表添加 UNIQUE 索引，然后再在 MERGE 表上添加普通索引，如果该表中以前存在非 UNIQUE 键，那么表的键顺序会不同。这是因为 ALTER TABLE 会将 UNIQUE 索引放在普通索引之前，以便尽早检测重复键。If you use ALTER TABLE to add a UNIQUE index to a table used in a MERGE table and then add a normal index on the MERGE table, the key order is different for the tables if there was an old, non-UNIQUE key in the table. This is because ALTER TABLE puts UNIQUE indexes before normal indexes to be able to detect duplicate keys as early as possible.

涉及临时表与已定义触发器的非临时表进行连接的 UPDATE 语句可能会导致错误，即使该更新语句仅读取非临时表，在以下情况中也是如此：An UPDATE statement involving a temporary table with a join on a non-temporary table having a trigger defined on it can result in an error, even though the update statement reads only the non-temporary table, in the following cases: