MySQL索引使用：欄位為varchar類型時，條件要使用''包起來

標籤：insert   條件   bsp   using   create   date   incr   obj   mysql索引   

結論：

當MySQL中欄位為int類型時，搜尋條件where num=‘111‘ 與where num=111都可以使用該欄位的索引。
當MySQL中欄位為varchar類型時，搜尋條件where num=‘111‘ 可以使用索引，where num=111 不可以使用索引

驗證過程：

    建表語句：

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

結果：

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 setmysql> 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 setmysql> 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 set1065 - Query was emptymysql> 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

　　

MySQL索引使用：欄位為varchar類型時，條件要使用''包起來