现在需要跟据用户的输入对某表进行搜索,原程序(不是我写的)用的是 全文索引, 搜索出来的结果里,大部分只包括某个关键字,比如 big beats 可能就搜到只
包括 big 或只包括 beats 的结果来,就像 or 一样,客户不满意,要求改成 and .
原程序:
$q = str_replace(" ","+",$_search);
......
......
SELECT DISTINCT
`TrackId`, `AlbumId`, `AlbumName`, `TrackTitle`,
MATCH(`TrackTitle`, `AlbumName`, `Artist`, `Keywords`, `TrackSourceCode`) AGAINST('$q') AS relevance,
CONCAT(`TrackTitle`, `AlbumName`, `Artist`, `Keywords`, `TrackSourceCode`) REGEXP '^$q' AS begining
FROM
`tracks`
WHERE
(
MATCH(`TrackTitle`, `AlbumName`, `Artist`, `Keywords`, `TrackSourceCode`) AGAINST('$q' IN BOOLEAN MODE) OR
CONCAT(`TrackTitle`, `AlbumName`, `Artist`, `Keywords`, `TrackSourceCode`) LIKE '%$q%'
)
ORDER BY
begining DESC,
relevance DESC
LIMIT 100
关于 MySQL的全文索引,我知道的很少,只能对着手册查, MySQL 提供了四种 Modifier (修饰语):
IN BOOLEAN MODE
IN NATURAL LANGUAGE MODE
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
WITH QUERY EXPANSION
我试着用 IN NATURAL LANGUAGE MODE 但是报错,可能是 5.0.32 还不支持吧.
关于 BOOLEAN MODE, MySQL 提供了几种逻辑操作符:
+ A leading plus sign indicates that this word must be present in each row that is returned.
这里说的 + 后面的 word 必须出现在每一行里,但是我写 +big +time ：
SELECT * ,
MATCH (TrackTitle , AlbumName , Artist , Keywords , TrackSourceCode ) AGAINST ( '+big +time') AS relevance
FROM tracks
WHERE
MATCH( TrackTitle , AlbumName , Artist , Keywords , TrackSourceCode ) AGAINST('+big +time' IN BOOLEAN MODE)
ORDER BY
relevance DESC
LIMIT 100
却只能找到 time 的相关记录， big 找不到，big 也不在 stopwords列表里．
" A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed.
双引号能精确匹配，但是它里面却不能用通配符．比如：WOOD43 , WOOD66 这些，用户如果只输入 WOOD 是找不到这些数据的．
SELECT * ,
MATCH (TrackTitle , AlbumName , Artist , Keywords , TrackSourceCode ) AGAINST ( '"WOOD*"' ) AS relevance
FROM tracks
WHERE
MATCH( TrackTitle , AlbumName , Artist , Keywords , TrackSourceCode ) AGAINST('"WOOD*"' IN BOOLEAN MODE)
ORDER BY
relevance DESC
LIMIT 100
上面这条语句只找到了 WOOD43的数据
* The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be
affected. Words match if they begin with the word preceding the * operator.
这个是个通配符．
还有一些其它的逻辑操作符：
- >< () ~
用这些符号并不能达到客户的要求，没有办法，只用最原始的方法： like ,虽然慢了点．
IN BOOLEAN MODE
They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow
BOOLEAN MODE 可以用在没有全文索引的情况下．
SELECT *,
MATCH(fname,lname,company) AGAINST ('+Cooper +Toni' IN BOOLEAN MODE) as relevance
FROM
users
WHERE
MATCH(fname,lname,company) AGAINST ('+Cooper +Toni' IN BOOLEAN MODE)
users 表没有建全文索引
| < Prev | Next > |
|---|
Last Updated ( Wednesday, 20 May 2009 15:30 )