MySQL的隐式类型转换整理总结

前言

前几天在看到一篇文章：价值百万的 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 use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use 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, use 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 cases, 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> select * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
+----+-------+-----------+
2 rows in set (0.00 sec)

mysql> select * from test where name = 'test1' and password = 0;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
+----+-------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message   |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'password1' |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

相信上面的例子，一些机灵的同学可以发现其实上面的例子也可以做sql注入。

假设网站的登录那块做的比较挫，使用下面的方式：

SELECT * FROM users WHERE username = '$_POST["username"]' AND password = '$_POST["password"]'

如果username输入的是a' OR 1='1，那么password随便输入，这样就生成了下面的查询：

SELECT * FROM users WHERE username = 'a' OR 1='1' AND password = 'anyvalue'

就有可能登录系统。其实如果攻击者看过了这篇文章，那么就可以利用隐式转化来进行登录了。如下：

mysql> select * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa | aaaa |
| 4 | 55aaa | 55aaaa |
+----+-------+-----------+
4 rows in set (0.00 sec)

mysql> select * from test where name = 'a' + '55';
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 4 | 55aaa | 55aaaa |
+----+-------+----------+
1 row in set, 5 warnings (0.00 sec)

之所以出现上述的原因是因为：

mysql> select '55aaa' = 55;
+--------------+
| '55aaa' = 55 |
+--------------+
| 1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a' + '55';
+------------+
| 'a' + '55' |
+------------+
| 55 |
+------------+
1 row in set, 1 warning (0.00 sec)

下面通过一些例子来复习一下上面的转换规则：

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)

mysql> select 'aa' + 1;
+----------+
| 'aa' + 1 |
+----------+
| 1 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message  |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

把字符串“aa”和1进行求和，得到1，因为“aa”和数字1的类型不同，MySQL官方文档告诉我们：

     When an operator is used with operands of different types, type conversion occurs to make the operands compatible.

查看warnings可以看到隐式转化把字符串转为了double类型。但是因为字符串是非数字型的，所以就会被转换为0，因此最终计算的是0+1=1

上面的例子是类型不同，所以出现了隐式转化，那么如果我们使用相同类型的值进行运算呢？

mysql> select 'a' + 'b';
+-----------+
| 'a' + 'b' |
+-----------+
|  0 |
+-----------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message    |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

是不是有点郁闷呢？

之所以出现这种情况，是因为+为算术操作符arithmetic operator 这样就可以解释为什么a和b都转换为double了。因为转换之后其实就是：0+0=0了。

再看一个例子：

mysql> select 'a'+'b'='c';
+-------------+
| 'a'+'b'='c' |
+-------------+
|  1 |
+-------------+
1 row in set, 3 warnings (0.00 sec)

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 set (0.00 sec)

现在就看也很好的理解上面的例子了吧。a+b=c结果为1，1在MySQL中可以理解为TRUE，因为'a'+'b'的结果为0，c也会隐式转化为0，因此比较其实是：0=0也就是true，也就是1.

第二个需要注意点就是防止多查询或者删除数据

mysql> select * 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 set (0.00 sec)

mysql> select * from test where name = 1212;
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+----------+
2 rows in set, 5 warnings (0.00 sec)

mysql> select * from test where name = '1212';
+----+------+----------+
| id | name | password |
+----+------+----------+
| 5 | 1212 | aaa |
+----+------+----------+
1 row in set (0.00 sec)

​上面的例子本意是查询id为5的那一条记录，结果把id为6的那一条也查询出来了。我想说明什么情况呢？有时候我们的数据库表中的一些列是varchar类型，但是存储的值为‘1123'这种的纯数字的字符串值，一些同学写sql的时候又不习惯加引号。这样当进行select，update或者delete的时候就可能会多操作一些数据。所以应该加引号的地方别忘记了。

关于字符串转数字的一些说明

mysql> select 'a' = 0;
+---------+
| 'a' = 0 |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> select '1a' = 1;
+----------+
| '1a' = 1 |
+----------+
| 1 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> select '1a1b' = 1;
+------------+
| '1a1b' = 1 |
+------------+
|  1 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> select '1a2b3' = 1;
+-------------+
| '1a2b3' = 1 |
+-------------+
|  1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a1b2c3' = 0;
+--------------+
| 'a1b2c3' = 0 |
+--------------+
|  1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

从上面的例子可以看出，当把字符串转为数字的时候，其实是从左边开始处理的。

1. 如果字符串的第一个字符就是非数字的字符，那么转换为数字就是0
2. 如果字符串以数字开头
3. 如果字符串中都是数字，那么转换为数字就是整个字符串对应的数字
4. 如果字符串中存在非数字，那么转换为的数字就是开头的那些数字对应的值
   

总结

以上就是这篇文章的全部内容了，如果你有其他更好的例子，或者被隐式转化坑过的情况，欢迎分享。希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。

# mysql隐式转换  # mysql隐式类型转换  # mysql  # 5.7  # 隐式转换  # 一文带你搞懂MySQL中的隐式类型转换和显式类型转换  # Mysql隐式类型转换方式  # MySQL隐式类型转换导致索引失效的解决  # MySQL隐式类型的转换陷阱和规则  # 一文揭秘MySQL导致索引失效的隐式类型转换规则与案例  # 转换为  # 隐式  # 都是  # 的是  # 另外一个  # 是因为  # 有一个  # 不做  # 这篇文章  # 浮点数  # 则会  # 就可以  # 出现了  # 我想  # 就会  # 文档  # 很好  # 也会  # 第一个  # 就有 

相关文章： 子杰智能建站系统｜零代码开发与AI生成SEO优化指南  定制建站如何定义？其核心优势是什么？  公司网站制作价格怎么算,公司办个官网需要多少钱？  全景视频制作网站有哪些,全景图怎么做成网页？  php8.4新语法match怎么用_php8.4match表达式替代switch【方法】  高防网站服务器：DDoS防御与BGP线路的AI智能防护方案  陕西网站制作公司有哪些,陕西凌云电器有限公司官网？  seo网站制作优化,网站SEO优化步骤有哪些？  定制建站哪家更专业可靠？推荐榜单揭晓  如何通过网站建站时间优化SEO与用户体验？  官网网站制作腾讯审核要多久,联想路由器newifi官网  c# 在高并发场景下，委托和接口调用的性能对比  香港服务器建站指南：外贸独立站搭建与跨境电商配置流程  建站之星如何通过成品分离优化网站效率？  如何在景安云服务器上绑定域名并配置虚拟主机？  制作充值网站的软件,做人力招聘为什么要自己交端口钱？  如何破解联通资金短缺导致的基站建设难题？  红河网站制作公司,红河事业单位身份证如何上传？  存储型VPS适合搭建中小型网站吗？  娃派WAP自助建站：免费模板+移动优化，快速打造专业网站  网站设计制作公司地址,网站建设比较好的公司都有哪些？  建站DNS解析失败？如何正确配置域名服务器？  如何快速登录WAP自助建站平台？  网站制作知乎推荐,想做自己的网站用什么工具比较好？  香港服务器网站推广：SEO优化与外贸独立站搭建策略  大连网站设计制作招聘信息,大连投诉网站有哪些？  如何彻底卸载建站之星软件？  浅析上传头像示例及其注意事项  网站制作多少钱一个,建一个论坛网站大约需要多少钱？  北京网页设计制作网站有哪些,继续教育自动播放怎么设置？  武汉网站制作费用多少,在武汉武昌，建面100平方左右的房子，想装暖气片，费用大概是多少啊？  做企业网站制作流程,企业网站制作基本流程有哪些？  php json中文编码为null的解决办法  建站之星如何一键生成手机站？  如何通过服务器快速搭建网站？完整步骤解析  建站之星各版本价格是多少？  微信小程序 input输入框控件详解及实例（多种示例）  图册素材网站设计制作软件,图册的导出方式有几种？  中山网站推广排名,中山信息港登录入口？  简易网站制作视频教程,使用记事本编写一个简单的网页html文件？  代购小票制作网站有哪些,购物小票的简要说明？  宁波免费建站如何选择可靠模板与平台？  如何使用Golang table-driven基准测试_多组数据测量函数效率  实惠建站价格推荐：2025年高性价比自助建站套餐解析  制作宣传网站的软件,小红书可以宣传网站吗？  如何在云主机快速搭建网站站点？  公司门户网站制作公司有哪些,怎样使用wordpress制作一个企业网站？  定制建站模板如何实现SEO优化与智能系统配置？18字教程  如何通过远程VPS快速搭建个人网站？  建站之星在线版空间：自助建站+智能模板一键生成方案