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

2024-09-07 22:12:32

字体：大中小

来源：转载

供稿：网友

   字符类型的数据没有使用引号，索引将被抑制，下边看一个案例：
查看表结构：
点击(此处)折叠或打开

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 |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
查询速度明显变快，执行计划走了索引，这样是正常的写法。

（编辑：武林网）

上一篇：应用示例荟萃 performance_schema全方位解说

下一篇：5.7版本 Semisync Replication 加强