MySQL IN 和 LIKE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3732246/
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
MySQL IN with LIKE
提问by James T
How would I use a IN table with like? So that I could use % in them? By in I mean:
我将如何使用类似的 IN 表？这样我就可以在其中使用 % 了吗？我的意思是：
SELECT fields
FROM table
WHERE age = "50"
AND name IN ("tim", "bob", "nancy", "john");
I already tried:
我已经尝试过：
SELECT fields
FROM table
WHERE age = "50"
AND name LIKE ("2010-09-17%", "2010-09-16%")
But it gave the error "Operand should contain 1 column(s)"
但它给出了错误“操作数应该包含 1 列”
回答by Ned Batchelder
You can use a number of LIKE expressions:
您可以使用许多 LIKE 表达式：
SELECT fields
FROM table
WHERE age = "50"
AND (
name LIKE "2010-09-17%"
OR name LIKE "2010-09-16%"
);
or you can use a regex:
或者您可以使用正则表达式：
SELECT fields
FROM table
WHERE age = "50"
AND name REGEXP "2010-09-17.*|2010-09-16.*";
or, cleverly
或者，巧妙地
SELECT fields
FROM table
WHERE age = "50"
AND name REGEXP "2010-09-1(6|7).*";
回答by OMG Ponies
There is no combination of the LIKE and IN clauses. It's either one, or the other, syntax:
LIKE 和 IN 子句没有组合。它是一种或另一种语法：
SELECT fields
FROM table
WHERE age = 50
AND ( name IN ('tim', 'bob', 'nancy', 'john')
OR name LIKE '2010-09-17%'
OR name LIKE '2010-09-16%')
The alternative to consider when searching text is Full Text Search (FTS):
搜索文本时要考虑的替代方法是全文搜索 (FTS)：
SELECT fields
FROM table
WHERE age = 50
AND MATCH(name) AGAINST('tim bob nancy john')
...but this requires MyISAM tables, and Full Text Indexing.
...但这需要 MyISAM 表和全文索引。
回答by onedaywhen
Put the values in a table (MyParamsTable) and use LIKEin a JOINcondition e.g. something like:
将值放在表 ( MyParamsTable) 中并LIKE在JOIN条件中使用，例如：
SELECT fields
FROM table
INNER JOIN MyParamsTable
ON table.name LIKE CONCAT(MyParamsTable.name, "%")
WHERE age = "50";
