mysql索引字段varchar_MySQL索引使用：字段为varchar类型时，条件要使用''包起来

结论：

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

MySQL索引使用：字段为varchar类型时，条件要使用''包起来

标签：insert   条件   bsp   using   create   date   incr   obj   mysql索引

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/jixingke/p/8486075.html