您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Sphinx + MySQL 全文檢索實戰：UTF-8 與 GBK 雙編碼環境部署指南

編輯：MySQL 資料庫相關知識

環境版本

MySQL：5.1.38-rc
Sphinx：0.9.9（穩定版）
OS：CentOS 5.4

核心安裝流程

1. 整合 SphinxSE 至 MySQL 源碼

# 解壓
tar zxvf mysql-5.1.38.tar.gz
tar zxvf sphinx-0.9.9.tar.gz

# 複製 SphinxSE 插件源碼
cp -rf sphinx-0.9.9/mysqlse mysql-5.1.38/storage/sphinx

2. 編譯安裝 MySQL（啟用 Sphinx 插件）

cd mysql-5.1.38

# 解決相依套件
yum install ncurses-devel automake libtool

# 生成 configure 腳本
sh BUILD/autorun.sh

# 配置編譯參數（關鍵：--with-plugins=sphinx）
./configure \
  --with-plugins=sphinx \
  --prefix=/usr/local/mysql \
  --with-charset=utf8 \
  --with-extra-charsets=complex \
  --enable-thread-safe-client \
  --enable-assembler \
  --with-readline \
  --with-big-tables \
  --with-embedded-server \
  --enable-local-infile

make && make install

3. 初始化 MySQL 資料目錄與配置

# 建立用戶與目錄
groupadd mysql
useradd -g mysql mysql
mkdir -p /storage/mysql/3306/data
chown -R mysql:mysql /storage/mysql /usr/local/mysql

# 初始化系統表
/usr/local/mysql/bin/mysql_install_db \
  --basedir=/usr/local/mysql \
  --datadir=/storage/mysql/3306/data \
  --user=mysql

my.cnf 關鍵配置（節選）

[mysqld]
default-character-set = utf8
basedir = /usr/local/mysql
datadir = /storage/mysql/3306/data
socket = /tmp/mysql.sock
port = 3306
max_connections = 3000
max_allowed_packet = 32M
default-storage-engine = MyISAM
skip-name-resolve
server-id = 1
log-bin = /storage/mysql/3306/binlog
binlog_format = MIXED
# InnoDB 相關參數註釋掉（若不使用）

4. 驗證 SphinxSE 安裝

/usr/local/mysql/bin/mysql -u root -p -S /tmp/mysql.sock
mysql> SHOW ENGINES;
# 應看到 SPHINX 引擎 Support = YES

5. 安裝 Sphinx 搜尋服務


cd sphinx-0.9.9
ldconfig /usr/local/mysql/lib/mysql
ldconfig /usr/local/mysql/include/mysql
./configure --prefix=/usr/local/sphinx --with-mysql=/usr/local/mysql
make && make install

# 複製範例配置
cp sphinx.conf.dist /usr/local/sphinx/etc/sphinx.conf

雙編碼（UTF-8 + GBK）索引配置核心

資料來源定義


# UTF-8 來源
source src1 {
    type = mysql
    sql_host = localhost
    sql_user = admin
    sql_pass = 12345678
    sql_db = test
    sql_port = 3306
    sql_sock = /tmp/mysql.sock
    sql_query_pre = SET NAMES UTF8
    sql_query_pre = SET SESSION query_cache_type=OFF
    sql_query = SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content FROM documents
    sql_attr_uint = group_id
    sql_attr_timestamp = date_added
}

# GBK 來源
source src2 {
    type = mysql
    sql_host = localhost
    sql_user = admin
    sql_pass = 12345678
    sql_db = gbk
    sql_port = 3306
    sql_sock = /tmp/mysql.sock
    sql_query_pre = SET NAMES GBK
    sql_query_pre = SET CHARACTER_SET_CLIENT=GBK
    sql_query_pre = SET CHARACTER_SET_CONNECTION=GBK
    sql_query_pre = SET CHARACTER_SET_RESULTS=UTF8
    sql_query_pre = SET SESSION query_cache_type=OFF
    sql_query = SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content FROM documents
    sql_attr_uint = group_id
    sql_attr_timestamp = date_added
}

索引定義與中文分詞配置


index test1 {  # UTF-8 索引
    source = src1
    path = /usr/local/sphinx/var/data/test1
    docinfo = extern
    charset_type = utf-8
    min_word_len = 1
    # 單字切分（非真正分詞），生產建議整合 Coreseek/SCWS/jieba
    charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, ...
    ngram_len = 1
    min_infix_len = 1
}

index test2 {  # GBK 索引（同樣使用 utf-8 charset_type，由 sql_query_pre 轉碼）
    source = src2
    path = /usr/local/sphinx/var/data/test2
    charset_type = utf-8
    # 相同 charset_table 與 ngram 設定
}

> 注意：Sphinx 0.9.9 內建不支援中文語意分詞，ngram_len=1 僅實現單字切分。生產環境強烈建議使用 Coreseek (Sphinx + MMSEG) 或整合 SCWS / Jieba 實現真正中文分詞。

Searchd 服務配置


searchd {
    port = 3312
    log = /usr/local/sphinx/var/log/searchd.log
    query_log = /usr/local/sphinx/var/log/query.log
    read_timeout = 5
    client_timeout = 300
    max_children = 30
    max_matches = 1000
    seamless_rotate = 1  # 支援無縫輪轉（增量索引必備）
    preopen_indexes = 0
    unlink_old = 1
}

建立索引與啟動服務


# 建立軟連結解決 libmysqlclient 相依
ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16

# 全量建立索引
/usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all

# 啟動搜尋服務
/usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf

測試驗證


# CLI 測試 UTF-8 索引
/usr/local/sphinx/bin/search -c /usr/local/sphinx/etc/sphinx.conf "關鍵詞"

# CLI 測試 GBK 索引（需確保終端編碼一致）
/usr/local/sphinx/bin/search -c /usr/local/sphinx/etc/sphinx.conf --index test2 "關鍵詞"

MySQL 編碼切換技巧

若需整庫切換編碼（如測試 GBK 環境）：


# my.cnf 修改
[client]
default-character-set = gbk
[mysqld]
default-character-set = gbk

# 重啟後驗證
SHOW VARIABLES LIKE 'character_set_%';

常見坑點總結

問題	原因	解決
configure: unknown plugin: sphinx	未執行 `sh BUILD/autorun.sh`	執行 autorun.sh 重新生成 configure
aclocal: command not found	缺少 automake	`yum install automake libtool`
libmysqlclient.so.16 not found	動態庫路徑未加載	建立軟連結至 /usr/lib 或設定 LD_LIBRARY_PATH
中文搜尋不準/無結果	ngram 單字切分、charset_table 不全	整合 Coreseek/SCWS 實現語意分詞
GBK 資料亂碼	sql_query_pre 編碼轉換缺失	完整設定 NAMES/CLIENT/CONNECTION/RESULTS 連結