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

点击(此处)折叠或打开

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)

点击(此处)折叠或打开

1. mysql> select * from test06 where username=13499770088;
   
2. Empty set, 8208 warnings (5.77 sec)
   
3. 
   
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. +----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+

点击(此处)折叠或打开

1. mysql> select * from test06 where username='13499770088';
   
2. Empty set (0.07 sec)
   
3. 
   
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. +----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+