[MySQL]如何在Percona上安裝mysqlftppc-bigram做中文全文索引呢

為何不用Sphinx或Lucene呢？因為比起前面這兩個，這plugin安裝及使用上相對容易，幾百萬筆的資料查詢起來還可接受，M/S架構上裝在Slave運作也還OK，若資料量千萬筆就比較不建議用這個囉，還是用專業的Sphinx或Lucene吧

環境：CentOS 6.5 + Percona 5.5.40

安裝條件：
1.mysql-devel套件，安裝這個才有mysql_config
2.libicu-devel套件，要支援UNICODE，安裝這個才有icu-config

3.安裝mysqlftppc-bigram

4.相容MySQL 5.5與5.6，Engine只能用MyISAM喔


1.若用MySQL可直接安裝mysql-devel套件，因為改用Percona，如果還安裝這個套件會在編譯時出錯，需要改用Percona-XtraDB-Cluster-devel套件才行，這是給Percona用的

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install Percona-XtraDB-Cluster-devel-55

2.安裝libicu-devel套件即可，Percona也可用
yum -y install libicu-devel

3.安裝mysqlftppc-bigram

yum -y install git-core autoconf automake libtool
git clone https://github.com/y-ken/mysqlftppc-bigram
cd mysqlftppc-bigram/
aclocal
libtoolize --automake
automake --add-missing
automake
autoconf

./configure --with-mysql-config=/usr/bin/mysql_config --with-icu-config=/usr/bin/icu-config

 make
 make install

編譯完成會看到如上畫面，此時去plugin路徑下可看到相關的lib囉

4.安裝Percona，因為Percona-XtraDB-Cluster-devel與Percona-Server-client相沖，得先移除Percona-XtraDB-Cluster-devel喔

yum remove Percona-XtraDB-Cluster-devel-55
yum install Percona-Server-server-55

5.啟動Percona(這篇只是示範如何安裝plugin，設定Percona的部分不再此篇範圍囉)
/etc/init.d/mysql start

登入MySQL，安裝plugin
 install plugin bigram soname 'libftbigram.so';

檢查是否安裝成功
 show plugins;

 SHOW STATUS LIKE 'Bigram_info';

6.建立一個有全文索引的資料表

CREATE TABLE article (

   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

    title VARCHAR(200),

    body TEXT,

    FULLTEXT(title, body)

) ENGINE=MYISAM;


7.兩種使用模式

• Natural langage mode

SELECT *

FROM article

WHERE MATCH(title, body)

AGAINST ('xxx' IN NATURAL LANGUAGE MODE);

boolean mode

SELECT *

FROM article

WHERE MATCH(title, body)

AGAINST ('+mysql -yoursql' IN BOOLEAN MODE); 

8.MySQL的ft_min_word_len參數預設是4，這是全文索引查詢時最短的字元數，看需求自行修改喔