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查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
关于集合与数组转换实现方法

关于集合与数组转换实现方法

《关于集合与数组转换实现方法》：本文主要介绍关于集合与数组转换实现方法,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、Arrays.asList()1.1、方法作用1.2、内部实现1.3、修改元素的影响1.4、注意事项2、list.toArray()2.1、方
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...
MySQL中的锁机制详解之全局锁,表级锁,行级锁

MySQL中的锁机制详解之全局锁,表级锁,行级锁

《MySQL中的锁机制详解之全局锁,表级锁,行级锁》MySQL锁机制通过全局、表级、行级锁控制并发,保障数据一致性与隔离性,全局锁适用于全库备份,表级锁适合读多写少场景,行级锁(InnoDB)实现高并... 目录一、锁机制基础：从并发问题到锁分类1.1 并发访问的三大问题1.2 锁的核心作用1.3 锁粒度分
阅读更多...
MySQL数据库中ENUM的用法是什么详解

MySQL数据库中ENUM的用法是什么详解

《MySQL数据库中ENUM的用法是什么详解》ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用,下面：本文主要介绍MySQL数据库中ENUM的用法是什么的相关资料,文中通过代码... 目录mysql 中 ENUM 的用法一、ENUM 的定义与语法二、ENUM 的特点三、ENUM 的用法1
阅读更多...
MySQL count()聚合函数详解

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式？总结深入剖析一下 mysql 中的 COUNT
阅读更多...
mysql中的服务器架构详解

mysql中的服务器架构详解

《mysql中的服务器架构详解》：本文主要介绍mysql中的服务器架构,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、mysql服务器架构解释3、总结1、背景简单理解一下mysqphpl的服务器架构。2、mysjsql服务器架构解释mysql的架
阅读更多...
MySQL之InnoDB存储引擎中的索引用法及说明

MySQL之InnoDB存储引擎中的索引用法及说明

《MySQL之InnoDB存储引擎中的索引用法及说明》：本文主要介绍MySQL之InnoDB存储引擎中的索引用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐... 目录1、背景2、准备3、正篇【1】存储用户记录的数据页【2】存储目录项记录的数据页【3】聚簇索引【4】二
阅读更多...
mysql中的数据目录用法及说明

mysql中的数据目录用法及说明

《mysql中的数据目录用法及说明》：本文主要介绍mysql中的数据目录用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、版本3、数据目录4、总结1、背景安装mysql之后，在安装目录下会有一个data目录，我们创建的数据库、创建的表、插入的
阅读更多...
MySQL中的InnoDB单表访问过程

MySQL中的InnoDB单表访问过程

《MySQL中的InnoDB单表访问过程》：本文主要介绍MySQL中的InnoDB单表访问过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、环境3、访问类型【1】const【2】ref【3】ref_or_null【4】range【5】index【6】
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2