MySQL的隐式类型转换整理总结
前⾔
前⼏天在看到⼀篇⽂章：感觉写的很不错，再加上⾃⼰之前也对MySQL的隐式转化这边并不是很清楚，所以就顺势整理了⼀下。希望对⼤家有所帮助。
当我们对不同类型的值进⾏⽐较的时候，为了使得这些数值「可⽐较」（也可以称为类型的兼容性)，MySQL会做⼀些隐式转化（Implicit type conversion）。
⽐如下⾯的例⼦：
mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
很明显，上⾯的SQL语句的执⾏过程中就出现了隐式转化。并且从结果们可以判断出，第⼀条SQL中，
将字符串的“1”转换为数字1，⽽在第⼆条的SQL中，将数字2转换为字符串“2”。
MySQL也提供了CAST()函数。我们可以使⽤它明确的把数值转换为字符串。当使⽤CONCA()函数的时候，也可能会出现隐式转化，因为它希望的参数为字符串形式，但是如果我们传递的不是字符串呢：
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8'
mysql> SELECT 38.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 u 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.
abcc的四字词语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 则会匹配上。
mysql> lect * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
+----+-------+-----------+
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注⼊。
假设⽹站的登录那块做的⽐较挫，使⽤下⾯的⽅式：
SELECT * FROM urs WHERE urname = '$_POST["urname"]' AND password = '$_POST["password"]'
如果urname输⼊的是a' OR 1='1，那么password随便输⼊，这样就⽣成了下⾯的查询：
SELECT * FROM urs WHERE urname = 'a' OR 1='1' AND password = 'anyvalue'
就有可能登录系统。其实如果攻击者看过了这篇⽂章，那么就可以利⽤隐式转化来进⾏登录了。如下：mysql> 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)
之所以出现上述的原因是因为：
mysql> lect '55aaa' = 55;
+--------------+
| '55aaa' = 55 |
+--------------+
| 1 |
+--------------+
1 row in t, 1 warning (0.00 c)
mysql> lect 'a' + '55';
+------------+
| 'a' + '55' |
+------------+
| 55 |
+------------+
next什么意思
1 row in t, 1 warning (0.00 c)
下⾯通过⼀些例⼦来复习⼀下上⾯的转换规则：
mysql> lect 1+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
上⾯的例⼦是类型不同，所以出现了隐式转化，那么如果我们使⽤相同类型的值进⾏运算呢？
mysql> 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了。
再看⼀个例⼦：
mysql> lect 'a'+'b'='c';
+-------------+
| 'a'+'b'='c' |
+-------------+
| 1 |
+-------------+
1 row in t, 3 warnings (0.00 c)
mysql> 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.
第⼆个需要注意点就是防⽌多查询或者删除数据祖国和我
mysql> 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的时候就可能会多操作⼀些数据。所以应该加引号的地⽅别忘记了。
关于字符串转数字的⼀些说明
mysql> lect 'a' = 0;
+---------+
| 'a' = 0 |
+---------+
| 1 |
+---------+
1 row in t, 1 warning (0.00 c)
mysql> lect '1a' = 1;
+----------+
| '1a' = 1 |
+----------+
| 1 |
+----------+
1 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. 如果字符串中存在⾮数字，那么转换为的数字就是开头的那些数字对应的值
总结
以上就是这篇⽂章的全部内容了，如果你有其他更好的例⼦，或者被隐式转化坑过的情况，欢迎分享。希望本⽂的内容对⼤家的学习或者⼯作能带来⼀定的帮助，如果有疑问⼤家可以留⾔交流。
