如何在 MySQL 中实现关键字搜索？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/759580/
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
How to implement a Keyword Search in MySQL?
提问by santanu
I am new to SQL programming.
我是 SQL 编程的新手。
I have a table job where the fields are id, position, category, location, salary range, description, refno.
我有一个表格工作，其中字段是id, position, category, location, salary range, description, refno。
I want to implement a keyword searchfrom the front end. The keyword can reside in any of the fields of the above table.
我想从前端实现关键字搜索。关键字可以位于上表的任何字段中。
This is the query I have tried but it consist of so many duplicate rows:
这是我尝试过的查询，但它包含如此多的重复行：
SELECT
a.*,
b.catname
FROM
job a,
category b
WHERE
a.catid = b.catid AND
a.jobsalrange = '15001-20000' AND
a.jobloc = 'Berkshire' AND
a.jobpos LIKE '%sales%' OR
a.jobloc LIKE '%sales%' OR
a.jobsal LIKE '%sales%' OR
a.jobref LIKE '%sales%' OR
a.jobemail LIKE '%sales%' OR
a.jobsalrange LIKE '%sales%' OR
b.catname LIKE '%sales%'
回答by Greg
For a single keyword on VARCHAR fields you can use LIKE:
对于 VARCHAR 字段上的单个关键字，您可以使用LIKE：
SELECT id, category, location
FROM table
WHERE
(
category LIKE '%keyword%'
OR location LIKE '%keyword%'
)
For a description you're usually better adding a full text index and doing a Full-Text Search(MyISAM only):
对于描述，您通常最好添加全文索引并进行全文搜索（仅限 MyISAM）：
SELECT id, description
FROM table
WHERE MATCH (description) AGAINST('keyword1 keyword2')
回答by Jon Bright
SELECT
*
FROM
yourtable
WHERE
id LIKE '%keyword%'
OR position LIKE '%keyword%'
OR category LIKE '%keyword%'
OR location LIKE '%keyword%'
OR description LIKE '%keyword%'
OR refno LIKE '%keyword%';
回答by paxdiablo
Ideally, have a keyword table containing the fields:
理想情况下，有一个包含字段的关键字表：
Keyword
Id
Count (possibly)
with an index on Keyword. Create an insert/update/delete trigger on the other table so that, when a row is changed, every keyword is extracted and put into (or replaced in) this table.
带有关键字索引。在另一个表上创建一个插入/更新/删除触发器，以便在更改行时提取每个关键字并将其放入（或替换）到该表中。
You'll also need a table of words to not count as keywords (if, and, so, but, ...).
您还需要一个不计为关键字的单词表（如果，并且，所以，但是，...）。
In this way, you'll get the best speed for queries wanting to look for the keywords and you can implement (relatively easily) more complex queries such as "contains Java and RCA1802".
通过这种方式，您将获得想要查找关键字的查询的最佳速度，并且您可以（相对容易地）实现更复杂的查询，例如“包含 Java 和 RCA1802”。
"LIKE"queries will work but they won't scale as well.
"LIKE"查询将起作用，但它们也不会扩展。
回答by Calvin
Personally, I wouldn't use the LIKEstring comparison on the ID field or any other numeric field. It doesn't make sense for a search for ID# "216" to return 16216, 21651, 3216087, 5321668..., and so on and so forth; likewise with salary.
就个人而言，我不会LIKE在 ID 字段或任何其他数字字段上使用字符串比较。搜索 ID#“ 216”返回 16 216、21651 、 3 216087 、 53 21668... 等等是没有意义的；工资也是一样。
Also, if you want to use prepared statements to prevent SQL injections, you would use a query string like:
此外，如果您想使用准备好的语句来防止 SQL 注入，您可以使用如下查询字符串：
SELECT * FROM job WHERE `position` LIKE CONCAT('%', ? ,'%') OR ...
回答by Francesco Panina
I will explain the method i usally prefer:
我将解释我通常喜欢的方法：
First of all you need to take into consideration that for this method you will sacrifice memory with the aim of gaining computation speed. Second you need to have a the right to edit the table structure.
首先，您需要考虑到，对于这种方法，您将牺牲内存以提高计算速度。其次，您需要拥有编辑表结构的权限。
1) Add a field (i usually call it "digest") where you store all the data from the table.
1）添加一个字段（我通常称之为“摘要”），您可以在其中存储表中的所有数据。
The field will look like:
该字段将如下所示：
"n-n1-n2-n3-n4-n5-n6-n7-n8-n9" etc..where n is a single word
"n-n1-n2-n3-n4-n5-n6-n7-n8-n9" 等等..其中 n 是单个单词
I achieve this using a regular expression thar replaces " " with "-". This field is the result of all the table data "digested" in one sigle string.
我使用正则表达式 thar 将“”替换为“-”来实现这一点。该字段是所有表数据在一个字符串中“消化”的结果。
2) Use the LIKE statement %keyword% on the digest field:
2) 在摘要字段上使用 LIKE 语句 %keyword%：
SELECT * FROM table WHERE digest LIKE %keyword%
you can even build a qUery with a little loop so you can search for multiple keywords at the same time looking like:
你甚至可以用一个小循环构建一个查询，这样你就可以同时搜索多个关键字，如下所示：
SELECT * FROM table WHERE
digest LIKE %keyword1% AND
digest LIKE %keyword2% AND
digest LIKE %keyword3% ...
回答by raphie
You can find another simpler option in a thread here: Match Against..with a more detail help in 11.9.2. Boolean Full-Text Searches
您可以在此处的线程中找到另一个更简单的选项：Match Against..在11.9.2 中有更详细的帮助。布尔全文搜索
This is just in case someone need a more compact option. This will require to create an Index FULLTEXT in the table, which can be accomplish easily.
这是以防万一有人需要更紧凑的选择。这将需要在表中创建一个索引 FULLTEXT，这很容易完成。
Information on how to create Indexes (MySQL): MySQL FULLTEXT Indexing and Searching
有关如何创建索引 (MySQL) 的信息：MySQL FULLTEXT 索引和搜索
In the FULLTEXTIndex you can have more than one column listed, the result would be an SQL Statement with an index named search:
在FULLTEXT索引中，您可以列出多个列，结果将是一个带有名为 的索引的 SQL 语句search：
SELECT *,MATCH (`column`) AGAINST('+keyword1* +keyword2* +keyword3*') as relevance FROM `documents`USE INDEX(search) WHERE MATCH (`column`) AGAINST('+keyword1* +keyword2* +keyword3*' IN BOOLEAN MODE) ORDER BY relevance;
I tried with multiple columns, with no luck. Even though multiple columns are allowed in indexes, you still need an index for each column to use with Match/Against Statement.
我尝试了多列，但没有运气。即使索引中允许有多个列，您仍然需要为每个列设置一个索引以与 Match/Against 语句一起使用。
Depending in your criterias you can use either options.
根据您的标准，您可以使用任一选项。
回答by AndroidNewbie
I know this is a bit late but what I did to our application is this. Hope this will help someone tho. But it works for me:
我知道这有点晚了，但我对我们的应用程序所做的就是这样。希望这会帮助某人。但它对我有用：
SELECT * FROM `landmarks` WHERE `landmark_name` OR `landmark_description` OR `landmark_address` LIKE '%keyword'
OR `landmark_name` OR `landmark_description` OR `landmark_address` LIKE 'keyword%'
OR `landmark_name` OR `landmark_description` OR `landmark_address` LIKE '%keyword%'
