在MySQL中的SELECT查询中使用LIKE进行切换 [英] SWITCH with LIKE inside SELECT query in MySQL

问题描述

我有这个标签表

CREATE TABLE IF NOT EXISTS `Tags` (
   `id_tag` int(10) unsigned NOT NULL auto_increment,
   `tag` varchar(255) default NULL,
   PRIMARY KEY  (`id_tag`),
   UNIQUE KEY `tag` (`tag`),
   KEY `id_tag` (`id_tag`),
   KEY `tag_2` (`tag`),
   KEY `tag_3` (`tag`),
   KEY `tag_4` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2937 ;

INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
   (1816, '(class'),
   (2642, 'class\r\n\r\nâ?¬35'),
   (1906, 'class\r\nif'),
   (1398, 'class'),
   (2436, 'class)'),
   (1973, 'class:\n1.'),
   (2791, 'classes'),
   (1325, 'New'),
   (2185, 'pack'),
   (1905, 'packed'),
   (1389, 'WebClass');

我想获取标记与关键字class或pack或new匹配的所有记录，以及另一个字段，该字段指示3个关键字中的哪个实际上与标记字段匹配.

I want to fetch all records where tag matches keywords class or pack or new, along with another field which indicates which of the 3 keywords actually matched with the tag field.

以下查询未给出正确的结果 查询1

The following query does not give correct results Query 1

select id_tag,
case tag 
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
end as matching_tag 
from Tags 
where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"

我必须在箱子内部使用like.否则，完成匹配工作.以下查询有效:-

I have to use the like inside the case. Otherwise complete matching works. The following query works:-

查询2

select id_tag,
case tag 
   when "class" then "class" 
   when "new" then "new"
   when "pack" then "pack"
end as matching_tag 
from Tags 
where tag = "class" OR tag = "new" OR tag = "pack"

查询1.出了什么问题.请帮助.

What is wrong with the query 1. Please help.

推荐答案

MySQL支持两种大小写变体，您在查询2中使用的变体不太灵活，但仅支持单个变量的相等性.另一个版本在大小写之后不指定任何变量，那么条件不必只是相等:

Mysql supports two variants of case, the one you use in query 2 is less flexible but supports only equality on a single variable. The other version specifies no variable after case and then conditions need not be only equality:

select id_tag,
case  
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
end as matching_tag 
from Tags 
where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"

有关更多信息，请参见文档详细信息

See documentation for further details

以下是有关查询#1为何返回其返回内容的更多解释:

Here's a bit more explanation on why your query #1 returned what it returned:

case tag
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
end as matching_tag

期望获得一个文字值，以便在when ... then之间进行比较 在上述情况下，表达式tag LIKE "%class%"，tag LIKE "%new%"和tag LIKE "%pack%"均在实际案例比较之前进行求值. 但是(！)，发生的事情是它们变为0或1，并且与tag的值比较时，它是将与任何char匹配的第一个0值(char将转换为0)-这与结果一致您的第一个查询.

expects to get a literal value for comparison between when ... then In the above case the expressions tag LIKE "%class%", tag LIKE "%new%" and tag LIKE "%pack%" are all evaluated before the actual case comparison. However (!), what happens is that they become either 0 or 1 and when compared to the value of tag it is the first value of 0 that will match any char (char will get cast to 0) - this is consistent with the results of your first query.

以下是显示相关表达式的逻辑值的查询:

Here's a query that shows the logical values for the relevant expressions:

select id_tag, tag LIKE "%class%", tag LIKE "%new%", tag = 0, case tag     when tag LIKE "%class%" then "class"     when tag LIKE "%new%" then "new"    when tag LIKE "%pack%" then "pack" end as matching_tag  from Tags  where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%";

这就是为什么您得到意外结果的原因；静默的CAST是这里的标准陷阱.

That's why you get unexpected results; the silent CAST is a standard pitfall here.

这篇关于在MySQL中的SELECT查询中使用LIKE进行切换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！