问题描述
我是 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')
这篇关于如何在 MySQL 中实现关键字搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！