讓MySQL支持中文全文檢索
■ 楊寶昌
--------------------------------------------------------------------------------
因為中文詞間并沒有明顯的區(qū)隔,所以中文的分詞是按照字典、詞庫的匹配和詞的頻度統(tǒng)計,或是基于句法、語法分析的分詞,而MySQL并不具備此功能,所以MySQL對中文全文檢索的支持幾乎為零。
目前很多網(wǎng)站和系統(tǒng)都提供了全文搜索功能,用戶可以輸入詞或者語句來定位匹配的記錄。在后臺,可以使用Select查詢中的Like語句來執(zhí)行這種查詢,盡管這種方法可行,但對于全文查找而言,這是一種效率極端低下的方法,尤其在處理大量數(shù)據(jù)的時候。MySQL針對這一問題提供了一種基于內建的全文查找方式的解決方案。開發(fā)者只需要簡單地標記出需要全文查找的字段,然后使用MySQL方法在那些字段運行搜索,這不僅僅提高了性能和效率(因為MySQL對這些字段做了索引來優(yōu)化搜索),而且實現(xiàn)了更高質量的搜索,因為MySQL使用自然語言來智能地對結果評級,以去掉不相關的項目。這就是MySQL的全文檢索功能。
到MySQL3.23.23時,MySQL開始支持全文索引和搜索。全文索引在MySQL中是一個 FullText類型索引。FullText索引用于MyISAM表,可以在Create Table時或之后使用Alter Table或Create Index在Char、VarChar或TEXT列上創(chuàng)建。對于大的數(shù)據(jù)庫,將數(shù)據(jù)裝載到一個沒有FullText索引的表中,然后再使用Alter Table (或Create Index) 創(chuàng)建索引,這將是非常快的。
但是,眾所周知,在英文排版時詞間是以空格區(qū)分的,所以英文的分詞是基于空格的。但是涉及到中文等東亞文字就沒有這么簡單了,因為中文詞間并沒有明顯的區(qū)隔,所以中文的分詞則是按照字典、詞庫的匹配和詞的頻度統(tǒng)計,或是基于句法、語法分析的分詞,而MySQL并不具備此功能,所以MySQL對中文全文檢索的支持幾乎為零。
需求及設計思想
來自NetCraft統(tǒng)計的數(shù)據(jù),截至2006年4月份全球網(wǎng)站數(shù)量超過8065萬個,目前,Linux及Apache在網(wǎng)站操作系統(tǒng)及Web服務器軟件市場的份額為62.7%。再來看一份來自中國互聯(lián)網(wǎng)絡信息中心(CNNIC)的調查報告,截至2006年1月1日,全國有69.4萬個網(wǎng)站,在線數(shù)據(jù)庫數(shù)為29萬5400個。由于中國使用LAMP(Linux、Apache、MySQL、PHP)套件的數(shù)量沒有達到世界平均份額,所以估計有15萬左右的基于PHP/MySQL構架的網(wǎng)站,這不包括5萬個WAP網(wǎng)站、企業(yè)管理系統(tǒng)、教學應用系統(tǒng)等信息管理系統(tǒng)。如果全都算上,在中國使用MySQL的站點(系統(tǒng))數(shù)量應該在15萬至18萬之間。
MySQL有如此龐大的使用規(guī)模,中文全文檢索在MySQL的實現(xiàn)將是相當迫切,加之筆者在做一個網(wǎng)站系統(tǒng)的時候也是由于租用服務器硬性環(huán)境,不能自由選擇數(shù)據(jù)庫或者添加組件,所以只能根據(jù)已有的條件進行適應性改良。一個偶然的思路使筆者產生了能否將中文和英文建立特定關聯(lián),然后再進行檢索呢?答案是可以的。MySQL不支持中文全文檢索,但是支持英文全文檢索,可以將“中文全文檢索”轉變?yōu)椤坝⑽娜臋z索”,然后再變回“中文全文檢索”。那么中文又如何轉換成英文呢?思路可以是:中文全文檢索→拼音全文檢索→中文全文檢索。所以轉換的思路如圖1所示。
圖1 中文全文檢索思路
用戶輸入中文信息提交的時候,通過一個中文到拼音的轉換程序將其數(shù)據(jù)轉換為拼音的格式存入數(shù)據(jù)庫中,然后用戶檢索時同樣是輸入中文,也將檢索詞轉換為拼音,到數(shù)據(jù)庫中進行匹配,返回結果。但是這樣做有兩個缺點:第一個是,檢索是成功了,可是沒有中文原數(shù)據(jù),如何返回有效結果?第二個是,中文轉換拼音如果中文篇幅過長,轉換時間過長怎么辦?所以催生了第二種解決方法,如圖2所示。
圖2 改進后的檢索思路
如圖2所示,用戶輸入中文按照正常方式直接提交至數(shù)據(jù)庫,然后有一個后臺轉換程序循環(huán)執(zhí)行,將中文轉換為拼音存入另外一張全文檢索表中,其他用戶在進行全文檢索時由于詞語長度有限,所以可以采用即時轉換拼音,然后進行數(shù)據(jù)庫查詢匹配。
MySQL中文全文檢索實現(xiàn)
1.數(shù)據(jù)庫表結構設計
此處用到兩張表,以為人員信息建立全文檢索為例,分為:人員信息表(UserInfo)和全文檢索表(IndexSeek),表結構如圖3所示。
圖3 人員信息表和全文檢索表
人員信息表(UserInfo)各列含義為: ID(id),自增序列;姓名(user_name);個人介紹(user_introduction);全文檢索狀態(tài)(user_state),新添加或者修改后狀態(tài)為0,拼音轉換完成狀態(tài)為1。
全文檢索表(IndexSeek) 各列含義為:ID(id),自增序列;關聯(lián)人員信息ID(user_id),對應UserInfo表中ID;索引項(index_item),該列設置為FullText全文檢索;索引標示(index_mark),標示為哪個列的索引,本文中涉及兩個索引,分別為姓名(user_name)、介紹(user_introduction)。
兩個表中的log字段記錄該行修改的時間和產生的錯誤提示。在本文中無實際用途。
2.用戶輸入中文資料
用戶自己輸入信息添加至數(shù)據(jù)庫,例如,各項為:“1”、“張三”、“愛好編程,PHP,MySQL”、“0”。
3.后臺轉換拼音過程
后臺應用程序按照規(guī)則進行中文到拼音的轉換,筆者在網(wǎng)上下載了內碼→拼音轉換數(shù)據(jù)包pinyin.dat,該文件的下載網(wǎng)址為:http://scws.tguanlim.com/py/getpy.php。 以下以PHP代碼為例演示中文→拼音轉換。
//my_Getpy漢字轉換拼音類[5]
class my_Getpy
{
var $_dat = 'pinyin.dat';
var $_fd = false;
function my_Getpy($pdat = '')
{
if ('' != $pdat)
$this->_dat = $pdat;
}
function load($pdat = '')
{
if ('' == $pdat)
$pdat = $this->_dat;
$this->unload();
$this->_fd = @fopen($pdat, 'rb');
if (!$this->_fd)
{
trigger_error("沒有`$pdat`文件", E_USER_WARNING);
return false;
}
return true;
}
function unload()
{
if ($this->_fd)
{
@fclose($this->_fd);
$this->_fd = false;
}
}
function get($zh)
{
/*if (strlen($zh) != 2)
{
trigger_error("`$zh` is not a valid GBK hanzi", E_USER_WARNING);
return false;
}*/
if (!$this->_fd && !$this->load())
return false;
$high = ord($zh[0]) - 0x81;
$low = ord($zh[1]) - 0x40;
// 計算偏移位置
$nz = ($ord0 - 0x81);
$off = ($high< <
+ $low - ($high * 0x40);
// 判斷 off 值
fseek($this->_fd, $off * 8, SEEK_SET);
$ret = fread($this->_fd,
;
$ret = rtrim($ret, '\0');
return $ret;
}
function _my_Getpy()
{
$this->_unload();
}
}
//截取字符函數(shù)
function csubstr($str,$start,$len)
{
$strlen=strlen($str);
if ($start>=$strlen)
return $str;
$clen=0;
for($i=0;$i< $strlen;$i++,$clen++)
{
if(ord(substr($str,$i,1))>0xa0)
{
if ($clen>=$start)
$tmpstr.=substr($str,$i,2);
$i++;
}
else
{
if ($clen>=$start)
$tmpstr.=substr($str,$i,1);
}
if ($clen>=$start+$len)
break;
}
return $tmpstr;
}
//計算字符長度的函數(shù),漢字中文都當作是長度為1
function StrLenW($str)
{
$count = 0;
$len = strlen($str);
for($i=0; $i< $len; $i++,$count++)
if(ord($str[$i])>=128)
$i++;
return $count;
}
//批量中文轉換拼音函數(shù),轉換后每個字符中間加上空格,判斷非中文字符如符號,英文,數(shù)字則不作變動
function operate_py($str){
$len_max=StrLenW($str);
$len="0";
$start="0";
$py = new my_Getpy;
for($start=0;$start< $len_max;$start++){
$simp=csubstr($str,$start,$len);
if ( ord($simp)>127 ){
$rs = $py->get($simp);
$rs=" ".$rs." ";
}
else{
$rs=$simp;
}
$rs_return.=$rs;
}
$py=null;
return $rs_return;
}
將該程序循環(huán)執(zhí)行,查詢user_state為0的行,取出user_name、user_introduction兩列數(shù)據(jù),分別進行拼音轉換,然后插入全文檢索表(IndexSeek)表中,添加兩行數(shù)據(jù),其中第一行為:
關聯(lián)人員信息ID(user_id):1;//對應UserInfo表中ID,此處為1
索引項(index_item):zhang1 san1;
索引標示(index_mark): 姓名(user_name)。
第二行為:
關聯(lián)人員信息ID(user_id):1;//對應UserInfo表中ID,此處為1
索引項(index_item):ai4 hao3 bian1 cheng2 ,PHP,MySQL;
索引標示(index_mark):介紹(user_introduction)。
至此,添加中文數(shù)據(jù)轉換拼音過程完畢。修改用戶信息同樣步驟進行,只是不再新添加表,刪除數(shù)據(jù)則根據(jù)關聯(lián)id全部刪除。
4.用戶檢索過程
由于用戶檢索時輸入的關鍵詞數(shù)量有限,則采用頁面應用程序直接轉換拼音進行匹配。例如用戶輸入檢索詞為:“編程”,提交給服務器,由服務器端程序處理轉換為“bian1 cheng2”進行數(shù)據(jù)表IndexSeek的全文檢索,全文檢索SQL為:
SELECT * FROM IndexSeekWHERE MATCH (index_item) AGAINST (' bian1 cheng2');
得出關聯(lián)人員ID為1,然后取得用戶資料表中全部信息返回給用戶。由于中文轉換拼音過程同上,在此不再列出代碼。
更多的英文全文檢索技巧請參看MySQL手冊,這里不再一一介紹。上面便是整個MySQL中文全文檢索實現(xiàn)過程。
本文所提供的方法并不能完全解決MySQL的全文檢索問題,由于中文字詞存在同音現(xiàn)象,比如:"清華"和"輕劃"檢索結果相同,這也是不可避免的,但是對于MySQL的用戶來說,該方法是目前不錯的一種折衷手段。
(計算機世界報 2006年09月25日 第37期 B28、B29)
■ 楊寶昌
--------------------------------------------------------------------------------
因為中文詞間并沒有明顯的區(qū)隔,所以中文的分詞是按照字典、詞庫的匹配和詞的頻度統(tǒng)計,或是基于句法、語法分析的分詞,而MySQL并不具備此功能,所以MySQL對中文全文檢索的支持幾乎為零。
目前很多網(wǎng)站和系統(tǒng)都提供了全文搜索功能,用戶可以輸入詞或者語句來定位匹配的記錄。在后臺,可以使用Select查詢中的Like語句來執(zhí)行這種查詢,盡管這種方法可行,但對于全文查找而言,這是一種效率極端低下的方法,尤其在處理大量數(shù)據(jù)的時候。MySQL針對這一問題提供了一種基于內建的全文查找方式的解決方案。開發(fā)者只需要簡單地標記出需要全文查找的字段,然后使用MySQL方法在那些字段運行搜索,這不僅僅提高了性能和效率(因為MySQL對這些字段做了索引來優(yōu)化搜索),而且實現(xiàn)了更高質量的搜索,因為MySQL使用自然語言來智能地對結果評級,以去掉不相關的項目。這就是MySQL的全文檢索功能。
到MySQL3.23.23時,MySQL開始支持全文索引和搜索。全文索引在MySQL中是一個 FullText類型索引。FullText索引用于MyISAM表,可以在Create Table時或之后使用Alter Table或Create Index在Char、VarChar或TEXT列上創(chuàng)建。對于大的數(shù)據(jù)庫,將數(shù)據(jù)裝載到一個沒有FullText索引的表中,然后再使用Alter Table (或Create Index) 創(chuàng)建索引,這將是非常快的。
但是,眾所周知,在英文排版時詞間是以空格區(qū)分的,所以英文的分詞是基于空格的。但是涉及到中文等東亞文字就沒有這么簡單了,因為中文詞間并沒有明顯的區(qū)隔,所以中文的分詞則是按照字典、詞庫的匹配和詞的頻度統(tǒng)計,或是基于句法、語法分析的分詞,而MySQL并不具備此功能,所以MySQL對中文全文檢索的支持幾乎為零。
需求及設計思想
來自NetCraft統(tǒng)計的數(shù)據(jù),截至2006年4月份全球網(wǎng)站數(shù)量超過8065萬個,目前,Linux及Apache在網(wǎng)站操作系統(tǒng)及Web服務器軟件市場的份額為62.7%。再來看一份來自中國互聯(lián)網(wǎng)絡信息中心(CNNIC)的調查報告,截至2006年1月1日,全國有69.4萬個網(wǎng)站,在線數(shù)據(jù)庫數(shù)為29萬5400個。由于中國使用LAMP(Linux、Apache、MySQL、PHP)套件的數(shù)量沒有達到世界平均份額,所以估計有15萬左右的基于PHP/MySQL構架的網(wǎng)站,這不包括5萬個WAP網(wǎng)站、企業(yè)管理系統(tǒng)、教學應用系統(tǒng)等信息管理系統(tǒng)。如果全都算上,在中國使用MySQL的站點(系統(tǒng))數(shù)量應該在15萬至18萬之間。
MySQL有如此龐大的使用規(guī)模,中文全文檢索在MySQL的實現(xiàn)將是相當迫切,加之筆者在做一個網(wǎng)站系統(tǒng)的時候也是由于租用服務器硬性環(huán)境,不能自由選擇數(shù)據(jù)庫或者添加組件,所以只能根據(jù)已有的條件進行適應性改良。一個偶然的思路使筆者產生了能否將中文和英文建立特定關聯(lián),然后再進行檢索呢?答案是可以的。MySQL不支持中文全文檢索,但是支持英文全文檢索,可以將“中文全文檢索”轉變?yōu)椤坝⑽娜臋z索”,然后再變回“中文全文檢索”。那么中文又如何轉換成英文呢?思路可以是:中文全文檢索→拼音全文檢索→中文全文檢索。所以轉換的思路如圖1所示。
圖1 中文全文檢索思路
用戶輸入中文信息提交的時候,通過一個中文到拼音的轉換程序將其數(shù)據(jù)轉換為拼音的格式存入數(shù)據(jù)庫中,然后用戶檢索時同樣是輸入中文,也將檢索詞轉換為拼音,到數(shù)據(jù)庫中進行匹配,返回結果。但是這樣做有兩個缺點:第一個是,檢索是成功了,可是沒有中文原數(shù)據(jù),如何返回有效結果?第二個是,中文轉換拼音如果中文篇幅過長,轉換時間過長怎么辦?所以催生了第二種解決方法,如圖2所示。
圖2 改進后的檢索思路
如圖2所示,用戶輸入中文按照正常方式直接提交至數(shù)據(jù)庫,然后有一個后臺轉換程序循環(huán)執(zhí)行,將中文轉換為拼音存入另外一張全文檢索表中,其他用戶在進行全文檢索時由于詞語長度有限,所以可以采用即時轉換拼音,然后進行數(shù)據(jù)庫查詢匹配。
MySQL中文全文檢索實現(xiàn)
1.數(shù)據(jù)庫表結構設計
此處用到兩張表,以為人員信息建立全文檢索為例,分為:人員信息表(UserInfo)和全文檢索表(IndexSeek),表結構如圖3所示。
圖3 人員信息表和全文檢索表
人員信息表(UserInfo)各列含義為: ID(id),自增序列;姓名(user_name);個人介紹(user_introduction);全文檢索狀態(tài)(user_state),新添加或者修改后狀態(tài)為0,拼音轉換完成狀態(tài)為1。
全文檢索表(IndexSeek) 各列含義為:ID(id),自增序列;關聯(lián)人員信息ID(user_id),對應UserInfo表中ID;索引項(index_item),該列設置為FullText全文檢索;索引標示(index_mark),標示為哪個列的索引,本文中涉及兩個索引,分別為姓名(user_name)、介紹(user_introduction)。
兩個表中的log字段記錄該行修改的時間和產生的錯誤提示。在本文中無實際用途。
2.用戶輸入中文資料
用戶自己輸入信息添加至數(shù)據(jù)庫,例如,各項為:“1”、“張三”、“愛好編程,PHP,MySQL”、“0”。
3.后臺轉換拼音過程
后臺應用程序按照規(guī)則進行中文到拼音的轉換,筆者在網(wǎng)上下載了內碼→拼音轉換數(shù)據(jù)包pinyin.dat,該文件的下載網(wǎng)址為:http://scws.tguanlim.com/py/getpy.php。 以下以PHP代碼為例演示中文→拼音轉換。
//my_Getpy漢字轉換拼音類[5]
class my_Getpy
{
var $_dat = 'pinyin.dat';
var $_fd = false;
function my_Getpy($pdat = '')
{
if ('' != $pdat)
$this->_dat = $pdat;
}
function load($pdat = '')
{
if ('' == $pdat)
$pdat = $this->_dat;
$this->unload();
$this->_fd = @fopen($pdat, 'rb');
if (!$this->_fd)
{
trigger_error("沒有`$pdat`文件", E_USER_WARNING);
return false;
}
return true;
}
function unload()
{
if ($this->_fd)
{
@fclose($this->_fd);
$this->_fd = false;
}
}
function get($zh)
{
/*if (strlen($zh) != 2)
{
trigger_error("`$zh` is not a valid GBK hanzi", E_USER_WARNING);
return false;
}*/
if (!$this->_fd && !$this->load())
return false;
$high = ord($zh[0]) - 0x81;
$low = ord($zh[1]) - 0x40;
// 計算偏移位置
$nz = ($ord0 - 0x81);
$off = ($high< <

// 判斷 off 值
fseek($this->_fd, $off * 8, SEEK_SET);
$ret = fread($this->_fd,

$ret = rtrim($ret, '\0');
return $ret;
}
function _my_Getpy()
{
$this->_unload();
}
}
//截取字符函數(shù)
function csubstr($str,$start,$len)
{
$strlen=strlen($str);
if ($start>=$strlen)
return $str;
$clen=0;
for($i=0;$i< $strlen;$i++,$clen++)
{
if(ord(substr($str,$i,1))>0xa0)
{
if ($clen>=$start)
$tmpstr.=substr($str,$i,2);
$i++;
}
else
{
if ($clen>=$start)
$tmpstr.=substr($str,$i,1);
}
if ($clen>=$start+$len)
break;
}
return $tmpstr;
}
//計算字符長度的函數(shù),漢字中文都當作是長度為1
function StrLenW($str)
{
$count = 0;
$len = strlen($str);
for($i=0; $i< $len; $i++,$count++)
if(ord($str[$i])>=128)
$i++;
return $count;
}
//批量中文轉換拼音函數(shù),轉換后每個字符中間加上空格,判斷非中文字符如符號,英文,數(shù)字則不作變動
function operate_py($str){
$len_max=StrLenW($str);
$len="0";
$start="0";
$py = new my_Getpy;
for($start=0;$start< $len_max;$start++){
$simp=csubstr($str,$start,$len);
if ( ord($simp)>127 ){
$rs = $py->get($simp);
$rs=" ".$rs." ";
}
else{
$rs=$simp;
}
$rs_return.=$rs;
}
$py=null;
return $rs_return;
}
將該程序循環(huán)執(zhí)行,查詢user_state為0的行,取出user_name、user_introduction兩列數(shù)據(jù),分別進行拼音轉換,然后插入全文檢索表(IndexSeek)表中,添加兩行數(shù)據(jù),其中第一行為:
關聯(lián)人員信息ID(user_id):1;//對應UserInfo表中ID,此處為1
索引項(index_item):zhang1 san1;
索引標示(index_mark): 姓名(user_name)。
第二行為:
關聯(lián)人員信息ID(user_id):1;//對應UserInfo表中ID,此處為1
索引項(index_item):ai4 hao3 bian1 cheng2 ,PHP,MySQL;
索引標示(index_mark):介紹(user_introduction)。
至此,添加中文數(shù)據(jù)轉換拼音過程完畢。修改用戶信息同樣步驟進行,只是不再新添加表,刪除數(shù)據(jù)則根據(jù)關聯(lián)id全部刪除。
4.用戶檢索過程
由于用戶檢索時輸入的關鍵詞數(shù)量有限,則采用頁面應用程序直接轉換拼音進行匹配。例如用戶輸入檢索詞為:“編程”,提交給服務器,由服務器端程序處理轉換為“bian1 cheng2”進行數(shù)據(jù)表IndexSeek的全文檢索,全文檢索SQL為:
SELECT * FROM IndexSeekWHERE MATCH (index_item) AGAINST (' bian1 cheng2');
得出關聯(lián)人員ID為1,然后取得用戶資料表中全部信息返回給用戶。由于中文轉換拼音過程同上,在此不再列出代碼。
更多的英文全文檢索技巧請參看MySQL手冊,這里不再一一介紹。上面便是整個MySQL中文全文檢索實現(xiàn)過程。
本文所提供的方法并不能完全解決MySQL的全文檢索問題,由于中文字詞存在同音現(xiàn)象,比如:"清華"和"輕劃"檢索結果相同,這也是不可避免的,但是對于MySQL的用戶來說,該方法是目前不錯的一種折衷手段。
(計算機世界報 2006年09月25日 第37期 B28、B29)
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
