我们都知道在mysql中存在隐式转化,其官方文档如下::
- 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.
如其中最后一点所述,In all other cases, the arguments are compared as floating-point (real) numbers(所有其他情况下,两个参数都会被转换为浮点数再进行比较)。也就是说在默认情况下,若两边类型不相等,会转化成数字类型来比较。这里我举个例子。
我有一张表test,字段如下,a字段为主键且类型为int
表中数据如图所示
现在我要执行以下sql
select * from test where a='a';
执行结果如下:
为什么会是这样呢?那让我们再执行一句sql
select 'a' = 0
可以看到居然是1,也就是说两个值是相等的,这就是上面所说的,mysql将字符’a’,转化成了数字0
所以说这种情况在开发时一定要注意,否则将造成数据结果的严重错误!