mysql查询在条件比较char与int 0时出现的一些现象 [英] some phenomenon of mysql query in where condition compare char filed with int 0
问题描述
有一张桌子,
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 | |
+-----------+----------+------+-----+---------+----------------+
具有两行:
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 |
+----+-----------+
为什么可以返回记录,为什么只返回foo1
,299a0be4a5a79e6a59fdd251b19d78bb
呢?
why could have record returned, and why only foo1
returned , what about 299a0be4a5a79e6a59fdd251b19d78bb
?
root@localhost:[test]05:38:20>select * from t where studio_id <> 0;
+----+----------------------------------+
| id | studio_id |
+----+----------------------------------+
| 2 | 299a0be4a5a79e6a59fdd251b19d78bb |
+----+----------------------------------+
推荐答案
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.
Number =文本比较表达式是通过将两个操作数都转换为浮点数来求值的.
Number=text comparison expressions are evaluated by converting both operands to floating point numbers.
通过从左到右评估字符将文本转换为数字.只要可以将字符评估为有效数字(符号,数字,小数点等),mysql就会将其视为数字.当mysql遇到无法在数字中使用的字符(例如字母)或将导致无效数字(例如第二个符号)时,转换将停止.
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).
文本'foo1'
被转换为0,因为其最左边的字符是字母.
Text 'foo1'
is converted to 0 because its leftmost character is a letter.
文本'299a0be4a5a79e6a59fdd251b19d78bb'
被转换为299,因为它以字符299开头,然后出现字母a
,该字母不能解释为数字.
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.
这篇关于mysql查询在条件比较char与int 0时出现的一些现象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!