mysql，准备好的语句和自动类型转换 [英] mysql, prepared statements, and automatic type conversion

问题描述

使用正则语句和预准备语句执行完全相同的查询时，我得到不同的结果，我认为这是类型转换错误.

I am getting different results performing the exact same query using regular statements and prepared statements, and I think it's a type conversion bug.

mysql> show columns from server where field = "vlan";
+-------------+--------+------+-----+---------+-------+
| Field       | Type   | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+-------+
| vlan        | int(5) | YES  | MUL | NULL    |       |
+-------------+--------+------+-----+---------+-------+

mysql> select hostname from server where `vlan` = '184.182' limit 1;
Empty set (0.00 sec)

mysql> prepare stupid from "select hostname from server where `vlan` = ? limit 1";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @vlan = '184.182';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stupid using @vlan;
+-------------------+
| hostname          |
+-------------------+
| web20.servers.com |
+-------------------+
1 row in set (0.00 sec)

vlan的实际值是184

对于准备好的语句和常规语句，mysql处理类型转换的方式看起来是不同的吗?那有意义吗?我该如何解决?

it looks like the way mysql is handling type conversions is different for prepared statements and regular statements? does that make sense? how do i fix this?

推荐答案

准备好的语句参数的预期数据类型在语句准备时确定，并且在执行语句之前将类型转换为该数据类型.

The expected data type of prepared statement parameters is determined upon statement preparation, and type conversion to that data type takes place prior to statement execution.

在您的示例中，应使用整数参数；因此，在执行该语句之前，将提供的字符串强制转换为整数(184)，并且对于匹配记录，整数列vlan与参数之间的比较成功.

In your example, an integer parameter is expected; therefore the provided string is cast to an integer (184) before the statement is executed, and the comparison between the integer column vlan and the parameter is successful for the matching record.

相比之下，"regular"语句将整数列与字符串进行比较；因此，将参数作为浮点数进行比较，并且没有记录具有匹配的vlan.

The "regular" statement, by contrast, compares the integer column with a string; therefore the arguments are compared as floating point numbers, and no record has a matching vlan.

为避免这种情况，请确保在准备时无法确定数据类型(或确定的数据类型不会丢失任何信息)-例如:

To avoid this situation, ensure that the data type cannot be determined upon preparation (or that the determined data type does not lose any information) - for example:

prepare not_so_stupid from
  "select hostname from server where `vlan` = CAST(? AS CHAR) limit 1"
;

这篇关于mysql，准备好的语句和自动类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！