mysql查询的一些现象，其中条件比较char归档为int 0

Have a table ,

有桌子，

root@localhost:[test]05:35:05>desc t;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| studio_id | char(32) | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+

have two rows:

有两行：

root@localhost:[test]05:35:29>select * from t;
+----+----------------------------------+
| id | studio_id                        |
+----+----------------------------------+
|  1 | foo1                             |
|  2 | 299a0be4a5a79e6a59fdd251b19d78bb |
+----+----------------------------------+

found some strange query phenomenon, e.g.

发现一些奇怪的查询现象，例如

# I can understand this
root@localhost:[test]05:37:00>select * from t where studio_id = '0';
Empty set (0.00 sec)
# I also understand this
root@localhost:[test]05:41:45>select * from t where studio_id = 1;
Empty set, 2 warnings (0.00 sec)


# but I can't understand this
root@localhost:[test]05:36:21>select * from t where studio_id = 0;
+----+-----------+
| id | studio_id |
+----+-----------+
|  1 | foo1      |
+----+-----------+

why could have record returned, and why only foo1 returned , what about 299a0be4a5a79e6a59fdd251b19d78bb?

为什么可能有记录返回，为什么只有foo1返回，299a0be4a5a79e6a59fdd251b19d78bb呢？

root@localhost:[test]05:38:20>select * from t where studio_id <> 0;
+----+----------------------------------+
| id | studio_id                        |
+----+----------------------------------+
|  2 | 299a0be4a5a79e6a59fdd251b19d78bb |
+----+----------------------------------+

1 个解决方案

#1

The cause is how mysql silently converts text to numbers to evaluate the number=text expression as described in mysql's documentation on type conversion in expression evaluation.

原因是mysql如何将文本静默转换为数字以评估number = text表达式，如mysql关于表达式求值中类型转换的文档中所述。

Number=text comparison expressions are evaluated by converting both operands to floating point numbers.

Number =文本比较表达式通过将两个操作数转换为浮点数来计算。

Text is converted to a number by evaluating the characters from left to right. As long as the characters can be evaluated as a valid number (sign, numbers, decimal point, etc), mysql considers it as a number. The conversion stops in the moment mysql encounters a character that cannot be used in a number (such as a letter), or would result in an ivalid number (such as a 2nd sign).

通过从左到右评估字符将文本转换为数字。只要字符可以被评估为有效数字（符号，数字，小数点等），mysql就会将其视为一个数字。转换在mysql遇到无法在数字中使用的字符（例如字母）的时刻停止，或者会导致无效的数字（例如第二个符号）。

Text 'foo1' is converted to 0 because its leftmost character is a letter.

文本'foo1'被转换为0，因为它的最左边的字符是一个字母。

Text '299a0be4a5a79e6a59fdd251b19d78bb' is converted to 299 because it starts with the characters 299 and then comes the letter a that cannot be interpreted as a number.

文本'299a0be4a5a79e6a59fdd251b19d78bb'被转换为299，因为它以字符299开头，然后是字母a，不能被解释为数字。

#1