MySQL的隐式类型转换整理总结
前言
前几天在看到一篇文章：价值百万的MySQL的隐式类型转换感觉写的很不错，再加上自己之前也对MySQL的隐式转化这边并不是很清楚，所以就顺势整理了一下。希望对大家有所帮助。
当我们对不同类型的值进行比较的时候，为了使得这些数值「可比较」（也可以称为类型的兼容性)，MySQL会做一些隐式转化（Implicittypeconversion）。
比如下面的例子：
mysql>SELECT1+'1'; ->2 mysql>SELECTCONCAT(2,'test'); ->'2test'
很明显，上面的SQL语句的执行过程中就出现了隐式转化。并且从结果们可以判断出，第一条SQL中，将字符串的“1”转换为数字1，而在第二条的SQL中，将数字2转换为字符串“2”。
MySQL也提供了CAST()函数。我们可以使用它明确的把数值转换为字符串。当使用CONCA()函数的时候，也可能会出现隐式转化，因为它希望的参数为字符串形式，但是如果我们传递的不是字符串呢：
mysql>SELECT38.8,CAST(38.8ASCHAR); ->38.8,'38.8' mysql>SELECT38.8,CONCAT(38.8); ->38.8,'38.8'
隐式转化规则
官方文档中关于隐式转化的规则是如下描述的：
IfoneorbothargumentsareNULL,theresultofthecomparisonisNULL,exceptfortheNULL-safe<=>equalitycomparisonoperator.ForNULL<=>NULL,theresultistrue.Noconversionisneeded.
- Ifbothargumentsinacomparisonoperationarestrings,theyarecomparedasstrings.
- Ifbothargumentsareintegers,theyarecomparedasintegers.
- Hexadecimalvaluesaretreatedasbinarystringsifnotcomparedtoanumber.
- IfoneoftheargumentsisaTIMESTAMPorDATETIMEcolumnandtheotherargumentisaconstant,theconstantisconvertedtoatimestampbeforethecomparisonisperformed.ThisisdonetobemoreODBC-friendly.NotethatthisisnotdonefortheargumentstoIN()!Tobesafe,alwaysusecompletedatetime,date,ortimestringswhendoingcomparisons.Forexample,toachievebestresultswhenusingBETWEENwithdateortimevalues,useCAST()toexplicitlyconvertthevaluestothedesireddatatype.
Asingle-rowsubqueryfromatableortablesisnotconsideredaconstant.Forexample,ifasubqueryreturnsanintegertobecomparedtoaDATETIMEvalue,thecomparisonisdoneastwointegers.Theintegerisnotconvertedtoatemporalvalue.TocomparetheoperandsasDATETIMEvalues,useCAST()toexplicitlyconvertthesubqueryvaluetoDATETIME. - Ifoneoftheargumentsisadecimalvalue,comparisondependsontheotherargument.Theargumentsarecomparedasdecimalvaluesiftheotherargumentisadecimalorintegervalue,orasfloating-pointvaluesiftheotherargumentisafloating-pointvalue.
- Inallothercases,theargumentsarecomparedasfloating-point(real)numbers.
翻译为中文就是：
- 两个参数至少有一个是NULL时，比较的结果也是NULL，例外是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
- 两个参数都是字符串，会按照字符串来比较，不做类型转换
- 两个参数都是整数，按照整数来比较，不做类型转换
- 十六进制的值和非数字做比较时，会被当做二进制串
- 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
- 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
- 所有其他情况下，两个参数都会被转换为浮点数再进行比较
注意点
安全问题：假如password类型为字符串，查询条件为int0则会匹配上。
mysql>select*fromtest; +----+-------+-----------+ |id|name|password| +----+-------+-----------+ |1|test1|password1| |2|test2|password2| +----+-------+-----------+ 2rowsinset(0.00sec) mysql>select*fromtestwherename='test1'andpassword=0; +----+-------+-----------+ |id|name|password| +----+-------+-----------+ |1|test1|password1| +----+-------+-----------+ 1rowinset,1warning(0.00sec) mysql>showwarnings; +---------+------+-----------------------------------------------+ |Level|Code|Message| +---------+------+-----------------------------------------------+ |Warning|1292|TruncatedincorrectDOUBLEvalue:'password1'| +---------+------+-----------------------------------------------+ 1rowinset(0.00sec)
相信上面的例子，一些机灵的同学可以发现其实上面的例子也可以做sql注入。
假设网站的登录那块做的比较挫，使用下面的方式：
SELECT*FROMusersWHEREusername='$_POST["username"]'ANDpassword='$_POST["password"]'
如果username输入的是a'OR1='1，那么password随便输入，这样就生成了下面的查询：
SELECT*FROMusersWHEREusername='a'OR1='1'ANDpassword='anyvalue'
就有可能登录系统。其实如果攻击者看过了这篇文章，那么就可以利用隐式转化来进行登录了。如下：
mysql>select*fromtest; +----+-------+-----------+ |id|name|password| +----+-------+-----------+ |1|test1|password1| |2|test2|password2| |3|aaa|aaaa| |4|55aaa|55aaaa| +----+-------+-----------+ 4rowsinset(0.00sec) mysql>select*fromtestwherename='a'+'55'; +----+-------+----------+ |id|name|password| +----+-------+----------+ |4|55aaa|55aaaa| +----+-------+----------+ 1rowinset,5warnings(0.00sec)
之所以出现上述的原因是因为：
mysql>select'55aaa'=55; +--------------+ |'55aaa'=55| +--------------+ |1| +--------------+ 1rowinset,1warning(0.00sec) mysql>select'a'+'55'; +------------+ |'a'+'55'| +------------+ |55| +------------+ 1rowinset,1warning(0.00sec)
下面通过一些例子来复习一下上面的转换规则：
mysql>select1+1; +-----+ |1+1| +-----+ |2| +-----+ 1rowinset(0.00sec) mysql>select'aa'+1; +----------+ |'aa'+1| +----------+ |1| +----------+ 1rowinset,1warning(0.00sec) mysql>showwarnings; +---------+------+----------------------------------------+ |Level|Code|Message| +---------+------+----------------------------------------+ |Warning|1292|TruncatedincorrectDOUBLEvalue:'aa'| +---------+------+----------------------------------------+ 1rowinset(0.00sec)
把字符串“aa”和1进行求和，得到1，因为“aa”和数字1的类型不同，MySQL官方文档告诉我们：
Whenanoperatorisusedwithoperandsofdifferenttypes,typeconversionoccurstomaketheoperandscompatible.
查看warnings可以看到隐式转化把字符串转为了double类型。但是因为字符串是非数字型的，所以就会被转换为0，因此最终计算的是0+1=1
上面的例子是类型不同，所以出现了隐式转化，那么如果我们使用相同类型的值进行运算呢？
mysql>select'a'+'b'; +-----------+ |'a'+'b'| +-----------+ |0| +-----------+ 1rowinset,2warnings(0.00sec) mysql>showwarnings; +---------+------+---------------------------------------+ |Level|Code|Message| +---------+------+---------------------------------------+ |Warning|1292|TruncatedincorrectDOUBLEvalue:'a'| |Warning|1292|TruncatedincorrectDOUBLEvalue:'b'| +---------+------+---------------------------------------+ 2rowsinset(0.00sec)
是不是有点郁闷呢？
之所以出现这种情况，是因为+为算术操作符arithmeticoperator这样就可以解释为什么a和b都转换为double了。因为转换之后其实就是：0+0=0了。
再看一个例子：
mysql>select'a'+'b'='c'; +-------------+ |'a'+'b'='c'| +-------------+ |1| +-------------+ 1rowinset,3warnings(0.00sec) mysql>showwarnings; +---------+------+---------------------------------------+ |Level|Code|Message| +---------+------+---------------------------------------+ |Warning|1292|TruncatedincorrectDOUBLEvalue:'a'| |Warning|1292|TruncatedincorrectDOUBLEvalue:'b'| |Warning|1292|TruncatedincorrectDOUBLEvalue:'c'| +---------+------+---------------------------------------+ 3rowsinset(0.00sec)
现在就看也很好的理解上面的例子了吧。a+b=c结果为1，1在MySQL中可以理解为TRUE，因为'a'+'b'的结果为0，c也会隐式转化为0，因此比较其实是：0=0也就是true，也就是1.
第二个需要注意点就是防止多查询或者删除数据
mysql>select*fromtest; +----+-------+-----------+ |id|name|password| +----+-------+-----------+ |1|test1|password1| |2|test2|password2| |3|aaa|aaaa| |4|55aaa|55aaaa| |5|1212|aaa| |6|1212a|aaa| +----+-------+-----------+ 6rowsinset(0.00sec) mysql>select*fromtestwherename=1212; +----+-------+----------+ |id|name|password| +----+-------+----------+ |5|1212|aaa| |6|1212a|aaa| +----+-------+----------+ 2rowsinset,5warnings(0.00sec) mysql>select*fromtestwherename='1212'; +----+------+----------+ |id|name|password| +----+------+----------+ |5|1212|aaa| +----+------+----------+ 1rowinset(0.00sec)
上面的例子本意是查询id为5的那一条记录，结果把id为6的那一条也查询出来了。我想说明什么情况呢？有时候我们的数据库表中的一些列是varchar类型，但是存储的值为‘1123'这种的纯数字的字符串值，一些同学写sql的时候又不习惯加引号。这样当进行select，update或者delete的时候就可能会多操作一些数据。所以应该加引号的地方别忘记了。
关于字符串转数字的一些说明
mysql>select'a'=0; +---------+ |'a'=0| +---------+ |1| +---------+ 1rowinset,1warning(0.00sec) mysql>select'1a'=1; +----------+ |'1a'=1| +----------+ |1| +----------+ 1rowinset,1warning(0.00sec) mysql>select'1a1b'=1; +------------+ |'1a1b'=1| +------------+ |1| +------------+ 1rowinset,1warning(0.00sec) mysql>select'1a2b3'=1; +-------------+ |'1a2b3'=1| +-------------+ |1| +-------------+ 1rowinset,1warning(0.00sec) mysql>select'a1b2c3'=0; +--------------+ |'a1b2c3'=0| +--------------+ |1| +--------------+ 1rowinset,1warning(0.00sec)
从上面的例子可以看出，当把字符串转为数字的时候，其实是从左边开始处理的。
- 如果字符串的第一个字符就是非数字的字符，那么转换为数字就是0
- 如果字符串以数字开头
- 如果字符串中都是数字，那么转换为数字就是整个字符串对应的数字
- 如果字符串中存在非数字，那么转换为的数字就是开头的那些数字对应的值
总结
以上就是这篇文章的全部内容了，如果你有其他更好的例子，或者被隐式转化坑过的情况，欢迎分享。希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。