MySQL模糊查询多个关键词，查询结果按照匹配关键词的多少来排序

• 首先想到的肯定是 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

通过一组关键词站内模糊搜索，按照匹配关键词的多少来排序。这个需求，目标达成