mysql - 按条件中包含 IN() 部分的 varchar 列选择，int 值返回所有行

Question

有人可以用这个查询向我解释原因吗:

SELECT * FROM `tags` WHERE (tag IN ('willa-lentza', 2016))

它会返回 tags 表中的所有行，但是当我将 2016 放入引号中时效果很好吗？

tag 列是 varchar 类型。

示例环境

CREATE TABLE  `tags` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tag` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

INSERT INTO `tags` (`id`, `tag`) VALUES
  (1, '2016'),
  (2, 'plum'),
  (3, 'banana'),
  (4, 'apple'),
  (5, 'willa-lentza');

我也遇到了和 Roland Bouman 一样的错误:

Truncated incorrect DOUBLE value: 'willa-lentza'

Answer 1

You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in