在 MySQL 中的 SELECT 查询中使用 LIKE 进行切换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3082894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SWITCH with LIKE inside SELECT query in MySQL
提问by Sandeepan Nath
I have this Tags table
我有这个标签表
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');
I want to fetch all records where tag matches keywords classor packor new, along with another field which indicates which of the 3 keywords actually matched with the tag field.
我想取其中标签匹配关键字的所有记录class或pack或new与这表明该3个关键字的实际与标记字段匹配的另一场一起。
The following query does not give correct results Query 1
以下查询未给出正确结果查询 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%"
I have to use the like inside the case. Otherwise complete matching works. The following query works:-
我必须在案例中使用类似的东西。否则完成匹配工作。以下查询有效：-
Query 2
查询 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"
What is wrong with the query 1. Please help.
查询有什么问题 1. 请帮忙。
回答by Unreason
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:
Mysql 支持两种大小写变体，您在查询 2 中使用的一种不太灵活，但仅支持单个变量的相等性。另一个版本在 case 之后没有指定变量，然后条件不必只是相等：
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 documentationfor further details
有关更多详细信息，请参阅文档
EDIT: Here's a bit more explanation on why your query #1 returned what it returned:
编辑：这里有更多关于为什么您的查询 #1 返回它返回的内容的解释：
case tag
when tag LIKE "%class%" then "class"
when tag LIKE "%new%" then "new"
when tag LIKE "%pack%" then "pack"
end as matching_tag
expects to get a literal value for comparison between when ... thenIn 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.
期望得到一个字面值，以便when ... then在上述情况下的表达式tag LIKE "%class%",tag LIKE "%new%"和之间进行比较，并tag LIKE "%pack%"在实际情况比较之前进行评估。但是（！），发生的情况是它们变为 0 或 1，并且与 tag 的值相比，它是第一个匹配任何字符的 0 值（字符将被强制转换为 0）——这与结果一致您的第一个查询。
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%";
That's why you get unexpected results; the silent CAST is a standard pitfall here.
这就是为什么你会得到意想不到的结果；无声 CAST 是这里的标准陷阱。
回答by V. Kovpak
Just want remind, about else clause:
只是想提醒一下，关于 else 子句：
case
when tag LIKE "%class%" then "class"
when tag LIKE "%new%" then "new"
when tag LIKE "%pack%" then "pack"
else "no one"
end as matching_tag
