php - SELECT QUERY LIKE 仅包含字符串中的特定单词

Question

我有一张如下表，表名是js_skills_achievements

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

当管理员搜索关键字是“nersupalli 中的 java 开发人员”时

我的查询在下面

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 记录。这里在 java 中总共有 2 条记录。即 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

有点难看的方法是反转你的 like 子句并将你的字符串与列匹配

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 

Demo