开发者

详解隐秘的 MySQL 类型转换问题详解

目录
  • 1、问题开篇
    • 1、字符串类型查询
    • 2、数值型查询
  • 2、问题引申
    • 3、跟进探究
      • 3.1 什么是隐式类型转换?
      • 3.2 如何避免隐式类型转换?
        • 3.2.1 清楚转换规则
        • 3.2.2 使用内置函数显示转换
        • 3.2.3 类型保持一致
      • 3.3 字符类型转换
      • 4、总结

        1、问题开篇

        本文来自于我近期工作中遇到的一个真实问题,稍作整理后分享给大家~

        一张用户表,其中 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';
        

        执行计划如下:

        详解隐秘的 MySQL 类型转换问题详解

        2、数值型查询

        EXPLAIN SELECT * FROM users WHERE phone = 2;
        

        执行计划如下:

        详解隐秘的 MySQL 类型转换问题详解

        发现问题:

        当索引字段 `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';
        

        执行计划如下:

        详解隐秘的 MySQL 类型转换问题详解

        2、数值型查询

        EXPLAIN SELECT * FROM users WHERE phone = 2;
        

        执行计划如下:

        详解隐秘的 MySQL 类型转换问题详解

        执行后发现,无论是以字符串查询还是以数值型查询都会用到索引。

        小结:

        • 当索引字段是数值类型时,数值型或者字符型查询都不影响索引的使用。
        • 当索引字段是字符类型时,数值型查询无法使用索引,字符型查询可正常使用索引。

        开发者_SQLite

        3、跟进探究

        为什么会是这样呢?其根源就是 mysql 的隐式类型转换。

        3.1 什么是隐式类型转换?

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

        即 MySQL 会根据需要自动将数字转换为字符串,或者将字符串转换为数字。

        mysql> SELECT 1+'1';
        -> 2
        mysql> SELECT CONCAT(2,' test');
        -> '2 test'
        

        很明显,上面的 SQL 语句的执行过程中就android出现了隐式转化。

        从结果我们可以判定,SQL1 中将字符串的“1”转换为数字 1,而在 SQL2 中,将数字 2 转换为字符串“2”。

        3.2 如何避免隐式类型转换?

        3.2.1 清楚转换规则

        只有当清楚的知道隐式类型转换的规则,才能从根本上避免产生隐式类型转换。

        参考 MySQL 文档相关描述,确定隐式类型转换规则:

        1、两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换

        2、两个参数都是字符vgdngDb串,会按照字符串来比较,不做类型转换

        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 的类型不同,通过上述转换规则并且经查看 www.devze.comwarnings 可以确认:隐式类型转化将字符串转为了 double 类型。

        由于字符串是非数字型的,所以就会被转换为 0,因此计算结果:0+1=1

        3.2.2 使用内置函数显示转换

        MySQL 对数据进行类型转换,提供了 cast() 和 convert()。

        相同点:两者都是进行数据类型转换,实现的功能基本等同

        不同点:两者的语法不http://www.devze.com同: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);
        

        结果所示:

        详解隐秘的 MySQL 类型转换问题详解

        结果显示同应用字符串类型参数一样,可使用索引`idx_phone`。

        3.2.3 类型保持一致

        最简单的一种,保证查询应用规范,SQL 参数类型与数据库中字段类型保持一致即可。

        3.3 字符类型转换

        另外,关于字符串类型转换的一些补充:

        mysql> select '1a2b3c' = 1;
        -> 1
        mysql> select 'a1b2c3' = 0;
        -> 1
        

        从上面的例子可以得出:

        • 如果字符串的第一个字符就是非数字的字符,那么转换为数字就是 0;
        • 如果字符串以数字开头,那转换的数字就是开头的那些数字对应的值,直到遇到非数字字符才结束。

        4、总结

        本文主要从问题入手,继而进行编程问题引申,最终挖掘出问题根源:MySQL 隐式类型转换。

        同时也告诫我们日常在写 SQL 时一定要检查参数类型与数据库字段类型是否一致,否则可能造成隐式类型转换,不能正常应用索引,造成慢查询,甚至拖垮整个数据库服务集群。

        如果参数不一致,也可以考虑使用 CAST 函数显性转换成一致类型。

        数据表设计及应用绝非易事,需要考虑的因素太多了,大家应用过程注意保持敬畏心。

        以上就是详解隐秘的 MySQL 类型转换问题详解的详细内容,更多关于MySQL 类型转换的资料请关注我们其它相关文章!

        0

        上一篇:

        下一篇:

        精彩评论

        暂无评论...
        验证码 换一张
        取 消

        最新数据库

        数据库排行榜