MySQL中的隐式转换（Implicit Conversion）

2024-06-14 15:44
文章标签 mysql 转换 隐式 database conversion implicit

本文主要是介绍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）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1060831。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
Java轻松实现PDF转换为PDF/A的示例代码

Java轻松实现PDF转换为PDF/A的示例代码

《Java轻松实现PDF转换为PDF/A的示例代码》本文将深入探讨Java环境下,如何利用专业工具将PDF转换为PDF/A格式,为数字文档的永续保存提供可靠方案,文中的示例代码讲解详细,感兴趣的小伙伴... 目录为什么需要将PDF转换为PDF/A使用Spire.PDF for Java进行转换前的准备通过
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2