- 首先想到的肯定是 LIKE ,关键词是“车”的话
SELECT * FROM article WHERE
title LIKE "%车%"
OR subtitle LIKE "%车%"
OR tag LIKE "%车%"
- 用一个 LIKE 解决不了的事情就多用几个 LIKE
SELECT * FROM article WHERE
(
title LIKE "%车%"
OR subtitle LIKE "%车%"
OR tag LIKE "%车%"
) OR (
title LIKE "%摩托%"
OR subtitle LIKE "%摩托%"
OR tag LIKE "%摩托%"
) OR (
title LIKE "%红色%"
OR subtitle LIKE "%红色%"
OR tag LIKE "%红色%"
) OR (
title LIKE "%美国%"
OR subtitle LIKE "%美国%"
OR tag LIKE "%美国%"
) OR (
title LIKE "%2006%"
OR subtitle LIKE "%2006%"
OR tag LIKE "%2006%"
)
- 我们还可以用正则
SELECT * FROM article WHERE
title REGEXP "车|摩托|红色|美国|2006"
OR subtitle REGEXP "车|摩托|红色|美国|2006"
OR tag REGEXP "车|摩托|红色|美国|2006"
- 几个字段合并起来
SELECT * FROM article WHERE
CONCAT_WS(" ", title, subtitle, tag) REGEXP "车|摩托|红色|美国|2006"
之所以用 CONCAT_WS()
而不是 CONCAT()
,是因为后者在某字段为 NULL 的情况下会导致合并结果为 NULL,万无一失嘛,我们用前者。
- 匹配关键词越多的文章越靠前
## 实例1
SELECT *,
(
(IF( CONCAT_WS(" ", title, subtitle, tag) LIKE "%车%", 1, 0))
+ (IF( CONCAT_WS(" ", title, subtitle, tag) LIKE "%摩托%", 1, 0))
+ (IF( CONCAT_WS(" ", title, subtitle, tag) LIKE "%红色%", 1, 0))
+ (IF( CONCAT_WS(" ", title, subtitle, tag) LIKE "%美国%", 1, 0))
+ (IF( CONCAT_WS(" ", title, subtitle, tag) LIKE "%2006%", 1, 0))
) AS keyweight
FROM article WHERE
CONCAT_WS(" ", title, subtitle, tag) REGEXP "车|摩托|红色|美国|2006"
ORDER BY keyweight DESC
## 实例2
SELECT
title,
(IF(title LIKE "%车%",1,0)) AS keyweight
FROM
article
ORDER BY keyweight DESC
通过一组关键词站内模糊搜索,按照匹配关键词的多少来排序。这个需求,目标达成