MySQL在字符串类型字段上搜索整型值时无法使用索引

示例

表如下：

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`),
  KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

当执行下面这样的sql时无法使用索引

select * from user where user_name = 100;

因为上面的sql语句等价于

select * from user where convert(user_name, signed) = 100;

即搜索把user_name字段转化为整型后的值等于100的行。而这样的user_name是可以有很多的，例如”100”，”0100”和”00100”。所以不能使用索引进行查找！

源码解释

mysql版本：5.6.17
文件：sql/sql_optimizer.cc
函数：add_key_field
源码解释：

      /*
        We can't always use indexes when comparing a string index to a
        number. cmp_type() is checked to allow compare of dates to numbers.
        eq_func is NEVER true when num_values > 1
       */
      if (!eq_func)
        return;
      if (field->result_type() == STRING_RESULT)
      {
        if ((*value)->result_type() != STRING_RESULT)
        {
          if (field->cmp_type() != (*value)->result_type())
          {
            warn_index_not_applicable(stat->join->thd, field, possible_keys);
            return;
          }
        }
        else
        {
        ... 
        }

field就是搜索的字段user_name，value就是查找的值100。

参考

1. MySQL: comparison of integer value and string field with index
2. How MySQL Uses Indexes
3. Numeric strings: VARCHAR fields holding numbers