选择查询，就像只包含字符串中的特定单词一样

Question

我有一个像下面这样的表，表名是js_skills_achievements

id   skill1  skill2  skill3  skill4
1     java    php    html    android
2     php     java   jquery  html
3     sql     php    html    jquery

当管理员搜索键是"java developer in nersupalli“时

我的查询如下

SELECT *
FROM `js_skills_achievements`
WHERE `skill1`  LIKE '%java developer in nersupalli%'
   OR `skill2`  LIKE '%java developer in nersupalli%'
   OR `skill3`  LIKE '%java developer in nersupalli%'
   OR `skill4`  LIKE '%java developer in nersupalli%'
ORDER BY id DESC
LIMIT 10

不是为我工作。我的要求是我需要显示所有的java记录。这里总共有2条java记录。id1，id2.但我得到了空荡荡的行。

以下是只工作的方式

SELECT *
FROM `js_skills_achievements`
WHERE `skill1`  LIKE '%java%'
   OR `skill2`  LIKE '%java%'
   OR `skill3`  LIKE '%java%'
   OR `skill4`  LIKE '%java%'
ORDER BY id DESC
LIMIT 10

我需要基于字符串指定的关键字搜索。你能帮个忙吗。我非常感激。谢谢

Answer 1

在你的档案中添加全文

ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill1`);
ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill2`);
ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill3`);
ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill4`);

然后运行此查询。

SELECT * FROM js_skills_achievements
WHERE MATCH(skill1,skill2,skill3,skill4) AGAINST ('java developer in nersupalli');

Answer 2

做这件事的方法有点糟糕，那就是反转您喜欢的子句，并将字符串与列匹配。

SELECT 
  * 
FROM
  js_skills_achievements 
WHERE 'java developer in nersupalli' LIKE CONCAT('%',skill1,'%')
  OR 'java developer in nersupalli' LIKE CONCAT('%',skill2,'%')
  OR 'java developer in nersupalli' LIKE CONCAT('%',skill3,'%')
  OR 'java developer in nersupalli' LIKE CONCAT('%',skill4,'%')
  OR skill1  LIKE '%java developer in nersupalli%'
  OR skill2  LIKE '%java developer in nersupalli%'
  OR skill3  LIKE '%java developer in nersupalli%'
  OR skill4  LIKE '%java developer in nersupalli%'
ORDER BY id DESC 
LIMIT 10 

演示

Answer 3

您可以在一个空格上打开搜索字符串。

$words = explode ( ' ' , $string);

而不是搜索skill1中的所有单词。所有的单词在skill2等。