MySQL中的隐式转换（Implicit Conversion）

本文主要是介绍MySQL中的隐式转换（Implicit Conversion），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL中的隐式转换（Implicit Conversion）指的是在SQL语句的执行过程中，数据库管理系统（DBMS）自动进行的数据类型转换。这种转换通常发生在数据类型不匹配但需要进行比较、计算或赋值等操作时。

以下是一些关于MySQL隐式转换的常见场景和注意事项：

1、字符串和数字之间的转换：

当字符串和数字进行算术运算时，字符串会被尝试转换为数字（如果可能）。例如，‘110’ + 10086会被解释为 110 + 10086。但是，如果字符串不能被解析为一个有效的数字（例如 ‘cba’ + 886），结果将为 886。
当字符串和数字进行比较时，也会发生类似的转换。

(test@192.168.80.230)[(none)]> select '110' + 10086;
+---------------+
| '110' + 10086 |
+---------------+
|         10196 |
+---------------+
1 row in set (0.00 sec)(test@192.168.80.230)[(none)]> select 'cba' + 886 as v1,'abc' + 886 as v2;
+-----+-----+
| v1  | v2  |
+-----+-----+
| 886 | 886 |
+-----+-----+
1 row in set, 2 warnings (0.00 sec)(test@192.168.80.230)[(none)]> select cast('1000000860000094443' as DECIMAL) as v1,cast('1000000860000094443' as DECIMAL(64,0)) as v2;
+------------+---------------------+
| v1         | v2                  |
+------------+---------------------+
| 9999999999 | 1000000860000094443 |
+------------+---------------------+
1 row in set, 1 warning (0.00 sec)

1.1、问题复现

创建测试表，并插入测试数据

create table T_Implicit_Conversion
(id int primary key,c_str1 varchar(64),c_str2 varchar(64),c_num DECIMAL(64,0) );insert into T_Implicit_Conversion
select 1,'10004100011000510085','cba',10004100011000510085
union all 
select 2,'10004100011000510084','nba',10004100011000510084;
union all 
select 3,'10004100011000510086','123',10004100011000510086
union all 
select 4,'10004100011000510087','aaa',10004100011000510087;
commit;
select * from T_Implicit_Conversion;(root@localhost)[db01]> select * from T_Implicit_Conversion;
+----+----------------------+--------+----------------------+
| id | c_str1               | c_str2 | c_num                |
+----+----------------------+--------+----------------------+
|  1 | 10004100011000510085 | cba    | 10004100011000510085 |
|  2 | 10004100011000510084 | nba    | 10004100011000510084 |
|  3 | 10004100011000510086 | 123    | 10004100011000510086 |
|  4 | 10004100011000510087 | aaa    | 10004100011000510087 |
+----+----------------------+------+------------------------+-- where条件字段类型和查询值类型一致的情况下(root@localhost)[db01]> select * from  T_Implicit_Conversion where c_str1 = '10004100011000510086';
+----+----------------------+--------+----------------------+
| id | c_str1               | c_str2 | c_num                |
+----+----------------------+------+------------------------+
|  3 | 10004100011000510086 | 123    | 10004100011000510086 |
+----+----------------------+--------+----------------------+
1 row in set (0.00 sec)

条件字段类型和查询值类型不一致的情况下，查询条件是 where str1 = 10004100011000510086

(root@localhost)[db01]> select * from T_Implicit_Conversion where c_str1=10004100011000510084;
+----+----------------------+--------+----------------------+
| id | c_str1               | c_str2 | c_num                |
+----+----------------------+--------+----------------------+
|  1 | 10004100011000510085 | cba    | 10004100011000510085 |
|  2 | 10004100011000510084 | nba    | 10004100011000510084 |
|  3 | 10004100011000510086 | 123    | 10004100011000510086 |
|  4 | 10004100011000510087 | aaa    | 10004100011000510087 |
+----+----------------------+------+------------------------+
4 rows in set (0.00 sec)

执行结果如上 10004100011000510084、5、7 的数据也跟着查出来了

1.2、分析原因

explain select * from  T_Implicit_Conversion where str1 = 10004100011000510086;   -- 查询结果不符合预期结果，不会产生warnings  
show warnings;

查看官方文档，可以得到字符型与数值型比较，最终都转化为浮点型来比较， 表字段为字符型，where查询值为数值型，可以转换，但会丢失精度。

1.3、表字段为字符型，内容较短时，可以转换，但不存在丢失精度的情况

(root@localhost)[db01]> create table T_Implicit_Conversion_2-> (id int primary key,->  c_str1 varchar(64),->  c_str2 varchar(64),->  c_num DECIMAL(18,2) ->  );
Query OK, 0 rows affected (0.03 sec)(root@localhost)[db01]> insert into T_Implicit_Conversion_2-> select 1,'100010086','cba',1000400510084-> union all-> select 2,'100010087','abc',1000400510085-> union all-> select 3,'100010088','nba',1000400510086-> union all -> select 4,'100010089','fba',1000400510087;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0(root@localhost)[db01]> commit;
Query OK, 0 rows affected (0.00 sec)(root@localhost)[db01]> select * from T_Implicit_Conversion_2;
+----+-----------+--------+------------------+
| id | c_str1    | c_str2 | c_num            |
+----+-----------+--------+------------------+
|  1 | 100010086 | cba    | 1000400510084.00 |
|  2 | 100010087 | abc    | 1000400510085.00 |
|  3 | 100010088 | nba    | 1000400510086.00 |
|  4 | 100010089 | fba    | 1000400510087.00 |
+----+-----------+--------+------------------+
4 rows in set (0.00 sec)(root@localhost)[db01]> select * from T_Implicit_Conversion_2 where c_str1=100010086;
+----+-----------+--------+------------------+
| id | c_str1    | c_str2 | c_num            |
+----+-----------+--------+------------------+
|  1 | 100010086 | cba    | 1000400510084.00 |
+----+-----------+--------+------------------+
1 row in set (0.00 sec)(root@localhost)[db01]> select * from T_Implicit_Conversion_2 where c_str1='100010086';
+----+-----------+--------+------------------+
| id | c_str1    | c_str2 | c_num            |
+----+-----------+--------+------------------+
|  1 | 100010086 | cba    | 1000400510084.00 |
+----+-----------+--------+------------------+
1 row in set (0.00 sec)

官方文档地址
https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

1.4、表字段为数值型，where查询值为字符串

-- 表字段为数值型，where查询值为字符串 ，warnings Truncated incorrect DOUBLE value
(root@localhost)[db01]>  select * from T_Implicit_Conversion where id='2f';
+----+-----------+--------+------------------+
| id | c_str1    | c_str2 | c_num            |
+----+-----------+--------+------------------+
|  2 | 100010087 | abc    | 1000400510085.00 |
+----+-----------+--------+------------------+
1 row in set, 1 warning (0.00 sec)(root@localhost)[db01]> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2f' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)-- 表字段为数值型，where查询值为数值型 ，warnings 无内容
(root@localhost)[db01]> select * from T_Implicit_Conversion where id=2;
+----+-----------+--------+------------------+
| id | c_str1    | c_str2 | c_num            |
+----+-----------+--------+------------------+
|  2 | 100010087 | abc    | 1000400510085.00 |
+----+-----------+--------+------------------+
1 row in set (0.00 sec)(root@localhost)[db01]> show warnings;
Empty set (0.00 sec)

2、日期/时间类型和其他类型的转换：

当日期/时间类型与字符串或数字进行比较或计算时，可能会进行隐式转换。但是，这种转换的结果可能并不总是符合预期，因此最好显式地进行转换或使用适当的函数。

3、NULL值的处理：

在MySQL中，NULL 是一个特殊的值，表示“无”或“未知”。当 NULL 参与算术运算时，结果通常为 NULL。当 NULL 与其他值进行比较时，结果也通常是 NULL（除了 IS NULL 和 IS NOT NULL 这样的比较）。

(root@localhost)[db01]> select null + 886;
+------------+
| null + 886 |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)(root@localhost)[db01]> select null + 'nba';
+--------------+
| null + 'nba' |
+--------------+
|         NULL |
+--------------+
1 row in set, 1 warning (0.00 sec)

4、整数和小数之间的转换：

整数和小数（即DECIMAL、FLOAT、DOUBLE等）之间的转换通常是自动的，但可能会导致精度损失或舍入。

5、二进制和字符类型之间的转换：

二进制类型（如BINARY、VARBINARY）和字符类型（如CHAR、VARCHAR）在某些上下文中可能会进行转换。但是，这种转换通常涉及到编码和字符集的问题，因此需要格外小心。

6、隐式转换的问题：

虽然隐式转换在某些情况下很方便，但它们也可能导致不可预测的结果和错误。为了避免这些问题，最好明确知道哪些类型的转换正在发生，并在必要时使用显式的类型转换函数（如 CAST() 或 CONVERT()）。
隐式转换还可能导致性能问题，因为数据库需要花费额外的资源来执行这些转换。

7、查看隐式转换：

要查看MySQL是否对某个表达式进行了隐式转换，可以使用 EXPLAIN 语句（尽管这主要用于查看查询的执行计划，而不是直接的隐式转换）。但是，更好的方法是仔细检查SQL语句中的数据类型和操作符，并了解MySQL的隐式转换规则。

最后，要注意，虽然这里讨论了MySQL的隐式转换，但其他数据库系统也可能有类似的机制和行为。因此，在编写跨数据库的SQL代码时，需要格外小心数据类型和转换的问题。

出现这种因隐式转换产生的错误，是完全可以避免的低级错误
第一个是约束开发人员绝对不允许隐式转换发生
第二个是使用当下一些审核工具，产生隐式转换的语句应该审计提早避免上线生产环境。

这篇关于MySQL中的隐式转换（Implicit Conversion）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---