我是SQL编程的新手.
I am new to SQL programming.
我有一个表作业,其中的字段是id,position,category,location,salary range,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中实现关键字搜索?
发布评论