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

發布時間：2020-08-11 01:42:30 來源：ITPUB博客閱讀：154 作者：StevenBeijing 欄目：MySQL數據庫

字符類型的數據沒有使用引號，索引將被抑制，下邊看一個案例：
查看表結構：

點擊(此處)折疊或打開

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 |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+

查詢速度明顯變快，執行計劃走了索引，這樣是正常的寫法。

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站