当运算符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。有些转换是隐式发生的。例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

也可以使用 CAST() 函数显式地将数字转换为字符串。使用 CONCAT() 函数时会隐式进行转换，因为它期望字符串参数。It is also possible to convert a number to a string explicitly using the CAST() function. Conversion occurs implicitly with the CONCAT() function because it expects string arguments.

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'

本节稍后会介绍隐式数值到字符串转换的字符集信息，以及适用于 CREATE TABLE ... SELECT 语句的修改规则。See later in this section for information about the character set of implicit number-to-string conversions, and for modified rules that apply to CREATE TABLE ... SELECT statements.

以下规则描述了比较操作的转换方式：The following rules describe how conversion occurs for comparison operations:

有关将值从一种时间类型转换为另一种时间类型的信息，请参见 第 13.2.8 节，“日期和时间类型之间的转换”。For information about conversion of values from one temporal type to another, see Section 13.2.8, “Conversion Between Date and Time Types”.

JSON 值的比较发生在两个层次。第一个层次的比较基于被比较值的 JSON 类型。如果类型不同，则比较结果仅由哪种类型优先级更高决定。如果两个值具有相同的 JSON 类型，则使用特定类型的规则进行第二层比较。对于 JSON 值和非 JSON 值的比较，非 JSON 值将被转换为 JSON，并作为 JSON 值进行比较。有关详细信息，请参见 JSON 值的比较和排序。Comparison of JSON values takes place at two levels. The first level of comparison is based on the JSON types of the compared values. If the types differ, the comparison result is determined solely by which type has higher precedence. If the two values have the same JSON type, a second level of comparison occurs using type-specific rules. For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON and the values compared as JSON values. For details, see Comparison and Ordering of JSON Values.

以下示例说明了将字符串转换为数字以进行比较操作：The following examples illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

在将字符串列与数字进行比较时，MySQL 不能使用该列上的索引来快速查找值。如果 str_col 是一个已索引的字符串列，则在执行以下语句进行查找时，无法使用索引：For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

其原因是许多不同的字符串可能会转换为值 1，例如 '1'、' 1' 或 '1a'。The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

在将字符串列与整数 0 比较时，可能会出现另一个问题。考虑如下创建和填充的表 t1：Another issue can arise when comparing a string column with integer 0. Consider table t1 created and populated as shown here:

mysql> CREATE TABLE t1 (
    ->   c1 INT NOT NULL AUTO_INCREMENT,
    ->   c2 INT DEFAULT NULL,
    ->   c3 VARCHAR(25) DEFAULT NULL,
    ->   PRIMARY KEY (c1)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES ROW(1, 52, 'grape'), ROW(2, 139, 'apple'), 
    ->                       ROW(3, 37, 'peach'), ROW(4, 221, 'watermelon'),
    ->                       ROW(5, 83, 'pear');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

当从此表中选择并将 c3（它是一个 VARCHAR 列）与整数 0 比较时，观察结果：Observe the result when selecting from this table and comparing c3, which is a VARCHAR column, with integer 0:

mysql> SELECT * FROM t1 WHERE c3 = 0;
+----+------+------------+
| c1 | c2   | c3         |
+----+------+------------+
|  1 |   52 | grape      |
|  2 |  139 | apple      |
|  3 |   37 | peach      |
|  4 |  221 | watermelon |
|  5 |   83 | pear       |
+----+------+------------+
5 rows in set, 5 warnings (0.00 sec)

即使使用严格 SQL 模式，这种情况也会发生。为了防止这种情况发生，请像这里那样对值加引号：This occurs even when using strict SQL mode. To prevent this from happening, quote the value, as shown here:

mysql> SELECT * FROM t1 WHERE c3 = '0';
Empty set (0.00 sec)

当 SELECT 是数据定义语句的一部分，例如 CREATE TABLE ... SELECT 时，这种情况 不会 发生；在严格模式下，由于无效比较，该语句会失败：This does not occur when SELECT is part of a data definition statement such as CREATE TABLE ... SELECT; in strict mode, the statement fails due to the invalid comparison:

mysql> CREATE TABLE t2 SELECT * FROM t1 WHERE c3 = 0;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'grape'

当引用 0 时，语句成功执行，但是创建的表不包含任何行，因为没有匹配 '0' 的记录，如下所示：When the 0 is quoted, the statement succeeds, but the table created contains no rows because there were none matching '0', as shown here:

mysql> CREATE TABLE t2 SELECT * FROM t1 WHERE c3 = '0';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

这是一个已知问题，这是因为在处理 SELECT 时未应用严格模式。也可以参见 严格 SQL 模式。This is a known issue, which is due to the fact that strict mode is not applied when processing SELECT. See also Strict SQL Mode.

浮点数与大整数值之间的比较是近似的，因为整数在比较之前被转换为双精度浮点数，而双精度浮点数无法精确表示所有 64 位整数。例如，整数值 2⁵³ + 1 无法表示为浮点数，并且在浮点比较之前，会根据平台被四舍五入为 2⁵³ 或 2⁵³ + 2。Comparisons between floating-point numbers and large integer values are approximate because the integer is converted to double-precision floating point before comparison, which is not capable of representing all 64-bit integers exactly. For example, the integer value 2⁵³ + 1 is not representable as a float, and is rounded to 2⁵³ or 2⁵³ + 2 before a float comparison, depending on the platform.

举例说明，以下比较中只有第一个比较的是相等的值，但两个比较都返回真（1）：To illustrate, only the first of the following comparisons compares equal values, but both comparisons return true (1):

mysql> SELECT '9223372036854775807' = 9223372036854775807;
        -> 1
mysql> SELECT '9223372036854775807' = 9223372036854775806;
        -> 1

当从字符串到浮点数以及从整数到浮点数的转换发生时，它们不一定以相同的方式进行。整数可能由 CPU 转换为浮点数，而字符串则会在涉及浮点数乘法的操作中逐位转换。此外，结果还可能受到计算机架构、编译器版本或优化级别等因素的影响。避免此类问题的一种方法是使用 CAST()，这样值就不会被隐式转换为浮点数：When conversions from string to floating-point and from integer to floating-point occur, they do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications. Also, results can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use CAST() so that a value is not converted implicitly to a float-point number:

mysql> SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806;
        -> 0

有关浮点比较的更多信息，请参见 B.3.4.8 节，“浮点值的问题”。For more information about floating-point comparisons, see Section B.3.4.8, “Problems with Floating-Point Values”.

服务器包括 dtoa，这是一个转换库，为字符串或 DECIMAL 值与近似值 (FLOAT/DOUBLE) 数字之间的改进转换提供基础：The server includes dtoa, a conversion library that provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers:

由于该库生成的转换结果在某些情况下与非 dtoa 的结果不同，可能会导致依赖以前结果的应用程序出现不兼容。例如，依赖之前转换中特定精确结果的应用程序可能需要调整以适应额外的精度。Because the conversions produced by this library differ in some cases from non-dtoa results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.

dtoa 库提供具有以下特性的转换。D 表示具有 DECIMAL 或字符串表示的值，而 F 表示以本地二进制（IEEE）格式表示的浮点数。The dtoa library provides conversions with the following properties. D represents a value with a DECIMAL or string representation, and F represents a floating-point number in native binary (IEEE) format.

这些属性意味着 F -> D -> F 的转换是无损的，除非 F 是 -inf、+inf 或 NaN。后者的值不被支持，因为 SQL 标准将它们定义为 FLOAT 或 DOUBLE 的无效值。These properties imply that F -> D -> F conversions are lossless unless F is -inf, +inf, or NaN. The latter values are not supported because the SQL standard defines them as invalid values for FLOAT or DOUBLE.

对于 D -> F -> D 的转换，无损的充分条件是 D 使用不超过 15 位的精度，并且不是非规格化值、-inf、+inf 或 NaN。在某些情况下，即使 D 具有超过 15 位的精度，转换也可能是无损的，但这并非总是如此。For D -> F -> D conversions, a sufficient condition for losslessness is that D uses 15 or fewer digits of precision, is not a denormal value, -inf, +inf, or NaN. In some cases, the conversion is lossless even if D has more than 15 digits of precision, but this is not always the case.

将数值或时间值隐式转换为字符串会生成具有由 character_set_connection 和 collation_connection 系统变量确定的字符集和排序规则的值。（这些变量通常通过 SET NAMES 设置。有关连接字符集的信息，请参见 第 12.4 节，“连接字符集和排序规则”。）Implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables. (These variables commonly are set with SET NAMES. For information about connection character sets, see Section 12.4, “Connection Character Sets and Collations”.)

这意味着这样的转换会产生一个字符（非二进制）字符串（一个 CHAR、VARCHAR 或 LONGTEXT 值），除非连接字符集设置为 binary。在这种情况下，转换结果是一个二进制字符串（一个 BINARY、VARBINARY 或 LONGBLOB 值）。This means that such a conversion results in a character (nonbinary) string (a CHAR, VARCHAR, or LONGTEXT value), except in the case that the connection character set is set to binary. In that case, the conversion result is a binary string (a BINARY, VARBINARY, or LONGBLOB value).

对于整数表达式，上述关于表达式求值的说明在表达式赋值方面的适用略有不同；例如，在这样的语句中：For integer expressions, the preceding remarks about expression evaluation apply somewhat differently for expression assignment; for example, in a statement such as this:

CREATE TABLE t SELECT integer_expr;

在这种情况下，由表达式生成的列中的表的类型为 INT 或 BIGINT，取决于整数表达式的长度。如果该表达式的最大长度不适合 INT，则使用 BIGINT。长度取自 SELECT 结果集元数据的 max_length 值（参见 C API 基本数据结构）。这意味着你可以通过使用足够长的表达式来强制使用 BIGINT 而不是 INT：In this case, the table in the column resulting from the expression has type INT or BIGINT depending on the length of the integer expression. If the maximum length of the expression does not fit in an INT, BIGINT is used instead. The length is taken from the max_length value of the SELECT result set metadata (see C API Basic Data Structures). This means that you can force a BIGINT rather than INT by use of a sufficiently long expression:

CREATE TABLE t SELECT 000000000000000000000;