Mysql查询语句中的引号对索引的影响

# explain select * from t1 where user_ip=123 limit 1;——————————————————————————————-| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |——-

# explain select * from t1 where user_ip=123 limit 1;+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | t1 | ALL  | user_ip       | NULL | NULL    | NULL |  262 | Using where |+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+1 row in se<div style="color:transparent">本文来源gaodai^.ma#com搞#代!码网</div>t (0.00 sec)# explain select * from t1 where user_ip='123' limit 1;+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+| id | select_type | table       | type | possible_keys | key     | key_len | ref   | rows | Extra       |+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+|  1 | SIMPLE      | t1 | ref  | user_ip       | user_ip | 47      | const |    1 | Using where |+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+1 row in set (0.01 sec)

可以看到, user_ip 字段是 varchar 类型. 查询时, 如果这个字段的值没有加引号, MySQL 就不会使用索引. MySQL 为什么会有这种致命的策略?!

—– 更新: —–

相关的文档已经找到: http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html, 这里面提到和几种类型转换的情况, 字符串和数字比较都被统一转成浮点数再比较.

Related posts:

1. MySQL”海量数据”查询性能分析
2. MySQL 查询使用 Group By 的注意点
3. 史上最强大的PHP MySQL操作类
4. MySQL 建立外键约束
5. 如何使用SSDB的zscan命令

喜欢 (0)赏

[搞代码]

分享 (0)