mysql官网对隐式转换的描述:
12.2 Type Conversion in Expression Evaluation
文中的几条规则翻译如下:
1、两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
2、两个参数都是字符串,会按照字符串来比较,不做类型转换
3、两个参数都是整数,按照整数来比较,不做类型转换
4、十六进制的值和非数字做比较时,会被当做二进制串
5、有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
6、有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
7、所有其他情况下,两个参数都会被转换为浮点数再进行比较
隐式转换的示例:
从上面的几个例子看出,两个参数都会转化为浮点数,但’a’转化时失败,结果为0,所以’a’=1 => 0=1,结果为0;
总结几个规则:
1、如果字符串第一个字符不是数字,则字符串转化结果为0;
2、如果字符串是数字开头,但是包含非数字,则从头截取到非数字的那一位进行转化;比如:10as1a => 10,1.01a => 1.01
3、如果字符串都是数字,则转化结果就是对应的数字;
隐式转换的缺陷
1、隐式转换可能带来的全表扫描慢查询
新建test表,并插入数据
CREATE TABLEtest
(
id
int(11) NOT NULL,
num
int(11) NOT NULL,
name
varchar(100) NOT NULL DEFAULT ‘abcdefg’,
KEYname
(name
),
KEYid
(id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
执行sql:select * from test where name = 0
再看执行计划:
很明显看出了两个结果:
(1)sql执行结果跟我们预期的不一样;
(2)执行计划显示没有使用到索引,全表扫描;
分析:
(1)看下sql,name字段是varchar类型且有索引的,但where 条件中参数类型确是int类型,这里执行的时候隐式索引起到了作用,把name字段所有的列转换为了int类型来做比较,如果字符串的第一个字符就是非数字的字符,那么转换为数字就是0,查询到9条数据;
(2)因为隐式转换将所有name列数据转为浮点型进行比较,sql优化器无法确认索引是否有效,所以选择了其他方案;
结论:
在平时写sql时,该是什么类型就是什么类型做匹配;不要偷懒
2、sql注入
插入数据:
执行sql:
SELECT * FROM test WHERE NAME = ‘test1’ AND num =0;
正常情况下查询到一条数据;
如果where 条件参数是:test1’ OR 1 = '1 ;查到多条记录
SELECT * FROM test WHERE NAME = ‘test1’ OR 1 = ‘1’ AND num =0;
其他
没有太多的深入,看懂即可