弱类型语言携手Mysql隐式转换带来的阵痛

wwf 发表于 | 分类于

背景

程序（php写）中的一个变量传入mysql的where条件查询；发现变量改变查询的结果不变。

解析sql原生语句：

1
select * from `flow_data_logs` where `msgid` = 258041935053193246 limit 1;
1
的结果  msgid 不是 258041935053193246

改写sql:

1
select * from `flow_data_logs` where `msgid` = '258041935053193246' limit 1;
1
的结果  msgid 是 258041935053193246

心路历程

mysql 隐式转换和php弱类型带来多次坑，但是每次依旧采的很欢乐。
本次查询耗时接近两个小时复盘如下：

  1. 发现问题，想办法去定位
  2. 先pull代码重启进程，发现无效
  3. 加断点日志，添加参数和pid，发现数据是先被读出来然后才能写进去，而且是同一个进程，懵逼了
  4. 排除代码有没有在另外机器上执行
  5. 仔细查看日志发现查出的msgid和传入的msgid不是一条数据
  6. 定位到问题，担心是不是超出integer范围溢出了，查询之后排除
  7. 传入参数强制转换成string,问题解决；查bug两小时解决bug两秒钟。

也说php弱类型语言

PHP 弱类型语言变量在定义中不需要明确的类型定义，变量类型是根据使用变量的使用变量的上下文决定，也就是说，如果把一个 string 值赋给变量 $var，$var 就成了一个 string。如果又把一个integer 赋给 $var，那它就成了一个integer

1、php中的整型
  • 整型数的字长和平台有关,且 PHP 不支持无符号的 integer
  • 最大值常量 PHP_INT_MAX ，最小值常量 PHP_INT_MIN （需PHP大于 7.0.0）表示
  • 32位平台下 最大 2147483647 64位平台最大9223372036854775807
  • 如果给定的数超出了integer 的范围，将会被解释为 float

示例64位系统下的常量

1
2
3
4
5
❯ php -r "echo PHP_INT_MAX;"
9223372036854775807%

❯ php -r "echo PHP_INT_MIN;"
-9223372036854775808%

示例 64位系统下的整数溢出

1
2
❯ php -r "var_dump(9223372036854775808);"
float(9.2233720368548E+18)
2、php类型转换

php 中类型强制转换：在要转换的变量之前加上用括号括起来的目标类型

  • (int), (integer) - 转换为整形 integer
  • (bool), (boolean) - 转换为布尔类型 boolean
  • (float), (double), (real) - 转换为浮点型 float
  • (string) - 转换为字符串 string
  • (array) - 转换为数组 array
  • (object) - 转换为对象 object
  • (unset) - 转换为 NULL

【 tips】 (unset) 转换在 PHP 7.2.0 中已被废弃。请注意 (unset) 转换等于将值赋予 NULL。(unset) 转换将在 PHP 8.0.0 中被移除

再说 MySQL 的隐式转换

1、MySQL 中对隐式转换的定义：

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

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。

2、比较操作时 MySQL 隐式类型转换规则
  • 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
  • 两个参数都是字符串，会按照字符串来比较，不做类型转换
  • 两个参数都是整数，按照整数来比较，不做类型转换
  • 十六进制的值和非数字做比较时，会被当做二进制串
  • 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
  • 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
  • 所有其他情况下，两个参数都会被转换为浮点数再进行比较

示例int 转换成浮点数 sql

1
2
3
4
5
6
7
8
SELECT '258041935053193233' = 258041935053193246;
# 结果 是 1

SELECT cast('258041935053193233' + 0.0 as unsigned);
# 结果 258041935053193248

SELECT cast('258041935053193246' + 0.0 as unsigned);
# 结果 258041935053193248
3、参考文献：

MYSQL官网文档

小米信息部技术团队

回归问题本质

  1. php 拿到值为整数的$msgid变量，变量类型为int且没有溢出
  2. php 把int类型的 $msgid传给myslq作为 where msgid=$msgid 条件（myslq 中msg为varchar类型）
  3. mysql 把int msgid转换成float
  4. 查出的结果异常