MySQL中存在的隐式数据类型转换以及可能带来的问题

By adminmysql360On 2023年7月8日2023年7月8日

今天我们来聊聊MySQL中存在的隐式数据类型转换以及可能带来的问题。

在MySQL中，当两个不同类型的数据进行运算时，为了使它们能够兼容，MySQL可能会执行隐式的数据类型转换。例如，MySQL会自动将字符串转换为数字，反之亦然。

比如，通过在查询语句中使用加号，可以将数字字符串转换为数值类型：

mysql> SELECT 1+'1';        -> 2

通过CONCAT()函数可以将数字显式转换为字符串类型：

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

同时，我们也可以利用CONCAT()函数完成字符串的拼接，但要注意它只能接收字符串类型的参数。

在比较运算中，MySQL遵循以下规则进行类型转换：

如果任意一个参数为NULL，比较的结果为NULL，但<=>（相等比较运算符）除外。NULL <=> NULL 的运算结果为true，并且不需要进行类型转换。
如果两个参数都是字符串，则执行字符串比较。
如果两个参数都是整数，则执行整数比较。
如果不是和数字进行比较，十六进制数值将被看作二进制字符串。
如果一个参数是TIMESTAMP或者DATETIME字段，另一个参数是常量，该常量将在比较之前转换为时间戳类型。这个规则是为了更好地支持ODBC规范。IN()运算符中的参数不会执行这一转换。为了避免问题，执行比较运算时应该使用完整的日期时间、日期或者时间字符串，例如，在使用BETWEEN运算符判断日期或者时间数据时，可以使用CAST()函数将数据的类型显式转换成相应的类型。
返回单行结果的子查询不会被当作常量。例如，当一个返回整数的子查询和DATETIME数据进行比较时，DATETIME将会被转换为整数类型，而不会将子查询的结果转换为时间类型。如果想要执行日期时间比较，可以使用CAST()函数显式将子查询的结果转换为DATETIME类型。
如果一个参数为精确数字类型（decimal），比较的方法取决于另一个参数的类型。如果另一个参数是精确数字或者整数类型，使用精确数字比较；如果另一个参数是浮点数类型，使用浮点数比较。
其他情况下，使用浮点数比较。例如，字符串和精确数字的比较使用浮点数比较方法。

关于时间类型之间的转换规则，请参考官方文档。

下面的示例演示了将字符串转换为数字的比较操作：

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

需要注意的是，如果将字符串类型的字段和数字进行比较，MySQL无法使用该字段上的索引快速查找数据。这是因为很多不同的字符串都可以转换为数字1，例如’1’、’ 1’或者’1a’。

此外，浮点数和INTEGER类型的超大数值之间的比较是近似比较，因为整数在比较之前需要转换为双精度浮点数，双精度浮点数无法精确地表示所有的64位整数。举例来说，整数253 + 1无法使用浮点数进行表示，只能近似为253或者253 + 2。

以下示例展示了两个比较运算结果都返回true（1），但只有第一个比较运算中的两个值相等：

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

值得一提的是，字符串转换为浮点数与整数转换为浮点数的方式可能不同。整数可能使用CPU转换为浮点数，而字符串可能使用浮点数乘法进行逐位转换。另外，转换结果可能受到计算机的架构、编译器版本或者优化级别等因素的影响。避免这种问题的方法之一就是使用CAST()函数，这样数据就不会被隐式转换为浮点数。

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

关于浮点数比较的更多信息，请参考官方文档。

MySQL服务器提供了一个转换库dtoa，可以支持字符串或者DECIMAL数据和近似数字（FLOAT/DOUBLE）之间的基本转换功能。该转换库具有以下特点：

跨平台的一致性转换结果，可以消除Unix和Windows之间的差异。
可以精确表示之前无法提供足够精度的数据，例如接近IEEE限制的数据。
以尽可能高的精度将数字转换成字符串格式。dtoa的精度总是等于或者高于标准C代码库函数。
数字或者时间类型到字符串的隐式转换结果的字符集和排序规则取决于character_set_connection和collation_connection系统变量。这些变量通常使用SET NAMES进行设置。关于连接字符集的信息，请参考官方文档。这意味着这种转换的结果是一个非二进制的字符串（CHAR、VARCHAR或者LONGTEXT），除非连接字符集被设置为binary。此时，转换结果是一个二进制字符串（BINARY、VARBINARY或者LONGBLOB）。

对于整数类型的表达式，与前文所述的表达式求值和表达式赋值有所不同。例如，在创建表时使用SELECT语句：

CREATE TABLE t SELECT integer_expr;

这种情况下，表t的字段类型取决于整数表达式的长度，可能是INT或者BIGINT。如果表达式的最大长度超过了INT，将使用BIGINT类型。这意味着我们可以通过一个足够长的表达式创建BIGINT类型的字段：

CREATE TABLE t SELECT 000000000000000000000 AS col;
DESC t;

当涉及到JSON数据的比较时，会有两种情况。第一层次的比较基于被比较数据的JSON类型，如果两个类型不同，比较的结果取决于具有更高优先级的类型；如果两个数据的JSON类型相同，使用具体的类型规则进行第二层次的比较。对于JSON和非JSON数据的比较，首先将非JSON数据转换为JSON类型，然后进行比较。关于JSON比较的详细信息，请参考官方文档。

浏览量: 11

MySQL中存在的隐式数据类型转换以及可能带来的问题

相关文章：

发表回复 取消回复

发表回复取消回复