如何在 MySQL 中实现关键字搜索?

2021-11-20 00:00:00 sql search mysql

我是 SQL 编程的新手.

I am new to SQL programming.

我有一个表格作业，其中的字段是 id、position、category、location、薪资范围, description, refno.

I have a table job where the fields are id, position, category, location, salary range, description, refno.

我想从前端实现关键字搜索.关键字可以位于上表的任何字段中.

I want to implement a keyword search from 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%'

推荐答案

对于 VARCHAR 字段上的单个关键字，您可以使用 LIKE:

For a single keyword on VARCHAR fields you can use LIKE:

SELECT id, category, location
FROM table
WHERE
(
    category LIKE '%keyword%'
    OR location LIKE '%keyword%'
)

对于描述，您通常最好添加全文索引并执行 全文搜索(仅限 MyISAM):

For a description you're usually better adding a full text index and doing a Full-Text Search (MyISAM only):

SELECT id, description
FROM table
WHERE MATCH (description) AGAINST('keyword1 keyword2')

相关文章