MySQL的隐式类型转换

引入

在项目中调用别的部门的模糊查询接口，发现还根据模糊字段搜索主键，还搜出了结果。SQL大概是这个意思Select * from tablea where name like "%1Test%" or id = '1Test',结果是查询到了数据，也就是数据库主键id为1的这条数据。后来发现这里是因为有MySQL的隐式类型转换导致能够查询到数据。

什么是隐式类型转换

在MySQL中:

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

也就是说,MySQL会根据需要自动将数字转换为字符串，将字符串转换数字。

隐式类型转换规则

• 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
• 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。
• 如果两个参数都是整数，则将它们作为整数进行比较，不做类型转换。
• 如果不与数字进行比较，则将十六进制值视为二进制字符串
• 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
• 如果其中一个参数是TIMESTAMP或DATETIME列，另一个参数是常量，则在执行比较之前将常量转换为时间戳。
• 在所有其他情况下，参数都是作为浮点数（实数）比较的。

避免隐式类型转换

• 使用CAST函数显示转换

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

结果：

mysql > 38.8, ‘38.8’

• 类型一致

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

总结

避免发生隐式类型转换，隐式转换的类型主要有字段类型不一致、in 参数包含多个类型、字符集类型或校对规则不一致等隐式类型转换可能导致无法使用索引、查询结果不准确等，因此在使用时必须仔细甄别数字类型的建议在字段定义时就定义为 int 或者 bigint，表关联时关联字段必须保持类型、字符集、校对规则都一致。

由于历史原因，需要兼容旧的设计，可以使用 MySQL 的类型转换函数 cast 和 convert，来明确的进行转换。