12.3 表达式计算中的类型转换

5.7

5.7

当运算符与不同类型的操作数一起使用时，为了兼容操作数将进行类型转换。有些转换是隐式发生的。例如，MySQL 根据需要自动将字符串转换为数字，反之亦然。

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

也可以使用 CAST() 函数显式地将数字转换为字符串。使用 CONCAT() 函数时转换是隐式发生的，因为函数期望接收的是字符串参数。

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 语句的修改规则，请参见本节后面的内容。

下面的规则描述了比较操作中类型是如何转换的:

• 如果一个或者两个参数是 NULL，则比较的结果就是 NULL，除非使用了安全的 NULL <=> 全等比较符。对于 NULL <=> NULL，结果是 true ，不需要进行转换。

• 如果比较操作符的两个参数都是字符串，将作为字符串进行比较。

• 如果两个参数都是整数，将作为整数进行比较。

• 如果不是和数字进行比较，十六进制的值将会被视为二进制字符串。

• 如果其中一个参数是 TIMESTAMP 或者 DATETIME 列，另一个参数是一个常量，则在执行比较前先将常量转换为时间戳。这样做是为了对 ODBC 更加友好。对于 IN() 中的参数则不会这么做。为了安全起见，建议在做比较时使用完整的日期时间，日期或者时间字符串。例如，为了达到最佳效果，在 BETWEEN 操作符中使用日期或者时间值时，使用 CAST() 函数显式地将值转换为所需的数据类型。

  来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回一个要与 DATETIME 值进行比较的整数值，将作为两个整数进行比较。整数不会转换为时间值。如果要将操作数作为 DATETIME 值进行比较，使用 CAST() 函数显式地将子查询的值转换为 DATETIME 类型的值。

• 如果其中一个参数是十进制数，则比较依赖于另一个参数。如果另一个参数也是十进制数或者整数，将作为十进制数进行比较。如果另一个参数是浮点数，将作为浮点数进行比较。

• 除此之外的所有情况，参数都会作为浮点数（双精度）进行比较。例如，字符串和数字之间的比较，都是作为浮点数进行比较的。

想了解从时间类型的值转换为其他类型的更多信息，请参考 章节 11.2.9，「日期和时间类型之间的转换」。

JSON 值的比较在两个层级上进行。第一级比较基于比较值的 JSON 类型。如果类型不同，则比较结果仅由具有较高优先级的类型确定。如果两个值具有相同的 JSON 类型，则使用特定于类型的规则进行第二级比较。为了比较 JSON 和非 JSON 值，将非 JSON 值转换为 JSON，并将值作为 JSON 值进行比较。想了解更多细节，请参考 JSON 值的比较与排序。

下面的示例说明了比较操作中字符串到数字的转换:

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

对于字符串列与数字的比较，MySQL 不能使用列上的索引来快速查找值。如果 str_ col 是索引字符串列，则在以下语句执行查找时不能使用索引:

SELECT * FROM tbl_name WHERE str_col=1;

原因在于多种不同的字符串都会转换为数字 1，例如 '1', ' 1' 或者 '1a'。

将字符串列与整数 0 进行比较时可能会出现另一个问题。考虑如下所示的 t1 表的创建和填充：

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

当从这个表中查询，并且将 VARCHAR 类型的 c3 列与整数 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 模式这种情况也会出现。为了防止这种情况发生，请将值放在引号中，如下所示：

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

当 SELECT 作为数据定义语句（例如 CREATE TABLE ... SELECT）的一部分时，这种情况不会出现；在严格模式下，由于比较无效，数据定义语句将会执行失败。

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

当 0 被引号包裹时，语句会成功执行，但是表中不会有记录，因为没有匹配到 '0' 的记录，如下所示：

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 模式。

浮点数和大型整数之间的比较是近似的比较，因为在比较之前，整数会转换为双精度浮点数，这并不能将所有的 64 位整数都精确地表示出来。例如， 2^53^ + 1 这个整数就不能用浮点数表示，在进行浮点数比较之前，它被四舍五入为 2^53 或者 2^53 + 2，具体结果取决于平台。

为了说明这一点，下面只有第一个比较比较了相同的值，但实际上两个比较都返回了 true (1)：

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

当出现从字符串到浮点数和整数到浮点数的转换时，并不一定按照相同的方式出现。整数会通过 CPU 转换为浮点数，而字符串是在涉及浮点乘法的操作中逐位转换的。当然，结果可能还会受到电脑的体系结构，编译器的版本或者优化级别等因素的影响。避免这类问题的一种方法是使用 CAST() 函数，这样数值就不会隐式地转换为浮点数：

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

想了解更多关于浮点数转换的内容，请参阅 章节 B.3.4.8, 「浮点数值的问题」。

如果服务器安装了 dtoa 这个转换库，可以提升字符串或者 DECIMAL 值和近似值（FLOAT/DOUBLE）数字之间的转换效率：

• 跨平台一致性的转换结果，这消除了例如 Unix 与 Windows 之间转换的差异。

• 在以前的结果不能提供足够精确度的情况下，现在可以精确地表示数值，例如接近 IEEE 限制的数值。

• 尽可能精确地将数字转换为字符串格式。dtoa 库的精度总是与标准 C 库函数相同甚至更好。

由于该库生成的转换在某些情况下与非 dtoa 结果不同，因此在依赖于以前结果的应用程序中存在不兼容的可能性。例如，如果应用程序依赖于以前转换的特定精确结果，可能需要进行调整以适应额外的精度。

dtoa 库提供了具有以下属性的转换。D 代表 DECIMAL 类型的值或字符串表达式，F 代表本机二进制（IEEE）格式的浮点数。

• F -> D 转换是以尽可能高的精度完成的，返回 D 作为最短的字符串，当读回时产生 F ，并按 IEEE 指定的原生二进制格式四舍五入为最接近的值。

• D -> F 转换是这样完成的: F 是与输入的十进制字符串 D 最接近的本机二进制数。

这些属性意味着 F -> D -> F 转换是无损的，除非 F 是 -inf, +inf 或者 NaN。后面的值是不支持的，因为 SQL 标准中定义了这些值对于 FLOAT 或者 DOUBLE 类型来说都是无效的。

对于 D -> F -> D 转换，无损的一个充分条件是 D 使用 15 位或更少精度的数位，而不是使用非正规值，比如 -inf, +inf 或者 NaN。不过，在某些情况下，即使 D 的精度超过 15 位，转换也可以是无损的，但这并不常见。

数字或者时间值到字符串的隐式转换，所生成的值的字符集和排序规则，由系统变量 character_set_connection 和 collation_connection 共同决定。（这些变量一般通过 SET NAMES 来设定。有关连接字符集的信息，请参阅 章节 10.4, 「连接字符集和排序规则」）

这意味着这种转换会生成一个字符（非二进制）字符串（一个 CHAR, VARCHAR 或者 LONGTEXT 类型的值），除非连接字符集被设置为 binary。在这种情况下，转换结果是一个二进制字符串（BINARY, VARBINARY 或者 LONGBLOB 类型的值）。

对于整数表达式，前面讨论的关于 计算 再到 赋值 的表达式用法将有所不同; 例如，在以下语句中:

CREATE TABLE t SELECT integer_expr;

在这种情况下，由表达式生成的表中的列的类型为 INT 或 BIGINT，具体取决于整数表达式的长度。如果表达式的最大长度不适合 INT，则改为使用 BIGINT。长度取自 SELECT 结果集元数据的 max_length 值（参考 C API 基本数据结构）。这意味着可以通过使用足够长的表达式来强制使用 BIGINT 而不是 INT：

CREATE TABLE t SELECT 000000000000000000000;