mysql - 尽管没有通配符，查询仍返回 "LIKE"结果？

Question

我真的很困惑。运行以下查询:

SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = 5

返回也以“5”开头的行，尽管我既不使用 LIKE 也不使用 % 通配符。怎么会？

size 字段的类型为 VARCHAR。

Answer 1

那是因为您使用的是数字数据和 varchar 数据之间的比较。 MySQL 会将您的列隐式转换为 double，结果为 5。看这个简单的测试数据:

mysql> select * from test;+-----------------+| name            |+-----------------+| 5               || 5 and some crap |+-----------------+2 rows in set (0.00 sec)

Now, "good" way: compare strings:

mysql> select * from test where name = '5';+------+| name |+------+| 5    |+------+1 row in set (0.00 sec)

And "bad" way: compare integers:

mysql> select * from test where name = 5;+-----------------+| name            |+-----------------+| 5               || 5 and some crap |+-----------------+2 rows in set, 1 warning (0.05 sec)

-and here is your reason:

+---------+------+-----------------------------------------------------+| Level   | Code | Message                                             |+---------+------+-----------------------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: '5 and some crap' |+---------+------+-----------------------------------------------------+1 row in set (0.00 sec)

Finally, to understand, why is it so:

SELECT 
  CAST('5' AS DECIMAL) AS 5d, 
  CAST('5 and some crap' AS DECIMAL) AS 5sd, 
  CAST('5' AS DECIMAL) = CAST('5 and some crap' AS DECIMAL) AS areEqual;

将导致:

+----+-----+----------+| 5d | 5sd | areEqual |+----+-----+----------+|  5 |   5 |        1 |+----+-----+----------+1 row in set (0.00 sec)

-如您所见，只是截断了不重要的部分(如上面的警告消息中所述)