在mysql中，我们使用sql进行where查询时，当查询条件的左右两边类型不匹配时，mysql会自动的根据一定的规则帮我们进行类型转换，转换成相同类型之后再进行比较查询，这就是隐式转换。隐式转换会带来的一个问题就是：可能会导致查询使用不了索引。
我们举个最常见的字符串和数值类型的例子，复制下方的sql语句并执行作为测试数据
-- demo表
drop table if exists `demo`;
create table `demo`(
`id` bigint(20) not null auto_increment comment '主键id',
`str` varchar(32) not null default '' comment '字符串',
`long` bigint(20) not null default '0' comment '数值',
primary key (`id`),
key `str` (`str`),
key `long` (`long`)
) engine=innodb auto_increment=1000 default charset=utf8mb4 comment='demo表';
insert into `demo` values (1001,'1001',1001);
insert into `demo` values (1002,'1002',1002);
insert into `demo` values (1003,'1003',1003);
insert into `demo` values (1004,'1004',1004);
insert into `demo` values (1005,'1005',1005);
insert into `demo` values (1006,'1006',1006);
insert into `demo` values (1007,'1007',1007);
insert into `demo` values (1008,'1008',1008);
insert into `demo` values (1009,'1009',1009);
insert into `demo` values (1010,'1010',1010);
insert into `demo` values (1011,'1011',1011);
insert into `demo` values (1012,'1012',1012);
insert into `demo` values (1013,'1013',1013);
insert into `demo` values (1014,'1014',1014);
insert into `demo` values (1015,'1015',1015);
insert into `demo` values (1016,'1016',1016);
insert into `demo` values (1017,'1017',1017);
insert into `demo` values (1018,'1018',1018);
insert into `demo` values (1019,'1019',1019);
insert into `demo` values (1020,'1020',1020);
insert into `demo` values (1021,'1021',1021);
insert into `demo` values (1022,'1022',1022);
insert into `demo` values (1023,'1023',1023);
insert into `demo` values (1024,'1024',1024);
insert into `demo` values (1025,'1025',1025);
insert into `demo` values (1026,'1026',1026);
insert into `demo` values (1027,'1027',1027);
insert into `demo` values (1028,'1028',1028);
insert into `demo` values (1029,'1029',1029);
insert into `demo` values (1030,'1030',1030);
insert into `demo` values (1031,'1031',1031);
insert into `demo` values (1032,'1032',1032);
insert into `demo` values (1033,'1033',1033);
insert into `demo` values (1034,'1034',1034);
insert into `demo` values (1035,'1035',1035);
insert into `demo` values (1036,'1036',1036);
insert into `demo` values (1037,'1037',1037);
insert into `demo` values (1038,'1038',1038);
insert into `demo` values (1039,'1039',1039);
insert into `demo` values (1040,'1040',1040);
insert into `demo` values (1041,'1041',1041);
insert into `demo` values (1042,'1042',1042);
insert into `demo` values (1043,'1043',1043);
insert into `demo` values (1044,'1044',1044);
insert into `demo` values (1045,'1045',1045);
insert into `demo` values (1046,'1046',1046);
insert into `demo` values (1047,'1047',1047);
insert into `demo` values (1048,'1048',1048);
insert into `demo` values (1049,'1049',1049);
insert into `demo` values (1050,'1050',1050);
insert into `demo` values (1051,'1051',1051);
insert into `demo` values (1052,'1052',1052);
insert into `demo` values (1053,'1053',1053);
insert into `demo` values (1054,'1054',1054);
insert into `demo` values (1055,'1055',1055);
insert into `demo` values (1056,'1056',1056);
insert into `demo` values (1057,'1057',1057);
insert into `demo` values (1058,'1058',1058);
insert into `demo` values (1059,'1059',1059);
insert into `demo` values (1060,'1060',1060);
insert into `demo` values (1061,'1061',1061);
insert into `demo` values (1062,'1062',1062);
insert into `demo` values (1063,'1063',1063);
insert into `demo` values (1064,'1064',1064);
insert into `demo` values (1065,'1065',1065);
insert into `demo` values (1066,'1066',1066);
insert into `demo` values (1067,'1067',1067);
insert into `demo` values (1068,'1068',1068);
insert into `demo` values (1069,'1069',1069);
insert into `demo` values (1070,'1070',1070);
insert into `demo` values (1071,'1071',1071);
insert into `demo` values (1072,'1072',1072);
insert into `demo` values (1073,'1073',1073);
insert into `demo` values (1074,'1074',1074);
insert into `demo` values (1075,'1075',1075);
insert into `demo` values (1076,'1076',1076);
insert into `demo` values (1077,'1077',1077);
insert into `demo` values (1078,'1078',1078);
insert into `demo` values (1079,'1079',1079);
insert into `demo` values (1080,'1080',1080);
insert into `demo` values (1081,'1081',1081);
insert into `demo` values (1082,'1082',1082);
insert into `demo` values (1083,'1083',1083);
insert into `demo` values (1084,'1084',1084);
insert into `demo` values (1085,'1085',1085);
insert into `demo` values (1086,'1086',1086);
insert into `demo` values (1087,'1087',1087);
insert into `demo` values (1088,'1088',1088);
insert into `demo` values (1089,'1089',1089);
insert into `demo` values (1090,'1090',1090);
insert into `demo` values (1091,'1091',1091);
insert into `demo` values (1092,'1092',1092);
insert into `demo` values (1093,'1093',1093);
insert into `demo` values (1094,'1094',1094);
insert into `demo` values (1095,'1095',1095);
insert into `demo` values (1096,'1096',1096);
insert into `demo` values (1097,'1097',1097);
insert into `demo` values (1098,'1098',1098);
insert into `demo` values (1099,'1099',1099);
insert into `demo` values (1100,'1100',1100);
简单建了一个表，有一个str字段是字符串类型，一个long字段是数值类型，两个字段均有索引，且插入了100条测试数据。看下方的2个查询：
SQL1：
select * from `demo` where `str`=1001;
SQL2：
select * from `demo` where `long`='1001';
SQL1和SQL2都能查出相同的结果，但是区别是什么呢？区别就在于SQL1使用不了索引，我们通过explain语句看一下结果：
SQL1：
explain
select * from `demo` where `str`=1001;
|id |select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
|---|-----------|-----|----------|----|-------------|---|-------|---|----|--------|-----------|
|1 |SIMPLE |demo | |ALL |str | | | |100 |10 |Using where|
SQL2：
explain
select * from `demo` where `long`='1001';
|id |select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
|---|-----------|-----|----------|----|-------------|----|-------|-----|----|--------|---------------------|
|1 |SIMPLE |demo | |ref |long |long|8 |const|1 |100 |Using index condition|
可以看到，SQL1进行了全表扫描，因为查询中使用了字符串类型和数值类型两个不同类型的比较，两个不同的类型肯定是无法比较大小及相等的，所以mysql进行了类型的隐式转换，按照规则是将字符串类型转换成数值类型之后再进行比较。所以，SQL1中是需要将表中str字段的所有数据都转换成数值类型然后跟1001进行比较，故SQL1使用不了索引，从而进行了全表扫描。而SQL2中，按照规则，只需要将查询条件1001转换成数值类型，而不需要转换表中的long字段的数据，所以可以直接使用long字段的索引。
通过上述例子可以得出一个结论：我们在写sql的进行查询的时候，要多留个心眼看下查询时的类型是否一致，不一致的时候要保证隐式转换只会转换我们的查询条件，而不是全表做隐式转换，保证查询能走到索引。
最后还有一个问题，在mysql中，隐式转换的规则是什么？
完整的规则可以看mysql的官方文档：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
但是实际开发中最常见的还是字符串和数值两种类型，所以只要记住：字符串会转换成数值类型，基本就够用了。
