MySQL的隐式转换

柔情只为你懂 2024-03-22 16:49 40阅读 0赞

### 隐式转换 ###

若字符串是以数字开头，并且全部都是数字，则转换的数字结果是整个字符串；部分是数字，则转换的数字结果是截止到第一个不是数字的字符为止  
若字符串不是以数字开头，则转换的数字结果是 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

![在这里插入图片描述][27277d5fe44d4667bf24b52602648ac5.png]  
当作为字符串类型建立索引时，数据顺序和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

![在这里插入图片描述][3c24fa24b88e46f894fc8f28947b8f43.png]  
实际上，B+ 树索引的快速定位能力，来源于同一层兄弟节点的有序性。对索引字段做函数操作，可能会破坏索引值的有序性。  
**而当字符串和数字做比较时，会将字符串转换成数字。**

### 总结 ###

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

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

[27277d5fe44d4667bf24b52602648ac5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/69693efd3b574f3188f2cc81506c6f3b.png
[3c24fa24b88e46f894fc8f28947b8f43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/bbc0041ae5714fb182d1649e6f710837.png