详解隐秘的 MySQL 类型转换问题详解
本文来自于我近期工作中遇到的一个真实问题,稍作整理后分享给大家~
一张用户表,其中 phone 添加了普通索引:
CREATE TABLE users (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID ,
name varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT COMMENT 名称 ,
phone varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT COMMENT 手机 ,
created_at timestamp NOT NULL DEFAULT 1970-01-01 16:00:00 COMMENT 创建时间 ,
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ,
PRIMARY KEY (id),
KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT= 用户表
分别执行以下 SQL:
1、字符串类型查询 EXPLAIN SELECT * FROM users WHERE phone = 2执行计划如下:
执行计划如下:
发现问题:
当索引字段 `phone` 为字符串类型时,字符串查询时候使用了索引`idx_phone`,而数值类型查询时候竟无法使用索引`idx_phone`。
2、问题引申假如索引字段为整型的话,那用字符串查询时会不会走索引呢?
实践出真知,我们来验证一下。
同样如上表,修改字段 `phone` 类型由 varchar 变更为 bigint:
ALTER TABLE users MODIFY COLUMN phone bigint(16) NOT NULL COMMENT 手机然后,分别执行以下 SQL:
1、字符串类型查询
EXPLAIN SELECT * FROM users WHERE phone = 2执行计划如下:
2、数值型查询
EXPLAIN SELECT * FROM users WHERE phone = 2;执行计划如下:
执行后发现,无论是以字符串查询还是以数值型查询都会用到索引。
小结:
当索引字段是数值类型时,数值型或者字符型查询都不影响索引的使用。 当索引字段是字符类型时,数值型查询无法使用索引,字符型查询可正常使用索引。 3、跟进探究为什么会是这样呢?其根源就是 MySQL 的隐式类型转换。
3.1 什么是隐式类型转换?在 MySQL 中,当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容,则会发生隐式类型转换。
即 MySQL 会根据需要自动将数字转换为字符串,或者将字符串转换为数字。
mysql SELECT 1+ 1- 2
mysql SELECT CONCAT(2, test );
- 2 test
很明显,上面的 SQL 语句的执行过程中就出现了隐式转化。
从结果我们可以判定,SQL1 中将字符串的 1 转换为数字 1,而在 SQL2 中,将数字 2 转换为字符串 2 。
3.2 如何避免隐式类型转换? 3.2.1 清楚转换规则只有当清楚的知道隐式类型转换的规则,才能从根本上避免产生隐式类型转换。
参考 MySQL 文档相关描述,确定隐式类型转换规则:
1、两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 = 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
2、两个参数都是字符串,会按照字符串来比较,不做类型转换
3、两个参数都是整数,按照整数来比较,不做类型转换
4、十六进制的值和非数字做比较时,会被当做二进制串
5、有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
6、有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
7、所有其他情况下,两个参数都会被转换为浮点数再进行比较
验证示例:
mysql SELECT aa + 1;- 1
mysql show warnings;
+ + + -+
| Level | Code | Message |
+ + + -+
| Warning | 1292 | Truncated incorrect DOUBLE value: aa |
+ + + -+
上述示例中,将字符串 aa 和 1 进行求和,因为 aa 和数字 1 的类型不同,通过上述转换规则并且经查看 warnings 可以确认:隐式类型转化将字符串转为了 double 类型。
由于字符串是非数字型的,所以就会被转换为 0,因此计算结果:0+1=1
3.2.2 使用内置函数显示转换MySQL 对数据进行类型转换,提供了 cast() 和 convert()。
相同点:两者都是进行数据类型转换,实现的功能基本等同
不同点:两者的语法不同:cast(value as type) 、 convert(value,type)
将数值型转换为字符串型,应用示例如下:
mysql SELECT CAST(123 as char);- 123
mysql SELECT CONVERT(123, char);
- 123
假如应用在开篇描述问题的查询中,则如下所示:
EXPLAIN SELECT * FROM users WHERE phone = CAST(123 AS CHAR);结果所示:
结果显示同应用字符串类型参数一样,可使用索引`idx_phone`。
3.2.3 类型保持一致最简单的一种,保证查询应用规范,SQL 参数类型与数据库中字段类型保持一致即可。
3.3 字符类型转换另外,关于字符串类型转换的一些补充:
mysql select 1a2b3c = 1;- 1
mysql select a1b2c3 = 0;
从上面的例子可以得出:
如果字符串的第一个字符就是非数字的字符,那么转换为数字就是 0; 如果字符串以数字开头,那转换的数字就是开头的那些数字对应的值,直到遇到非数字字符才结束。本文主要从问题入手,继而进行问题引申,最终挖掘出问题根源:MySQL 隐式类型转换。
同时也告诫我们日常在写 SQL 时一定要检查参数类型与数据库字段类型是否一致,否则可能造成隐式类型转换,不能正常应用索引,造成慢查询,甚至拖垮整个数据库服务集群。
如果参数不一致,也可以考虑使用 CAST 函数显性转换成一致类型。
数据表设计及应用绝非易事,需要考虑的因素太多了,大家应用过程注意保持敬畏心。
本篇文章到此结束,如果您有相关技术方面疑问可以联系我们技术人员远程解决,感谢大家支持本站!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 详解隐秘的 MySQL 类型转换问题详解
相关文章
- MySQL中文排序法导论(中文排序mysql)
- MySQL的更新SQL实用技巧(mysql更新sql)
- MySQL删除唯一约束的指南(mysql删除唯一约束)
- MySQL中文数据无法插入问题排查(mysql中文无法插入)
- MySQL 外部连接:让数据丰富多彩(mysql外部连接)
- MySQL 时间自动插入实现技术(mysql时间自动插入)
- MySQL实现将行转列的技巧(mysql行转列)
- MySQL中的转义字符串:完全掌握的利器(mysql转义字符串)
- 自学MySQL:走上学习之路(如何自学mysql)
- MySQL表名大小写问题解析(mysql表名小写)
- Discover the Best MySQL Backup Solutions for Your Data(mysql备份方案)
- MySQL 解放数据库变革:简单易用的命名空间(mysql 命名空间)
- MySQL中rtime的作用及使用方法详解(mysql中rtime)
- MySQL中的real数据类型详解(mysql中real)
- 将CSV数据迁移至MySQL数据库(csv转到mysql)
- MySQL中的BLOB数据类型详解(mysql中blob类)
- MySQL 查询排除不等于条件(7 mysql查询不等于)
- 2天后 MySQL 将迎来新生(2天后mysql)
- Arch环境下升级MySQL出现问题解决之路(arch降级mysql)
- MySQL原生统计数量功能详解(mysql中原生统计数量)
- MySQL数据统计亿级数据中如何处理一千万数据(mysql一千万统计)
- MySQL中的条件语句if使用方法详解(mysql《if》)
- 数据库选型指南不含 MySQL 25字中文文章标题(mysql 不含)
- MySQL数据库遭遇两个冲突问题,如何解决(mysql下了两个冲突)
- 解决MySQL无法运行代码问题(mysql不能运行代码)
- MySQL外键无法删除的问题(mysql 不能删除外键)
- 如何解决MySQL无法保存查询的问题(mysql 不能保存查询)
- 解决MySQL自动重启问题的方法(mysql不断自动重启)
- 解析mysql修改为utf8后仍然有乱码的问题