字符类型数据缺失引号索引被抑制

发布时间：2020-08-11 01:42:30 作者：StevenBeijing
来源：ITPUB博客阅读：154

字符类型的数据没有使用引号，索引将被抑制，下边看一个案例：
查看表结构：

点击(此处)折叠或打开

username字段不加引号：

点击(此处)折叠或打开

mysql> select * from test06 where username=13499770088;
Empty set, 8208 warnings (5.77 sec)
mysql> explain select * from test06 where username=13499770088;
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | test06 | ALL | idx_test03_name | NULL | NULL | NULL | 2009559 | Using where |
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+

username字段加引号：

点击(此处)折叠或打开

mysql> select * from test06 where username='13499770088';
Empty set (0.07 sec)
mysql> explain select * from test06 where username='13499770088';
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | test06 | ref | idx_test03_name | idx_test03_name | 302 | const | 1 | Using index condition |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+

查询速度明显变快，执行计划走了索引，这样是正常的写法。

相关阅读