MySQL中的隐式转换（Implicit Conversion）

已于 2024-06-16 10:53:40 修改
阅读量1.7k 收藏 16
点赞数 33
文章标签： mysql 数据库
于 2024-06-13 16:55:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zxrhhm/article/details/139655528
版权

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查询值为数值型，可以转换，但会丢失精度，导致转换后数据丢失精度，查询不是预取的结果

(root@localhost)[db01]> select cast('10004100011000510084' as DECIMAL) as v1,cast('10004100011000510085' as DECIMAL) as v2,cast('10004
    -> ;
+------------+------------+------------+------------+
| v1         | v2         | v3         | v4         |
+------------+------------+------------+------------+
| 9999999999 | 9999999999 | 9999999999 | 9999999999 |
+------------+------------+------------+------------+
1 row in set, 4 warnings (0.01 sec)

(root@localhost)[db01]> select cast('10004100011000510084' as float) as v1,cast('10004100011000510085' as float) as v2,cast('10004100011000510086' as float) as v3,cast('10004100011000510087 ' as float(10)) as v4;
+------------+------------+------------+------------+
| v1         | v2         | v3         | v4         |
+------------+------------+------------+------------+
| 1.00041e19 | 1.00041e19 | 1.00041e19 | 1.00041e19 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)

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等）之间的转换通常是自动的，但可能会导致精度损失或舍入。

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is permitted to decide the value of M. MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10.

在标准SQL中，语法DECIMAL（M）等效于DECIMAL的（M，0）。类似地，语法DECIMAL等效于DECIMAL（M，0），其中允许实现来决定M的值。MySQL支持这两种形式的DECIMAL语法。M的默认值为10。

(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)

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

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

6、隐式转换的问题：

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

7、查看隐式转换：

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

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

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

zxrhhm
关注
mysql隐式转换
lin2535290272的博客
07-28 646
create table testSql(id int(11),name varchar(11),descid varchar(11)); alter table testSql add index(descid) alter table testSql add unique(id) insert into testSql values(1,‘123’,‘1’),(2,‘123a’,‘2’),(3,‘639’,‘3’) select * from testSql; tablename: testSql .
Mysql 隐式类型转换
做一个乐于分享的程序员
08-02 4744
mysql 隐式类型转换规则
MySQL（SQL函数）
最新发布
ma_no_lo的博客
08-17 211
字符函数：字符函数分为大小写处理函数，字符处理函数数字函数：日期函数：转换函数：通用函数：
MySQL 隐式转换必知必会
ActionTech的博客
05-21 458
在生产环境中经常会有一些隐式类型转换导致SQL索引失效，性能极差，进而影响影响集群负载和业务的情况。本文总结了隐式转换常见的场景，在生产中要尽量避免 SQL 隐式转换的出现。
MySQL 隐式类型转换
coco3600的博客
12-18 490
MySQL 隐式类型转换 官网： https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html ...
给大家说明。也议MySQL中隐式转换
wtq630的专栏
09-13 467
1. 环境说明 blog地址：http://blog.csdn.net/hw_libo/article/details/39252427 RHEL 6.4 x86_64 + MySQL 5.6.19 测试表： [sql] view plaincopyprint? MySQL [test]> show create table
Mysql的隐式转换
jzzw的博客
10-23 822
Mysql的隐式转换： 来我们先看看官方文档： When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For exam...
MySQL隐式转换
FirstMrRight的博客
01-01 1063
由于表数据量较少，共3行，所以两者在执行时间上相差无几，但是在数据量较大的表中，扫全表和使用主键索引的性能差距还是相距甚远的。CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。使用explain查询执行计划，扫描行数是1，即使用了主键索引。当两个表的字符集不同时，关联查询时会导致被驱动表无法命中索引。使用explain查询执行计划，扫描行数是3，即全表数据。类型，但是在第一个查询语句中，where条件中1为。官方给的定义些许抽象，下面看例子。最后，祝大家2023新年快乐。
MySQL隐式转化整理
weixin_33994444的博客
04-28 291
MySQL隐式转化整理 前几天在微博上看到一篇文章：价值百万的 MySQL 的隐式类型转换感觉写的很不错，再加上自己之前也对MySQL的隐式转化这边并不是很清楚，所以就顺势整理了一下。希望对大家有所帮助。 当我们对不同类型的值进行比较的时候，为了使得这些数值「可比较」（也可以称为类型的兼容性)，MySQL会做一些隐式转化（Implicit type conversion）。比如下面的例子： mys...
Mysql隐式类型转换原则
12-14
本文将要为您介绍的是Mysql隐式类型转换原则，具体实现方法: 　MySQL 的隐式类型转换原则： 　- 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种...
sql隐式转换_SQL Server中的隐式转换
culuo4781的博客
07-28 1946
sql隐式转换This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of imp...
谈谈MySQL中的隐式转换
12-14
工作过程中会遇到比较多关于隐式转换的案例，隐式转换除了会导致慢查询，还会导致数据不准。本文通过几个生产中遇到的案例来。 基础知识 关于比较运算的原则,MySQL官方文档的描述: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html 如果 判断符号左右两边有一个为NULL,结果就是null，除非使用安全的等值判断 <=>  (none) 05:17:16 >select  null = null; +-------------+ | null = null | +-------------+ |        NULL |
MySQL JSON数据遍历中的数据类型转换：理解隐式转换的影响，提升效率30%
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，在 IT 行业中广泛应用于数据存储、传输和处理。MySQL 作为流行的关系型数据库管理系统，提供了对 JSON 数据的原生支持，使开发者能够轻松
mysql的to char data_数据库中的to char
weixin_36487530的博客
01-18 1542
Oralce中设置小数点位数假如数据库中有一个字段为zdt8，是数值类型的，即使是数字0，也想显示成0.00，则使用to_char函数转换一下to_char(NVL(zdt8,0.00),'fm999990.00') AS v文章暖枫无敌2013-09-05490浏览量oracle中日期格式的转换在oracle中可以对任意类型的日期进行格式转换如下例子：updatea001seta00101...
Scala学习笔记-详细记录学习中遇到的知识点
H_X_P_的博客
07-30 726
Scala-从入门到放弃，从秀发到秃顶，详细记录学习中遇到的知识点
MySQL 的隐式转换
小楠的博客
03-31 1985
当我们对不同类型的值进行比较的时候，为了使得这些数值「可比较」（也可以称为类型的兼容性)，MySQL 会做一些隐式转换（Implicit type conversion）： 若字符串是以数字开头，并且全部都是数字，则转换的数字结果是整个字符串；部分是数字，则转换的数字结果是截止到第一个不是数字的字符为止； 若字符串不是以数字开头，则转换的数字结果是 0。 varchar str = "123dafa" // 转换为数字是 123 SELECT '123dafa' + 1; // 124 varchar
关于MySQL隐式转换
weixin_34233618的博客
09-02 153
一、如果表定义的是varchar字段，传入的是数字，则会发生隐式转换。 1、表DDL2、传int的sql3、传字符串的sql仔细看下表结构，rid的字段类型：而用户传入的是int，这里会有一个隐式转换的问题，隐式转换会导致全表扫描。把输入改成字符串类型，执行计划如下，这样就会很快了。此外，还需要注意的是：数字类型的0001等价于1字符串的0001和1不等价二、如果表定义的...
mysql中的隐式转换
cp's blog
07-22 278
如果你在做一些数据处理或者查询的时候发现很慢，那可能是你创建的字段类型与实际的字段类型不匹配。 在mysql查询中，当查询条件左右两侧类型不匹配的时候会发生隐式转换，可能导致查询无法使用索引。下面分析两种隐式转换的情况 看表结构 phone为 int类型,name为 varchar EXPLAIN select * from user where phone = ‘2’ EXPLAIN select * from user where phone = 2 两种情况都可以用到索引,这次等号右侧是’2’
MySQL隐式类型转换
千月落
04-20 2115
当运算符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。有些转换是隐式发生的。例如，MySQL会根据需要自动将字符串转换为数字，反之亦然。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值