谈谈MySQL中的隐式转换

工作过程中会遇到比较多关于隐式转换的案例,隐式转换除了会导致慢查询,还会导致数据不准。本文通过几个生产中遇到的案例来。

基础知识

关于比较运算的原则,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 |
+-------------+
1 row in set (0.00 sec)

(none) 05:34:59 >select  null <=> null;
+---------------+
| null <=> null |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

(none) 05:35:51 >select  null != 1;
+-----------+
| null != 1 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

如何判断左右两边都是相同类型的,比如都是字符串,则以字符串进行对比。如果是数字,则以数字进行比较。

注意 对于比较常见的 字符串与数字类型的比较的情况,如果字符串字段是索引字段,那么MySQL 无法通过索引进行查找数据,比如以下例子:

(none) 05:39:42 >select  1='1';
+-------+
| 1='1' |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

(none) 05:39:44 >select  1='1A';
+--------+
| 1='1A' |
+--------+
|      1 |
+--------+
1 row in set, 1 warning (0.00 sec)

(none) 05:39:47 >select  1='1 '; ##1后有空格
+--------+
| 1='1 ' |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

MySQL 认为数字1 与'1','1_','1A' 相等,故无法通过索引二分查找准确定位到具体的值。

Hexadecimal(十六进制)以二进制字符串的方式进行比较。

如何判断符号左边是 timestamp 或者datetime类型的,右边是常量,在比较之前,常量会被转换为时间类型。

隐式转换

字段类型不一样

In all other cases, the arguments are compared as floating-point (real) numbers.

除了以上的其他类型的比较,系统将字段和参数转换为浮点型进行比较。使用浮点数(或转换为浮点数的值)的比较是近似的,因为这样的数字是不精确的。看下面2个例子

>select '190325171202362933' = 190325171202362931;
+-------------------------------------------+
| '190325171202362933' = 190325171202362931 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

>select '190325171202362936' = 190325171202362931;
+-------------------------------------------+
| '190325171202362936' = 190325171202362931 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

直观上不相等的值,做等值判断之后竟然返回为1。这样带来2个问题不能利用索引且结果数据不准

>select '190325171202362931'+0.0;
+--------------------------+
| '190325171202362931'+0.0 |
+--------------------------+
|    1.9032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)

>select '190325171202362936'+0.0;
+--------------------------+
| '190325171202362936'+0.0 |
+--------------------------+
|    1.9032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)

将上面的值转换为浮点数,都是 1.9032517120236294e17,所以判断相等时为真,返回True。

in 参数包含多个类型

具体的案例参考之前的一篇文章MySQL优化案例一则 ,where 条件 in 集合里面的数据类型不一样,执行计划未利用到索引

淘宝MySQL月报(http://mysql.taobao.org/monthly/2017/12/06/ )里面有一篇正好和这个一样的案例,推荐给大家 简单说,就是在IN的入口有一个判断, 如果in中的字段类型不兼容, 则认为不可使用索引.

而这个arg_types_compatible 的赋值逻辑是:

if (type_cnt == 1)
    arg_types_compatible = TRUE; 

也就是说,当IN列表中出现超过一个字段类型时, 就认为类型不兼容,从而不能利用索引。

字符集类型不一致

环境准备:

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

insert into t1(c1,c2) values('a','a'),('b','b'),('c','c'),
('d','d'),('e','e');
insert into t2(c1,c2) values('a','a'),('b','b'),('c','c'),
('d','d'),('e','e');

测试结果

小结

希望通过以上案例,基础知识介绍,开发同学能少走弯路,在开发编写sql的阶段一定要明确字段的类型,尤其是看起来像数字类型的id,xxxid,xxxno 这类字段,实际上可能是字符类型。

以上就是谈谈MySQL中的隐式转换的详细内容,更多关于MySQL 隐式转换的资料请关注我们其它相关文章!

(0)

相关推荐

  • 浅析MySQL显式类型转换

    CAST 函数 在之前的文章中,我们提到过CAST函数,用于显示进行类型转换.在避免隐式类型转换时,好处多多.其实这里面还有很多细节,是需要梳理下的. 首先来看看下面这个转换: mysql> SELECT CAST('2017-12-14' AS DATE); +----------------------------+ | CAST('2017-12-14' AS DATE) | +----------------------------+ | 2017-12-14 | +----------

  • MySQL隐式类型的转换陷阱和规则

    前言 相信大家都知道隐式类型转换有无法命中索引的风险,在高并发.大数据量的情况下,命不中索引带来的后果非常严重.将数据库拖死,继而整个系统崩溃,对于大规模系统损失惨重.所以下面通过本文来好好学习下MySQL隐式类型的转换陷阱和规则. 1. 隐式类型转换实例 今天生产库上突然出现MySQL线程数告警,IOPS很高,实例会话里面出现许多类似下面的sql:(修改了相关字段和值) SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=

  • 解析MySQL隐式转换问题

    一.问题描述 root@mysqldb 22:12: [xucl]> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.0

  • 使用python将mysql数据库的数据转换为json数据的方法

    由于产品运营部需要采用第三方个推平台,来推送消息.如果手动一个个键入字段和字段值,容易出错,且非常繁琐,需要将mysql的数据转换为json数据,直接复制即可. 本文将涉及到如何使用Python访问Mysql数据库及读取获取数据(前提需要安装MySQLdb第三方库哦),以及如何将数据转换为json数据,最后保存成文件输出. 代码如下:注释比较详细了. # coding=utf-8 ''' Created on 2016-10-26 @author: Jennifer Project:读取mysq

  • mysql 转换NULL数据方法(必看)

    使用mysql查询数据库,当执行left join时,有些关联的字段内容是NULL,因此获取记录集后,需要对NULL的数据进行转换操作. 本文将提供一种方法,可以在查询时直接执行转换处理.使获取到的记录集不需要再进行转换. mysql提供了IFNULL函数 IFNULL(expr1, expr2) 如果expr1不是NULL,IFNULL()返回expr1,否则返回expr2 实例: user表结构和数据 +----+-----------+ | id | name | +----+------

  • MySQL的隐式类型转换整理总结

    前言 前几天在看到一篇文章:价值百万的 MySQL 的隐式类型转换感觉写的很不错,再加上自己之前也对MySQL的隐式转化这边并不是很清楚,所以就顺势整理了一下.希望对大家有所帮助. 当我们对不同类型的值进行比较的时候,为了使得这些数值「可比较」(也可以称为类型的兼容性),MySQL会做一些隐式转化(Implicit type conversion). 比如下面的例子: mysql> SELECT 1+'1'; -> 2 mysql> SELECT CONCAT(2,' test'); -

  • MySQL常用类型转换函数总结(推荐)

    1.Concat函数. 连接字符串常用:concat函数.如sql查询条件的like查询, AND c.name like concat(#{param.name},'%') 将Int 转为varchar经常用 concat函数,比如concat(8,'0') 得到字符串 '80' 2.Cast函数:CONVERT函数. 用法:CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name). SELECT

  • MySQL的时间差函数(TIMESTAMPDIFF、DATEDIFF)、日期转换计算函数(date_add、day、date_format、str_to_date)

    1. 时间差函数(TIMESTAMPDIFF.DATEDIFF) 需要用MySQL计算时间差,使用TIMESTAMPDIFF.DATEDIFF,记录一下实验结果 --0 select datediff(now(), now()); --2 select datediff('2015-04-22 23:59:00', '2015-04-20 00:00:00'); --2 select datediff('2015-04-22 00:00:00', '2015-04-20 23:59:00');

  • MySQL日期函数与日期转换格式化函数大全

    Mysql作为一款开元的免费关系型数据库,用户基础非常庞大,本文列出了MYSQL常用日期函数与日期转换格式化函数 1.DAYOFWEEK(date) SELECT DAYOFWEEK('2016-01-16') SELECT DAYOFWEEK('2016-01-16 00:00:00') -> 7 (表示,记住:星期天=1,星期一=2, ... 星期六=7) 2.WEEKDAY(date) SELECT WEEKDAY('2016-01-16') SELECT WEEKDAY('2016-01

  • 谈谈MySQL中的隐式转换

    工作过程中会遇到比较多关于隐式转换的案例,隐式转换除了会导致慢查询,还会导致数据不准.本文通过几个生产中遇到的案例来. 基础知识 关于比较运算的原则,MySQL官方文档的描述: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html 如果 判断符号左右两边有一个为NULL,结果就是null,除非使用安全的等值判断 <=> (none) 05:17:16 >select  null = null; +------------

  • Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    背景 在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢.这个varchar字段有一个复合索引.其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据. 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字.不然就会报错 还有可能查出来的数据不是我们想要的数据.如下图 分析 从执行结果来

  • C语言中自动隐式转换与类型强制转换实例分析

    本文通过一个C程序实例对C语言中自动隐式转换与类型强制转换的注意点进行深入分析,详情如下: 先看一个C程序: #include<stdlib.h> #include<stdio.h> #include<conio.h> double proc(int q){ int n; double sum,t;//本例的关键就在这几个变量的类型上 sum = 2.0; while(sum<=q){ t=sum; //sum = sum+(n+1)/n;//自动隐式转换 sum

  • scala中的隐式类型转换的实现

    Scala语言中的隐式转换是一个十分强大的语言特性,主要可以起到两个作用: 一.自动进行某些数据类型的隐式转换 String类型是不能自动转换为Int类型的,所以当给一个Int类型的变量或常量赋予String类型的值时编译器将报错.所以,一下语句是错误的. val x: Int = "100" 如果需要将一个字符串类型的整形数值赋给Int,比如使用String.toInt方法,例如: val x: Int = "100".toInt 如果想让字符串自动转换为整形,就

  • 一文详解JS 类型转换方法以及如何避免隐式转换

    目录 前言 类型转换的概念 5 种转换方法 1. 字符串转换为数字 2. 数字转换为字符串 3. 布尔值转换为字符串或数字 4. 对象转换为原始值 5. 空值和未定义值转换为数字或字符串 隐式转换 总结 前言 当我们使用JavaScript编写代码时,类型转换是一个非常重要的概念.JavaScript是一种弱类型语言,这意味着变量可以自动转换为另一种类型,而不需要我们明确地指定.在这篇文章中,我们将探讨JavaScript类型转换的概念.转换方法,并针对隐式转换进行分析. 类型转换的概念 Jav

  • MySQL令人大跌眼镜的隐式转换

    目录 MySQL的隐式转换 一.问题描述 二.源码解释 三.结论 MySQL的隐式转换 一.问题描述 show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1

  • MySQL索引失效之隐式转换的问题

    目录 常见索引失效: 一.常见索引失效场景 1.条件字段函数操作 2.条件字段运算操作 3.隐式类型转换 4.隐式字符编码转换 二.类型转换 1.字符串转整型 2.时间类型转换 常见索引失效: 1. 条件索引字段"不干净":函数操作.运算操作 2. 隐式类型转换:字符串转数值:其他类型转换 3. 隐式字符编码转换:按字符编码数据长度大的方向转换,避免数据截取 一.常见索引失效场景 root@test 10:50 > show create table t_num\G ******

  • JavaScript中的一些隐式转换和总结(推荐)

    js中的不同的数据类型之间的比较转换规则如下: 1. 对象和布尔值比较 对象和布尔值进行比较时,对象先转换为字符串,然后再转换为数字,布尔值直接转换为数字 [] == true; //false []转换为字符串'',然后转换为数字0,true转换为数字1,所以为false 2. 对象和字符串比较 对象和字符串进行比较时,对象转换为字符串,然后两者进行比较. [1,2,3] == '1,2,3' // true [1,2,3]转化为'1,2,3',然后和'1,2,3', so结果为true; 3

  • SQL Server 中的数据类型隐式转换问题

    写这篇文章的时候,还真不知道如何取名,也不知道这个该如何将其归类.这个是同事遇到的一个案例,案例比较复杂,这里抽丝剥茧,仅仅构造一个简单的案例来展现一下这个问题.我们先构造测试数据,如下所示: CREATE TABLE TEST ( ID INT, GOOD_TYPE VARCHAR(12), GOOD_WEIGHT NUMERIC(18,2) ) INSERT INTO dbo.TEST VALUES( 1, 'T1',1.27) SELECT GOOD_TYPE, CASE WHEN ( G

随机推荐