MySQL实现多关键词模糊搜索，搜索结果按照匹配关键词的多少来排序

MySQL实现多关键词模糊搜索，搜索结果按照匹配关键词的多少来排序

初学者，首先想到的肯定是 LIKE ，关键词是“车”的话，就这样：
SELECT * FROM article WHERE 
title LIKE "%车%" 
OR subtitle LIKE "%车%" 
OR tag 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，万无一失嘛，我们用前者。

匹配关键词的多少来排序匹配关键词越多的文章越靠前
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

转自这位大佬：http://lao.si/t/mysql-fuzzy-search-and-order-by