mysql - 在 WHERE 关闭中转换不一致的 int/varchar

Question

我有一个包含 id 列 (VARCHAR(32)) 的数据库表。

我不明白为什么这个查询会这样:

SELECT * FROM my_table where `id`=40000; 

我返回了 1 行，id 为 4e4a2269774a0032fbca2b4692b560b3

---

我做了一些测试，我有以下行为

SELECT *, case when (cast(`id` as unsigned)=40000) then '40000' else 'not 40000' end FROM my_table where `id`=40000; 

同一行返回“not 40000”。

SELECT * FROM my_table where `id`=CAST(40000 as char); 

没有返回行。

MySQL 版本为 8.0.15-commercial。

当 id 显然不是我问的那个时，为什么返回这一行？类型转换和比较是如何完成的？

---

为了重现这种奇怪的行为，这里有一个 fiddle ，感谢@MadhurBhaiya https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/1

Answer 1

感谢@RaymondNijland 找到，请参阅 https://www.db-fiddle.com/f/mwQ9hUVgDisBBBajCFKVHQ/3

---

SELECT CAST('4e4a2269774a0032fbca2b4692b560b3' as DECIMAL(5,0));

返回 40000，因为 4e4 = 4*10^4 = 40000，则忽略字符串的其他部分。

所以这个查询:

SELECT `id` FROM my_table where `id`=40000; 

似乎等同于这个查询

SELECT `id` FROM my_table where CAST(`id` as DECIMAL(5,0))=40000; 

结果在某种程度上是合乎逻辑的。

---

MySQL Documentation解释这个:

[...]

• 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. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.

4e4[INSERT ANYTHING NON NUMERIC HERE]... 被 MySQL 与值 4e4 (= 40000)。