MySQL的隐式类型转换整理总结
当我们对不同类型的值进⾏⽐较的时候，为了使得这些数值「可⽐较」（也可以称为类型的兼容性)，MySQL会做⼀些隐式转化（Implicit type conversion）。
⽐如下⾯的例⼦：
1 2 3 4mysql> SELECT1+'1';
-> 2
mysql> SELECT CONCAT(2,' test'); -> '2 test'
很明显，上⾯的SQL语句的执⾏过程中就出现了隐式转化。并且从结果们可以判断出，第⼀条SQL中，将字符串的“1”转换为数字1，⽽在第⼆条的SQL中，将数字2转换为字符串“2”。
MySQL也提供了CAST()函数。我们可以使⽤它明确的把数值转换为字符串。当使⽤CONCA()函数的时候，也可能会出现隐式转化，因为它希望的参数为字符串形式，但是如果我们传递的不是字符串呢：
1 2 3 4mysql> SELECT38.8, CAST(38.8 AS CHAR); -> 38.8, '38.8'
酒字开头的成语
mysql> SELECT38.8, CONCAT(38.8);
-> 38.8, '38.8'
隐式转化规则
官⽅⽂档中关于隐式转化的规则是如下描述的：
If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
文学If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always us
e complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, u CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be
compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, u CAST() to explicitly convert the subquery value to DATETIME.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cas, the arguments are compared as floating-point (real) numbers.
翻译为中⽂就是：
1. 两个参数⾄少有⼀个是 NULL 时，⽐较的结果也是 NULL，例外是使⽤ <=> 对两个 NULL 做⽐较时
会返回 1，这两种情况都不需要做
类型转换
2. 两个参数都是字符串，会按照字符串来⽐较，不做类型转换
3. 两个参数都是整数，按照整数来⽐较，不做类型转换
4. ⼗六进制的值和⾮数字做⽐较时，会被当做⼆进制串
5. 有⼀个参数是 TIMESTAMP 或 DATETIME，并且另外⼀个参数是常量，常量会被转换为 timestamp
6. 有⼀个参数是 decimal 类型，如果另外⼀个参数是 decimal 或者整数，会将整数转换为 decimal 后进⾏⽐较，如果另外⼀个参数是浮
点数，则会把 decimal 转换为浮点数进⾏⽐较
7. 所有其他情况下，两个参数都会被转换为浮点数再进⾏⽐较
注意点
安全问题：假如 password 类型为字符串，查询条件为 int 0 则会匹配上。
1
2 3 4 5 6 7mysql> lect* from test; +----+-------+-----------+
| id | name| password| +----+-------+-----------+
| 1 | test1 | password1 | | 2 | test2 | password2 | +----+-------+-----------+
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+----+-------+-----------+
2 rows in t(0.00 c)
mysql> lect* from test where name= 'test1'and password= 0; +----+-------+-----------+
| id | name| password|
+----+-------+-----------+
| 1 | test1 | password1 |
+----+-------+-----------+
1 row in t, 1 warning (0.00 c)
mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level| Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'password1'| +---------+------+-----------------------------------------------+
1 row in t(0.00 c)
相信上⾯的例⼦，⼀些机灵的同学可以发现其实上⾯的例⼦也可以做sql注⼊。
假设⽹站的登录那块做的⽐较挫，使⽤下⾯的⽅式：
1SELECT* FROM urs WHERE urname = '$_POST["urname"]'AND password= '$_POST["password"]'
如果urname输⼊的是a' OR 1='1，那么password随便输⼊，这样就⽣成了下⾯的查询：
1SELECT* FROM urs WHERE urname = 'a'OR1='1'AND password= 'anyvalue'
就有可能登录系统。其实如果攻击者看过了这篇⽂章，那么就可以利⽤隐式转化来进⾏登录了。如下：1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18mysql> lect* from test;
+----+-------+-----------+
| id | name| password|
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa | aaaa |
| 4 | 55aaa | 55aaaa |
+----+-------+-----------+
4 rows in t(0.00 c)
mysql> lect* from test where name= 'a'+ '55'; +----+-------+----------+
| id | name| password|
+----+-------+----------+
| 4 | 55aaa | 55aaaa |
+----+-------+----------+
卧室英文
1 row in t, 5 warnings (0.00 c)
之所以出现上述的原因是因为：1
2 3 4 5 6 7 8 9 10 11 12 13 14 15mysql> lect'55aaa'= 55;
+--------------+
| '55aaa'= 55 |
+--------------+
| 1 |
+--------------+
1 row in t, 1 warning (0.00 c) mysql> lect'a'+ '55';
+------------+
| 'a'+ '55'|
+------------+
| 55 |
+------------+
1 row in t, 1 warning (0.00 c)
下⾯通过⼀些例⼦来复习⼀下上⾯的转换规则：1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23mysql> lect1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in t(0.00 c)
mysql> lect'aa'+ 1;
+----------+
| 'aa'+ 1 |
+----------+
| 1 |
+----------+
1 row in t, 1 warning (0.00 c)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level| Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa'| +---------+------+----------------------------------------+
1 row in t(0.00 c)
把字符串“aa”和1进⾏求和，得到1，因为“aa”和数字1的类型不同，MySQL官⽅⽂档告诉我们：
When an operator is ud with operands of different types, type conversion occurs to make the operands compatible.
查看warnings可以看到隐式转化把字符串转为了double类型。但是因为字符串是⾮数字型的，所以就会被转换为0，因此最终计算的是0+1=1
上⾯的例⼦是类型不同，所以出现了隐式转化，那么如果我们使⽤相同类型的值进⾏运算呢？
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16mysql> lect'a'+ 'b';
+-----------+
| 'a'+ 'b'|
+-----------+
| 0 |
+-----------+
1 row in t,
2 warnings (0.00 c)
mysql> show warnings;
+---------+------+---------------------------------------+
| Level| Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'| | Warning | 1292 | Truncated incorrect DOUBLE value: 'b'| +---------+------+---------------------------------------+
2 rows in t(0.00 c)
冬天英文
是不是有点郁闷呢？随处可见的近义词
之所以出现这种情况，是因为+为算术操作符arithmetic operator 这样就可以解释为什么a和b都转换为double了。因为转换之后其实就是：0+0=0了。
再看⼀个例⼦：
1 2 3 4 5 6 7mysql> lect'a'+'b'='c';
+-------------+
| 'a'+'b'='c'|
+-------------+
| 1 |
+-------------+
1 row in t, 3 warnings (0.00 c)
8
9 10 11 12 13 14 15 16 17mysql> show warnings;
+---------+------+---------------------------------------+
| Level| Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'| | Warning | 1292 | Truncated incorrect DOUBLE value: 'b'| | Warning | 1292 | Truncated incorrect DOUBLE value: 'c'| +---------+------+---------------------------------------+
3 rows in t(0.00 c)
现在就看也很好的理解上⾯的例⼦了吧。a+b=c结果为1，1在MySQL中可以理解为TRUE，因为'a'+'b'的结果为0，c也会隐式转化为0，因此⽐较其实是：0=0也就是true，也就是1.
第⼆个需要注意点就是防⽌多查询或者删除数据
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 29mysql> lect* from test;
+----+-------+-----------+
| id | name| password|
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa | aaaa |
| 4 | 55aaa | 55aaaa |
| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+-----------+
6 rows in t(0.00 c)
mysql> lect* from test where name= 1212; +----+-------+----------+
| id | name| password|
+----+-------+----------+
面字怎么写| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+----------+
2 rows in t, 5 warnings (0.00 c)
mysql> lect* from test where name= '1212'; +----+------+----------+
| id | name| password|
+----+------+----------+
| 5 | 1212 | aaa |
+----+------+----------+
1 row in t(0.00 c)
上⾯的例⼦本意是查询id为5的那⼀条记录，结果把id为6的那⼀条也查询出来了。我想说明什么情况呢？有时候我们的数据库表中的⼀些列是varchar类型，但是存储的值为‘1123'这种的纯数字的字符串值，⼀些同学写sql的时候⼜不习惯加引号。这样当进⾏lect，update或者delete的时候就可能会多操作⼀些数据。所以应该加引号的地⽅别忘记了。
关于字符串转数字的⼀些说明玩具娃娃的英文
1
2
3 4 5 6 7 8 9 10 11 12 13 14 15mysql> lect'a'= 0;
+---------+
| 'a'= 0 |
+---------+
| 1 |
证明材料格式+---------+
1 row in t, 1 warning (0.00 c) mysql> lect'1a'= 1;
+----------+
| '1a'= 1 |
+----------+
| 1 |
+----------+
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 391 row in t, 1 warning (0.00 c) mysql> lect'1a1b'= 1;
+------------+
| '1a1b'= 1 |
+------------+
| 1 |
+------------+
1 row in t, 1 warning (0.00 c) mysql> lect'1a2b3'= 1;
+-------------+
| '1a2b3'= 1 |
+-------------+
| 1 |
+-------------+
1 row in t, 1 warning (0.00 c) mysql> lect'a1b2c3'= 0;
+--------------+
| 'a1b2c3'= 0 |
+--------------+
| 1 |
+--------------+
1 row in t, 1 warning (0.00 c)
从上⾯的例⼦可以看出，当把字符串转为数字的时候，其实是从左边开始处理的。
1. 如果字符串的第⼀个字符就是⾮数字的字符，那么转换为数字就是0
2. 如果字符串以数字开头
3. 如果字符串中都是数字，那么转换为数字就是整个字符串对应的数字
4. 如果字符串中存在⾮数字，那么转换为的数字就是开头的那些数字对应的值
总结
以上就是这篇⽂章的全部内容了，如果你有其他更好的例⼦，或者被隐式转化坑过的情况，欢迎分享。希望本⽂的内容对⼤家的学习或者⼯作能带来⼀定的帮助，如果有疑问⼤家可以留⾔交流。
