MySQL的隐式转换

隐式转换

若字符串是以数字开头，并且全部都是数字，则转换的数字结果是整个字符串；部分是数字，则转换的数字结果是截止到第一个不是数字的字符为止
若字符串不是以数字开头，则转换的数字结果是 0

varchar str = "123dafa" // 转换为数字是 123
SELECT '123dafa' + 1; // 124
varchar str = "aabb33" // 转换为数字是 0
SELECT 'aabb33' + 100; // 100
SELECT 1 + '1'; // 2
SELECT CONCAT(2, ' test'); // '2 test'

看一下官方的隐试转换说明：

• 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
• 两个参数都是字符串，会按照字符串来比较，不做类型转换
• 两个参数都是整数，按照整数来比较，不做类型转换
  十六进制的值和非数字做比较时，会被当做二进制串
• 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
• 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
• 所有其他情况下，两个参数都会被转换为浮点数再进行比较

为什么触发隐式类型转换，查询数据时就需要全表扫描？

对于 InnoDB 的 B+树索引结构，相信大多数小伙伴都有一定的了解。
示例有下面一组数据：

1, 2, 3, 4, 6, 6, 7, 11, 13, 21, 23, 39, 42, 61, 71, 
101, 201, 220, 303, 345, 411, 601, 620, 701, 1402, 3333

当作为字符串类型建立索引时，数据顺序和B+树索引结构如下：

1, 101, 11, 13, 1402, 2, 201, 21, 220, 23, 3, 303, 3333, 
345, 39, 4, 411, 42, 6, 6, 601, 61, 620, 7, 701, 71

实际上，B+ 树索引的快速定位能力，来源于同一层兄弟节点的有序性。对索引字段做函数操作，可能会破坏索引值的有序性。
而当字符串和数字做比较时，会将字符串转换成数字。

总结

MySQL查询中，当字符串和数字做比较时，会触发隐式类型转换，转换规则是将字符串转换成数字。

• 当索引字段是字符串类型，输入参数是数值类型时，会将字段转换成数值类型再进行查找，也就是对索引字段做了函数操作，破坏了索引的有序性，因此无法使用索引。
• 当索引字段是数值类型，输参数是字符串类型时，会将输入参数转换成数值类型再进行查找，对等号后面的输入参数进行函数操作，并不影响索引字段的有序性，因此可以使用索引。