MySQL中的奇怪类型转换行为

Question

以下是代码示例

mysql> SELECT id FROM tbl WHERE id = '1h';
+----+
| id |
+----+
|  1 |
+----+
1 row in set

确实有一个ID为1的字段（但不是'1h'）。
以下内容摘自MySQL文档：http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

mysql> SELECT 1 > '6x';
    -> 0
mysql> SELECT 7 > '6x';
    -> 1

这个bug已经有记录了。问题是什么导致了这种行为，如何进行更正以避免将char符号转换为字符串？我可以像下面这样转换所有字段值：

mysql> SELECT id FROM tbl WHERE cast(`id`, BINARY) = '1h';

但我不太喜欢这种变体。

- kos

---

你的 id 列的类型是什么？我猜它是数字类型？我真的没有看到任何错误。如果你强制 MySQL 在不同类型之间进行转换，你需要了解它是如何进行类型转换的。 - Dancrumb

抱歉。是的，它是int类型。我没有显式地进行转换。 - kos

MYSQL端有没有解决这个问题的方法？我特别注意到这个问题，尤其是当一个页面用用户提供的输入生成一个数字列的查询时。显然，用户可以输入“1h”，而MySQL仍将读取“1”并返回结果。如果没有MYSQL设置或排序类型来覆盖强制转换，那么我们就只能为每个用户提供的 数字参数检查数据类型了吗？那将需要大量的代码审查和重构！ - alds

Answer 1

这不是一个错误。

解决方案不是使用字符串值作为条件查询数字列。

永远不要依赖隐式类型转换。

- kapa

---

实际上，解决方案是永远不要依赖于隐式类型转换。 - user330315

@a_horse_with_no_name，如果您不介意的话，我会把这个加到我的答案中。 - kapa

@kos：你用什么语言来生成查询？大多数好的库都应该为你处理这个问题。 - Dancrumb

@Dancrumb（和其他人），我不依赖它。这是CodeIgniter的ActiveRecord行为。 - kos

@kos：PostgreSQL会直接抛出异常。MySQL认为自己很聪明，试图返回一些东西——即使它是错误的。 - user330315

Answer 2

你所提出的所有观察结果都不是bug，而是依赖隐式类型转换的结果。

在你提出的所有例子中，你要求MySQL将一个字符串转换为整数。如果你阅读了你链接到的页面，你会发现MySQL遵循一些规则来实现这一点。因此

'1h' -> 1
'6x' -> 6
'x6' -> 0

所以，如果你遵循这些规则，你就没问题了。

更好的方法是，不要让MySQL处于需要执行这些转换的位置。这种情况通常指向系统其他地方存在某种逻辑错误。

- Dancrumb