mysql varchar int 123 走索引吗？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql varchar int 123 走索引吗？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3104字，纯文字阅读大概需要5分钟。
内容图文
结论：
当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
|
字段类型不同造成的隐式转换，导致索引失效
内容总结
以上是互联网集市为您收集整理的mysql varchar int 123 走索引吗？全部内容，希望文章能够帮你解决mysql varchar int 123 走索引吗？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。