MySQL：列包含单词列表中的单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4688643/
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: Column Contains Word From List of Words
提问by mellowsoon
I have a list of words. Lets say they are 'Apple', 'Orange', and 'Pear'. I have rows in the database like this:
我有一个单词列表。假设它们是“Apple”、“Orange”和“Pear”。我在数据库中有这样的行：
------------------------------------------------
|author_id | content |
------------------------------------------------
| 54 | I ate an apple for breakfast. |
| 63 | Going to the store. |
| 12 | Should I wear the orange shirt? |
------------------------------------------------
I'm looking for a query on an InnoDB table that will return the 1st and 3rd row, because the contentcolumn contains one or more words from my list. I know I could query the table once for each word in my list, and use LIKE and the % wildcard character, but I'm wondering if there is a single query method for such a thing?
我正在寻找对 InnoDB 表的查询，该表将返回第 1 行和第 3 行，因为该content列包含我列表中的一个或多个单词。我知道我可以为列表中的每个单词查询一次表，并使用 LIKE 和 % 通配符，但我想知道是否有一种查询方法可以用于此类事情？
回答by John K.
MySQL (I believe the 5.0 version) added the ability to use regular expressions in your SQL.
MySQL（我相信 5.0 版本）增加了在 SQL 中使用正则表达式的能力。
Check out: http://www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm
查看：http: //www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm
SELECT author_id, content
FROM AuthorTableName
WHERE content REGEXP 'Apple|Orange|Pear'
ORDER BY author_id;
回答by Naveed
EDIT:
编辑：
Something like this:
像这样的东西：
SELECT * FROM yourtable WHERE content LIKE '%apple%' OR content LIKE '%orange%'
You can loop your words to create WHERE clause conditions.
您可以循环使用单词来创建 WHERE 子句条件。
For Example:
例如：
$words = array( 'apple', 'orange' );
$whereClause = '';
foreach( $words as $word) {
$whereClause .= ' content LIKE "%' . $word . '%" OR';
}
// Remove last 'OR'
$whereClause = substr($whereClause, 0, -2);
$sql = 'SELECT * FROM yourtable WHERE' . $whereClause;
echo $sql;
SELECT * FROM yourtable WHERE content LIKE "%apple%" OR content LIKE "%orange%"
