谈谈 MySQL 隐式类型转换

来源:andyqian

www.andyqian.com/2017/11/11/database/MySQLConvert/

前言

今天我们继续回到MySQL系列文章中,谈一谈MySQL中隐式类型转换。(其实我最早知道是在慢SQL优化中知道隐式类型转换概念的),在说隐式类型转换之前,首先我们通过一个实例来看看是怎么回事。

数据结构

本文中所有的操作,都是基于该数据结构(有兴趣的童鞋可以实验):

create table t_base_user(

oid bigint(20)notnullprimary keyauto_increment,

name varchar(30)nullcomment"name",

email varchar(30)nullcomment"email",

ageintnullcomment"age",

telephone varchar(30)nullcomment"telephone",

status tinyint(4)nullcomment"0 无效 1 有效",

created_at datetimenulldefaultnow()comment"创建时间",

updated_at datetimenulldefaultnow()comment"修改时间")

### 新建索引alter table t_base_user add index idx_email(email);

alter table t_base_user add index idx_name(name);

alter table t_base_user add index idx_telephone(telephone);

### 新增记录:

INSERTINTO`andyqian`.`t_base_user`(`name`,`email`,`age`,`telephone`,`status`,`created_at`,`updated_at`)

引子

首先我们基于上述数据结构中,我们来看看下面这个执行计划:

explain select * from t_base_user where telephone=12345678901;

执行计划结果:

细心的童鞋应该已经看出来了,为什么数据结构中已经在telephone字段上新建了idx_telephone索引,而上述语句并没有走索引,而是全表扫描。这是为什么呢?带着这疑问,我们来看看今天的主角——MySQL隐式类型转换

什么是隐式类型转换?

在MySQL中:

当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。则会发生转换隐式

也就是说,MySQL会根据需要自动将数字转换为字符串,将字符串转换数字。看到这个概念之后,是不是有一种茅塞顿开的感觉。哦… 原来在数据结构中telephone字段为字符串(varchar)类型,而我们传的手机号是数字类型。现在我们将SQL修改下:

select * from t_base_user where telephone=’12345678901′;

再看看上述语句的执行计划:

explain select * from t_base_user where telephone=’12345678901′;

结果:

从这里看,现在语句已经走索引了。为了加深我们对隐式类型转换的印象,我们再多看看几个隐式类型转换案例:

案例一: 字符串转换为数字

mysql > SELECT 1+’1′;

结果:

案例二: 数字转换为字符串

mysql -> SELECT CONCAT(1024,’ andyqian’);

结果:

‘1024,’ andyqian’;

此时CONCAT(字符拼接)函数就将1024进行了隐式类型转换。

如何避免隐式类型转换?

只有当清楚的知道隐式类型转换的规则,才能从根本上避免产生隐式类型转换。MySQL也在官网描述了进行隐式类型转换的一些规则如下:

1. 隐式类型转换规则

如果一个或两个参数都是NULL,比较的结果是NULL,除了NULL安全的相等比较运算符。对于NULL NULL,结果为true。不需要转换

如果比较操作中的两个参数都是字符串,则将它们作为字符串进行比较。

如果两个参数都是整数,则将它们作为整数进行比较。

如果不与数字进行比较,则将十六进制值视为二进制字符串

如果其中一个参数是十进制值,则比较取决于另一个参数。 如果另一个参数是十进制或整数值,则将参数与十进制值进行比较,如果另一个参数是浮点值,则将参数与浮点值进行比较

如果其中一个参数是TIMESTAMP或DATETIME列,另一个参数是常量,则在执行比较之前将常量转换为时间戳。

在所有其他情况下,参数都是作为浮点数(实数)比较的。

2. 使用CAST函数显示转换

我们可以使用CAST显示的将类型进行转换,如下所示:

mysql> SELECT 38.8, CAST(38.8 AS CHAR);

结果:

mysql > 38.8, ‘38.8’

如上述中:

select * from t_base_user where telephone=cast(12345678901 as char);

查看执行计划,我们也可以看出

你看,这个时候也走索引了。

3. 类型一致

这里说的类型一致,指的是在写SQL时,参数类型一定要与数据库中的类型一致,避免产生隐式类型转换,就如刚才在文首时,如果多检查,写的SQL的参数类型与数据库中字段类型一致,也就不会不走索引了,你说是不是?

小心隐式类型转换

这里再重申一次,写SQL时一定要检查参数类型与数据库字段类型一致,(如果参数不一致,也要使用CAST函数显示转换成一致)否则造成隐式类型转换,不走索引,后果简直不堪设想, 在前面《写会MySQL索引》这篇文章中提到过,不走索引,轻则造成慢查询,重则造成数据库服务器CPU100%。唉,说到这里,不瞒你说,我就吃过不少MySQL隐式类型转换的亏 ! (如慢查询) !

小结

看到这里,是不是有一种,数据表设计还真不是件容易的事情。需要考虑的因素太多太多了,需要考虑字段类型,索引设计,还有各种约束条件等等。也一定要谨慎谨慎再谨慎!其实换个角度就更容易理解了,大家都知道高楼大厦都是需要一个好的地基的,在数据库表设计中,前期的表结构设计就是这个地基,其重要性可想而知。

从后续开始,每篇MySQL文章最后,都推荐一个常用且实用的MySQL命令:

今天的命令是:

show full columns from table_name;

作用: 显示指定表所有列信息

例如:

show full columns from t_base_user;

返回结果如下图所示:

其中:

Field: 字段名

Type: 该字段类型

Collation: 描述了如何对查询出来的数据进行比较和排序

Null: 是否允许为空, NO: 不允许,YES 允许

Key: 键,例如: 主键(PRI), 唯一键(UNI) 等

Default: 该字段默认值 Extra: 附加信息如自增主键上的(auto_increment)

Privileges: 权限,有select,update等

Comment: 字段注释

注意: 通过该命令显示都是建表时的信息,这里着重强调一下,在数据库建表时,在每个字段上, 一定要加注释,加注释,加注释!

最后: 大家双十二剁手了吗? 祝大家晚安!

本文来自企鹅号 - 数据库开发媒体

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏nnngu

算法07 五大查找之:索引查找

上一篇总结了二分查找,这一篇要总结的是索引查找。 关于索引,我们很容易地联想到数据库中的索引,建立了索引,可以大大提高数据库的查询速度。 索引查找又称为分块查找...

43660
来自专栏MelonTeam专栏

浅析Associated Object

Category是Objective C为程序员提供的一个强大的动态机制,它们允许程序员为已有的对象添加新的方法,即便是在没有该对象的源代码的情况下。通过它可以...

239100
来自专栏C#

奇妙的NULL值,你知道多少

《NULL值的多义性分析》       谈到NULL值,很多人都是很熟悉,但是深入了解后,又感觉到陌生,对其含义和用法,都无法很准确的理解。NULL在数据库和...

23050
来自专栏desperate633

第14课 组合查询创建组合查询union的使用规则

组合查询很容易理解就是讲多个查询的结果放在一起显示 使用UNION关键字进行查询的组合

8720
来自专栏xingoo, 一个梦想做发明家的程序员

剑指OFFER之合并有序链表(九度OJ1519)

题目描述: 输入两个单调递增的链表,输出两个链表合成后的链表,当然我们需要合成后的链表满足单调不减规则。 (hint: 请务必使用链表。) 输入: 输入可能包含...

20680
来自专栏Java后端技术

你敢说自己了解单例模式?

  最近在学习设计模式,在看到单例模式的时候,我一开始以为直接很了解单例模式了,实现起来也很简单,但是实际上单例模式有着好几个变种,并且多线程中涉及到线程安全问...

11620
来自专栏Python爱好者

判断是否为小米MIUI系统

196120
来自专栏Clive的技术分享

PHP实现单例模式

<?php /** * 单例模式实现 */ class Singleton { //静态变量保存全局实例 private static $ins...

33470
来自专栏JavaQ

温故而知新-MySQL数据类型

选择数据类型的原则 MySQL支持多种数据类型,选择合适的数据类型存储数据对MySQL存储引擎来说至关重要,下面的一些原则可以在选择数据类型的时候做出更合适的选...

31970
来自专栏CodingToDie

MySql Hash 索引

Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 ...

33030

扫码关注云+社区

领取腾讯云代金券