MYSQL IN子句仅返回第一个值组合的结果

Question

我以前使用过这个查询：

SELECT * 
FROM stone_list 
WHERE type IN ('ALEXANDRITE','AMETHYST') 
   AND shape IN ('OVAL') ORDER BY type, shape

它在过去对我很有效，但这次它做了一些非常奇怪的事情。当shape类型只有一个值时，查询只返回IN为ALEXANDRITE且shape为OVAL的结果，即使还有type = AMETHYST and shape = OVAL的行也是如此。

现在，当我添加第二个形状时，例如shape IN ('OVAL','ROUND')，它将同时返回type = ALEXANDRITE and type = AMETHYST和shape = OVAL and shape = ROUND的值(因此，它按预期工作)。

真的胜过我。

有谁有什么想法吗？

谢谢!

Answer 1

查询没有任何错误，所以我只能假设您在问题中忽略或省略了一些东西。

mysql> select * from stone_list;
+----+-------------+--------+
| id | type        | shape  |
+----+-------------+--------+
|  1 | ALEXANDRITE | OVAL   |
|  2 | ALEXANDRITE | ROUND  |
|  3 | BASILIC     | OVAL   |
|  4 | AMETHYST    | SQUARE |
|  5 | AMETHYST    | ROUND  |
|  6 | AMETHYST    | OVAL   |
+----+-------------+--------+
6 rows in set (0.00 sec)


mysql> SELECT * FROM stone_list WHERE type IN ('ALEXANDRITE','AMETHYST') AND shape IN ('OVAL') ORDER BY type, shape
    -> ;
+----+-------------+-------+
| id | type        | shape |
+----+-------------+-------+
|  1 | ALEXANDRITE | OVAL  |
|  6 | AMETHYST    | OVAL  |
+----+-------------+-------+
2 rows in set (0.00 sec)


mysql> describe stone_list;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type  | varchar(50)      | YES  |     | NULL    |                |
| shape | varchar(50)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)