MySQL类型隐式转换int/varchar的坑

MySQL字段类型隐式转换

1. 查询参数类型与数据库字段类型不一致时存在隐式转换
2. 类型隐式转换后可能造成全表扫描
3. 字符类型的尾部空格与换行符处理方式不同

int 类型与 varchar 类型隐式转换

测试数据

create table test(id int primary key, desc varchar(10) key `idx_desc` (`desc`));
insert into table test values(1, '1 ') (2, '2\n') ......;

• 字段类型为int，查询参数为字符类型，索引有效
• MySQL会将查询参数字符转换为数字，转换时丢弃遇到的第一个非数字字符

如下3条语句查询等效，且索引有效

select * from test where id = 1;
select * from test where id = '1';
select * from test where id = '1abc';

• 字段类型为varchar，查询参数为int类型，索引无效
• MySQL全表扫描后，将varchar字段值转为int，再与查询条件匹配

select * from test where desc = 1;  --匹配id=1的行
select * from test where desc = 2;  --匹配id=2的行

• 当查询参数和数据库字段均为字符varchar，MySQL将trim尾部空格
  如下两条SQL返回同一行

select * from test wehre desc = '1';
select * from test wehre desc = '1 ';

• MySQL不会处理尾部回车换行符（与空格的处理不同）
  如下SQL匹配不到id=2的行

select * from test where desc = '2\t换行符';