MySQL隐式转换解决方案

一. mysql隐式转换有2个大坑

1.当表字段设置成字符串类型,查询的时候传整型,mysql会将全表字符串类型转换成整型再比较,触发全表扫描。

参考:https://www.cnblogs.com/zhizhuwang/p/3464212.html

2.不管字段是什么类型,只要in内部同时出现整型和字符串时,会无法使用索引,会触发全表扫描。

参考:https://yuerblog.cc/2017/05/22/mysql-implicit-type-cast/

二. 目前看来比较稳妥的方案

1.框架层面对记录每个字段的类型,根据字段类型做转换

如果是新项目可以这么做,或者选了一个封装很重的框架,必须设置表字段类型,应该就不用担心这个问题了。

2.在底层将所有字段包括in查询中每个值统一转成字符串

统一转成字符串刚好可以规避以上两个大坑

目前除了性能会变差一点和无法直接用整型查询字符串类型,这两个问题都可忽略,没有发现什么大问题。

三. 附上MySQL隐式转换规则

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

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

3. 两个参数都是整数,按照整数来比较,不做类型转换。

4. 十六进制的值和非数字做对比时,会被当做二进制串。

5.  有一个参数是 TIMESTAMP 或者 DATETIME 时,并且另外一个参数是常量,常量会被转换为 TIMESTAMP。

6.  有一个参数是 decimal 类型,如果另外一个是 decimal 或者整数,会将整数转换为 decimal 后进行比较;如果另外一个参数浮点数,则会把 decimal 转换为浮点数进行比较。

7.  所有其他情况下,两个参数都会被转换为浮点数再进行比较。

问题描述

where 条件语句里,字段属性和赋给的条件,当数据类型不一样,这时候是没办法进行直接比较的,需要进行一致性转换。

默认的转换规则为:

不同类型全部都转换为浮点型

如果字段是字符,条件是整型,那么会把表中的字段全部转换为整型。

转换总结

字符转整型

1. 字符开头的一律为0

2. 数字开头的,直接截取到第一个不是字符的位置

时间类型转换

1. date 转 datetime 或者 timestamp

    追加 00:00:00

2. date 转 time

    无意义,直接为 00:00:00

3. datetime 或者 timestamp 转 date

    直接截取 date 字段

4. datetime 或者 timestamp 转 time

    直接截取 time 字段

5. time 转 datetime 或者 timestamp

    按照字符串进行截取

6. time 和 datetime 转换为数字时,会变为双精度,加上ms(版本不同有区别)

点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注