本文主要向大家介绍了MySQL数据库之MySQL索引使用:字段为varchar类型时,条件要使用''包起来 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
结论:
当MySQL中字段为int类型时,搜索条件wherenum=‘111‘与wherenum=111都可以使用该字段的索引。
当MySQL中字段为varchar类型时,搜索条件wherenum=‘111‘可以使用索引,wherenum=111不可以使用索引
验证过程:
建表语句:
CREATETABLE`gyl`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`str`varchar(255)NOTNULL,
`num`int(11)NOTNULLDEFAULT‘0‘,
`obj`varchar(255)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`str_x`(`str`),
KEY`num_x`(`num`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
向表中使用自复制语句插入数据
insertintogyl(`str`,`num`)values(123123,‘12313‘);
insertintogyl(`str`,`num`)select`str`,`num`fromgyl;
更改数据updategylsetnum=id,str=id
结果:
mysql>explain
select*fromgylwherestr=123123limit1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|1|SIMPLE|gyl|ALL|str_x|NULL|NULL|NULL|262756|Usingwhere|
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1rowinset
mysql>explainselect*fromgylwherestr=‘123123‘limit1;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
|1|SIMPLE|gyl|ref|str_x|str_x|257|const|131378|Usingwhere|
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
1rowinset
mysql>explainselect*fromgylwherenum=‘12313‘limit1;;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
|1|SIMPLE|gyl|ref|num_x|num_x|4|const|131378||
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
1rowinset
1065-Querywasempty
mysql>explainselect*fromgylwherenum=12313limit1;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
|1|SIMPLE|gyl|ref|num_x|num_x|4|const|131378||
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
1rowinset
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!