字符类型的数据没有使用引号,索引将被抑制,下边看一个案例:
查看表结构:
点击(此处)折叠或打开
成都创新互联公司服务项目包括新乡县网站建设、新乡县网站制作、新乡县网页制作以及新乡县网络营销策划等。多年来,我们专注于互联网行业,利用自身积累的技术优势、行业经验、深度合作伙伴关系等,向广大中小型企业、政府机构等提供互联网行业的解决方案,新乡县网站推广取得了明显的社会效益与经济效益。目前,我们服务的客户以成都为中心已经辐射到新乡县省份的部分城市,未来相信会继续扩大服务区域并继续获得客户的支持与信任!
-
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字段不加引号:
-
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 |
-
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
查询速度明显变快,执行计划走了索引,这样是正常的写法。
当前题目:字符类型数据缺失引号索引被抑制
网站链接:
http://www.cdkjz.cn/article/pcdcej.html