【一、异常情况】

    前段时间，线上业务 php 代码出现了一个 sql 慢查询，简化为如下例子：

mysql> select * from test where name=1111;

+----+------+---------+
| id | name | randnum |
+----+------+---------+
| 31 | 1111 |     607 |
+----+------+---------+
1 row in set

    其对应的表结构为：

CREATE TABLE `test` (
  `id`      int(11)         NOT NULL AUTO_INCREMENT,
  `name`    varchar(100)    NOT NULL,
  `randnum` int(11)         NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),
  KEY `name` (`name`)
);

    这个 select 语句的 where 条件使用了 name = 1111 的写法，但是 name字段是 string 类型，1111 是 int 类型。explain 分析如下，type = All 表示造成了全表扫描。

mysql> explain select * from test where name=1111;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | name          | NULL | NULL    | NULL |   35 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

    这样也能查询出结果，而不是报错，说明 mysql 对等于号两边的参数，进行了 隐式的类型转换，从而做到了兼容，但是同时 失去了name 字段索引 的使用。

【二、正常情况】

    调整 where 语句为正常的 name = "1111" 时，此次查询同样能够查出数据，且正常地应用了索引。

mysql> select * from test where name="1111";

+----+------+---------+
| id | name | randnum |
+----+------+---------+
| 31 | 1111 |     607 |
+----+------+---------+
1 row in set

    explain 结果，此时 type = ref。

mysql> explain select * from test where name="1111";

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | name          | name | 402     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set

【三、mysql 隐式类型转换的规则】

    这里基于 mysql 5.7 的官方文档，给出进行比较判断时 2 个参数类型的隐式转换规则。

        ·    如果 2 个参数至少有一个为 null， 那么判断结果直接判定为 null。这条规则有一个例外，就是 <=> 操作符，当计算 null <=> null，会返回 true 的结果。

        ·    如果 2 个参数都是 string 类型，那么不进行转换，按照 string 进行比较。

        ·    如果 2 个参数都是 int 类型，那么不进行转换，按照 int 进行比较。

        ·    16 进制数如果和 int 类型比较，那么它就会被转化为 int 类型；如果和 string 类型进行比较，那么它会被转化为二进制字符串。

        ·    如果有一个参数为 TIMESTAMP 或者 DATETIME 类型时，如果另一个参数为常量，那么这个常量会被转化为对应的时间类型。但是使用 IN() 语法进行判断时，不会进行这个隐式转换。mysql 推荐在跨类型比较时间时，使用 CAST() 语法进行显示类型转化。

        ·    如果有一个参数为 DECIMAL 类型，当另一个参数为 DECIMAL 类型或者 int 类型时，它们会被转化为 DECIMAL 类型进行比较；当另一个参数为 float 类型时，它们会被转化为 float 类型进行比较。

        ·    所有其他情况下，2 个参数都会被转化为 float 实数进行比较。（int 类型也包含在 float 类型中）

    这里可以看出，本文例子中的隐式类型转换对应了第 7 条规则：字符串被转成了数字（int 包含在 float 中）

【四、为什么 string 转化为 float 会导致索引失效】

    mysql 转换字符串到数字时，使用的规则类似于弱类型编程语言那样：从这个 string 的前面第一个有效字符开始解析，一直到解析出错，这样就会造成不同的字符串会转化为相同的数字。

    下面看下几个字符串转化的结果。

mysql> select "1111" = 1111; 

+---------------+
| "1111" = 1111 |
+---------------+
|             1 |   # 结果相等
+---------------+
1 row in set

mysql> select "     1111" = 1111;

+--------------------+
| "     1111" = 1111 |
+--------------------+
|                  1 |   # 结果也相等
+--------------------+
1 row in set

mysql> select "     1111aaaaaa" = 1111;

+--------------------------+
| "     1111aaaaaa" = 1111 |
+--------------------------+
|                        1 |    # 结果依然相等
+--------------------------+
1 row in set

mysql> select "0001111aaaaaa" = 1111;

+------------------------+
| "0001111aaaaaa" = 1111 |
+------------------------+
|                      1 |      # 还是相等
+------------------------+
1 row in set

    上面 4 个例子明确表明，不同的 string 可以转化为同一个 int（int 包含在 float 中），但是这些 string 明显不相等，它们的索引条目也是分散在整个索引中。

    所以 mysql 无法利用索引减少检索条目。

【五、php 的难处】

    这种类型误传是很难被发现的，发现的时候，基本都是线上爆出了慢查询。

    这里强类型语言的好处就体现出来了：先判断变量类型再决定是否拼接双引号。

    但是对 php 而言，无论是手工拼接 sql 语句，还是使用 prepare binding 生成查询，都不能消除这种风险，所以最好有工具能够帮助使用弱类型语言的程序员避免这种问题。

【六、利用规则】

    通过上面的规则，可以得知，每个类型和 string 进行比较时，都会优先把字符串转化为对应的类型，所以我们可以可以把所有的 sql 参数都转化为 string 类型，这样，就避免了 string 列在和 int 比较时，失去索引依赖的风险。同时，对正常的查询也没有什么坏影响。

    因此，php 的 orm 框架实现中，可以把所有 binding 参数都转化为 string 类型。（手工拼接 sql 的情形就没办法了，推荐他们转化到 orm 框架上来）

【七、hacking 例子】

    作者经历过的一个项目中使用了自研的 orm 框架，就使用了上面所说的技巧。

    当前开源的主流 orm 框架，如 laravel 的 Eloquent ，就还未增加这个处理。

    这里以 laravel 5.5 为例子，它的关键函数是 binding 参数的保存。

    原样是：

// vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php 

public function addBinding($value, $type = 'where') {
    if (!array_key_exists($type, $this->bindings)) {
        throw new InvalidArgumentException("Invalid binding type: {$type}.");
    }

    if (is_array($value)) {
        $this->bindings[$type] = array_values(array_merge($this->bindings[$type], $value));
    } else {
        $this->bindings[$type][] = $value;
    }

    return $this;
}

接下来，在它的 binding 参数保存前，增加一步字符串转化。

修改后是：

// vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php

public function addBinding($value, $type = 'where') {
    if (!array_key_exists($type, $this->bindings)) {
        throw new InvalidArgumentException("Invalid binding type: {$type}.");
    }

    if (is_array($value)) {
        $this->bindings[$type] = array_values(array_merge($this->bindings[$type], $value));
        $tmp = [];
        // 把数组中的每个元素都转化为 string
        foreach ($this->bindings[$type] as $k => $v) {
            $tmp[$k] = "" . $v;
        }
        $this->bindings[$type] = $tmp;

    } else {
        // 把一个值转化为 string
        $this->bindings[$type][] = "" . $value;
    }
    return $this;
}

【八、参考文档】

《MySQL :: MySQL 5.7 Reference Manual :: 12.2 Type Conversion in Expression Evaluation》

（https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html）