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

本文介绍了如何在MySQL中实现关键字搜索?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是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')