Character type data missing quotation mark index is suppressed
The data of the character type does not use quotation marks, and the index will be suppressed. Here is an example:
View the table structure:
Click (here) to collapse or open
Mysql > show create table test06\ G
* * 1. Row *
Table: test06
Create Table: CREATE TABLE `test06` (
`id`bigint (11) NOT NULL DEFAULT'0'
`u_ id` bigint (11) NOT NULL
`openid` varchar (100) DEFAULT NULL
`unionid` varchar (100) DEFAULT NULL
`username` varchar (100) NOT NULL
`password` varchar (100) NOT NULL
`create_ time`datetime NOT NULL
KEY `idx_test03_ id` (`id`)
KEY `idx_test03_ name` (`username`)
KEY `idx_test06_crea_ time` (`create_ time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec) username field without quotation marks:
Click (here) to collapse or open
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 field in quotation marks:
Click (here) to collapse or open
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 |
The query speed of +-+ is obviously faster. The execution plan is indexed, which is the normal way to write.
