字符類別數據缺失引號索引被抑制

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 加強