通过条件为IN()的varchar列进行选择，并且int值返回所有行 [英] Select by varchar column with IN() part in condition and int value returns all rows

问题描述

有人可以通过该查询解释我的原因吗?

Can someone explain me why with that query:

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

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

it return me all rows from tags table, but when I put 2016 into quotes it works good ?

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)相同的错误:

I also get the same error as Roland Bouman got:

Truncated incorrect DOUBLE value: 'willa-lentza'

推荐答案

请勿将引号和 IN列表中未引用的值，因为 引用值的比较规则 (例如字符串)和未加引号的值 (例如数字)有所不同.混合类型 因此可能导致不一致 结果.

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

这篇关于通过条件为IN()的varchar列进行选择，并且int值返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！