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

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

点击(此处)折叠或打开

1. mysql> show create table test06 \G
2. *************************** 1. row ***************************
3.        Table: test06
4. Create Table: CREATE TABLE `test06` (
5.   `id` bigint(11) NOT NULL DEFAULT '0',
6.   `u_id` bigint(11) NOT NULL,
7.   `openid` varchar(100) DEFAULT NULL,
8.   `unionid` varchar(100) DEFAULT NULL,
9.   `username` varchar(100) NOT NULL,
10.   `password` varchar(100) NOT NULL,
11.   `create_time` datetime NOT NULL,
12.   KEY `idx_test03_id` (`id`),
13.   KEY `idx_test03_name` (`username`),
14.   KEY `idx_test06_crea_time` (`create_time`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
16. 1 row in set (0.00 sec)

username字段不加引号：

点击(此处)折叠或打开

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

username字段加引号：

点击(此处)折叠或打开

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

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