結論：
當MySQL中欄位為int型別時，搜索條件where num='111' 與where num=111都可以使用該欄位的索引，
當MySQL中欄位為varchar型別時，搜索條件where num='111' 可以使用索引，where num=111 不可以使用索引
驗證程序：
建表陳述句：
| 1 2 3 4 5 6 7 8 9 |
CREATE TABLE `gyl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(255) NOT NULL,
`num` int(11) NOT NULL DEFAULT '0',
`obj` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `str_x` (`str`),
KEY `num_x` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
向表中使用自復制陳述句插入資料
insert into gyl (`str`,`num`)values(123123,'12313');
insert into gyl (`str`,`num`) select `str`,`num` from gyl;
更改資料 update gyl set num=id,str=id
結果：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> explain
select * from gyl where str=123123 limit 1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | gyl | ALL | str_x | NULL | NULL | NULL | 262756 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set
mysql> explain select * from gyl where str='123123' limit 1;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| 1 | SIMPLE | gyl | ref | str_x | str_x | 257 | const | 131378 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
1 row in set
mysql> explain select * from gyl where num='12313' limit 1;;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
| 1 | SIMPLE | gyl | ref | num_x | num_x | 4 | const | 131378 | |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
1 row in set
1065 - Query was empty
mysql> explain select * from gyl where num=12313 limit 1;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
| 1 | SIMPLE | gyl | ref | num_x | num_x | 4 | const | 131378 | |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
1 row in set
|
欄位型別不同造成的隱式轉換，導致索引失效
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/68449.html
標籤：MySQL