让MySQL支持中文全文检索
■ 杨宝昌
--------------------------------------------------------------------------------
因为中文词间并没有明显的区隔，所以中文的分词是按照字典、词库的匹配和词的频度统计，或是基于句法、语法分析的分词，而MySQL并不具备此功能，所以MySQL对中文全文检索的支持几乎为零。
目前很多网站和系统都提供了全文搜索功能，用户可以输入词或者语句来定位匹配的记录。在后台，可以使用Select查询中的Like语句来执行这种查询，尽管这种方法可行，但对于全文查找而言，这是一种效率极端低下的方法，尤其在处理大量数据的时候。MySQL针对这一问题提供了一种基于内建的全文查找方式的解决方案。开发者只需要简单地标记出需要全文查找的字段，然后使用MySQL方法在那些字段运行搜索，这不仅仅提高了性能和效率（因为MySQL对这些字段做了索引来优化搜索），而且实现了更高质量的搜索，因为MySQL使用自然语言来智能地对结果评级，以去掉不相关的项目。这就是MySQL的全文检索功能。
到MySQL3.23.23时,MySQL开始支持全文索引和搜索。全文索引在MySQL中是一个 FullText类型索引。FullText索引用于MyISAM表，可以在Create Table时或之后使用Alter Table或Create Index在Char、VarChar或TEXT列上创建。对于大的数据库，将数据装载到一个没有FullText索引的表中，然后再使用Alter Table (或Create Index) 创建索引，这将是非常快的。
但是，众所周知，在英文排版时词间是以空格区分的，所以英文的分词是基于空格的。但是涉及到中文等东亚文字就没有这么简单了，因为中文词间并没有明显的区隔，所以中文的分词则是按照字典、词库的匹配和词的频度统计，或是基于句法、语法分析的分词，而MySQL并不具备此功能，所以MySQL对中文全文检索的支持几乎为零。
需求及设计思想
来自NetCraft统计的数据，截至2006年4月份全球网站数量超过8065万个，目前，Linux及Apache在网站操作系统及Web服务器软件市场的份额为62.7%。再来看一份来自中国互联网络信息中心（CNNIC）的调查报告，截至2006年1月1日，全国有69.4万个网站，在线数据库数为29万5400个。由于中国使用LAMP(Linux、Apache、MySQL、PHP)套件的数量没有达到世界平均份额，所以估计有15万左右的基于PHP/MySQL构架的网站，这不包括5万个WAP网站、企业管理系统、教学应用系统等信息管理系统。如果全都算上，在中国使用MySQL的站点（系统）数量应该在15万至18万之间。
MySQL有如此庞大的使用规模，中文全文检索在MySQL的实现将是相当迫切，加之笔者在做一个网站系统的时候也是由于租用服务器硬性环境，不能自由选择数据库或者添加组件，所以只能根据已有的条件进行适应性改良。一个偶然的思路使笔者产生了能否将中文和英文建立特定关联，然后再进行检索呢？答案是可以的。MySQL不支持中文全文检索，但是支持英文全文检索，可以将“中文全文检索”转变为“英文全文检索”，然后再变回“中文全文检索”。那么中文又如何转换成英文呢？思路可以是：中文全文检索→拼音全文检索→中文全文检索。所以转换的思路如图1所示。
图1 中文全文检索思路
用户输入中文信息提交的时候，通过一个中文到拼音的转换程序将其数据转换为拼音的格式存入数据库中，然后用户检索时同样是输入中文，也将检索词转换为拼音，到数据库中进行匹配，返回结果。但是这样做有两个缺点:第一个是，检索是成功了，可是没有中文原数据，如何返回有效结果？第二个是，中文转换拼音如果中文篇幅过长，转换时间过长怎么办？所以催生了第二种解决方法，如图2所示。
图2 改进后的检索思路
如图2所示，用户输入中文按照正常方式直接提交至数据库，然后有一个后台转换程序循环执行，将中文转换为拼音存入另外一张全文检索表中，其他用户在进行全文检索时由于词语长度有限，所以可以采用即时转换拼音，然后进行数据库查询匹配。
MySQL中文全文检索实现
1．数据库表结构设计
此处用到两张表，以为人员信息建立全文检索为例，分为：人员信息表(UserInfo)和全文检索表(IndexSeek)，表结构如图3所示。
图3 人员信息表和全文检索表
人员信息表(UserInfo)各列含义为: ID(id)，自增序列;姓名(user_name);个人介绍(user_introduction)；全文检索状态(user_state)，新添加或者修改后状态为0，拼音转换完成状态为1。
全文检索表(IndexSeek) 各列含义为：ID(id)，自增序列；关联人员信息ID(user_id)，对应UserInfo表中ID；索引项(index_item)，该列设置为FullText全文检索；索引标示(index_mark)，标示为哪个列的索引，本文中涉及两个索引，分别为姓名(user_name)、介绍(user_introduction)。
两个表中的log字段记录该行修改的时间和产生的错误提示。在本文中无实际用途。
2．用户输入中文资料
用户自己输入信息添加至数据库，例如，各项为:“1”、“张三”、“爱好编程，PHP，MySQL”、“0”。
3．后台转换拼音过程
后台应用程序按照规则进行中文到拼音的转换，笔者在网上下载了内码→拼音转换数据包pinyin.dat，该文件的下载网址为：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();
}
}
//截取字符函数
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;
}
//计算字符长度的函数，汉字中文都当作是长度为1
function StrLenW($str)
{
$count = 0;
$len = strlen($str);
for($i=0; $i< $len; $i++,$count++)
if(ord($str[$i])>=128)
$i++;
return $count;
}
//批量中文转换拼音函数，转换后每个字符中间加上空格，判断非中文字符如符号，英文，数字则不作变动
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;
}
将该程序循环执行，查询user_state为0的行，取出user_name、user_introduction两列数据，分别进行拼音转换，然后插入全文检索表(IndexSeek)表中，添加两行数据，其中第一行为：
关联人员信息ID(user_id):1；//对应UserInfo表中ID，此处为1
索引项(index_item)：zhang1 san1；
索引标示(index_mark)： 姓名(user_name)。
第二行为：
关联人员信息ID(user_id)：1；//对应UserInfo表中ID，此处为1
索引项(index_item)：ai4 hao3 bian1 cheng2 ，PHP，MySQL；
索引标示(index_mark)：介绍(user_introduction)。
至此，添加中文数据转换拼音过程完毕。修改用户信息同样步骤进行，只是不再新添加表，删除数据则根据关联id全部删除。
4．用户检索过程
由于用户检索时输入的关键词数量有限，则采用页面应用程序直接转换拼音进行匹配。例如用户输入检索词为：“编程”，提交给服务器，由服务器端程序处理转换为“bian1 cheng2”进行数据表IndexSeek的全文检索，全文检索SQL为：
SELECT * FROM IndexSeekWHERE MATCH (index_item) AGAINST (' bian1 cheng2');
得出关联人员ID为1，然后取得用户资料表中全部信息返回给用户。由于中文转换拼音过程同上，在此不再列出代码。
更多的英文全文检索技巧请参看MySQL手册，这里不再一一介绍。上面便是整个MySQL中文全文检索实现过程。
本文所提供的方法并不能完全解决MySQL的全文检索问题，由于中文字词存在同音现象，比如："清华"和"轻划"检索结果相同，这也是不可避免的，但是对于MySQL的用户来说，该方法是目前不错的一种折衷手段。
（计算机世界报 2006年09月25日 第37期 B28、B29）
■ 杨宝昌
--------------------------------------------------------------------------------
因为中文词间并没有明显的区隔，所以中文的分词是按照字典、词库的匹配和词的频度统计，或是基于句法、语法分析的分词，而MySQL并不具备此功能，所以MySQL对中文全文检索的支持几乎为零。
目前很多网站和系统都提供了全文搜索功能，用户可以输入词或者语句来定位匹配的记录。在后台，可以使用Select查询中的Like语句来执行这种查询，尽管这种方法可行，但对于全文查找而言，这是一种效率极端低下的方法，尤其在处理大量数据的时候。MySQL针对这一问题提供了一种基于内建的全文查找方式的解决方案。开发者只需要简单地标记出需要全文查找的字段，然后使用MySQL方法在那些字段运行搜索，这不仅仅提高了性能和效率（因为MySQL对这些字段做了索引来优化搜索），而且实现了更高质量的搜索，因为MySQL使用自然语言来智能地对结果评级，以去掉不相关的项目。这就是MySQL的全文检索功能。
到MySQL3.23.23时,MySQL开始支持全文索引和搜索。全文索引在MySQL中是一个 FullText类型索引。FullText索引用于MyISAM表，可以在Create Table时或之后使用Alter Table或Create Index在Char、VarChar或TEXT列上创建。对于大的数据库，将数据装载到一个没有FullText索引的表中，然后再使用Alter Table (或Create Index) 创建索引，这将是非常快的。
但是，众所周知，在英文排版时词间是以空格区分的，所以英文的分词是基于空格的。但是涉及到中文等东亚文字就没有这么简单了，因为中文词间并没有明显的区隔，所以中文的分词则是按照字典、词库的匹配和词的频度统计，或是基于句法、语法分析的分词，而MySQL并不具备此功能，所以MySQL对中文全文检索的支持几乎为零。
需求及设计思想
来自NetCraft统计的数据，截至2006年4月份全球网站数量超过8065万个，目前，Linux及Apache在网站操作系统及Web服务器软件市场的份额为62.7%。再来看一份来自中国互联网络信息中心（CNNIC）的调查报告，截至2006年1月1日，全国有69.4万个网站，在线数据库数为29万5400个。由于中国使用LAMP(Linux、Apache、MySQL、PHP)套件的数量没有达到世界平均份额，所以估计有15万左右的基于PHP/MySQL构架的网站，这不包括5万个WAP网站、企业管理系统、教学应用系统等信息管理系统。如果全都算上，在中国使用MySQL的站点（系统）数量应该在15万至18万之间。
MySQL有如此庞大的使用规模，中文全文检索在MySQL的实现将是相当迫切，加之笔者在做一个网站系统的时候也是由于租用服务器硬性环境，不能自由选择数据库或者添加组件，所以只能根据已有的条件进行适应性改良。一个偶然的思路使笔者产生了能否将中文和英文建立特定关联，然后再进行检索呢？答案是可以的。MySQL不支持中文全文检索，但是支持英文全文检索，可以将“中文全文检索”转变为“英文全文检索”，然后再变回“中文全文检索”。那么中文又如何转换成英文呢？思路可以是：中文全文检索→拼音全文检索→中文全文检索。所以转换的思路如图1所示。
图1 中文全文检索思路
用户输入中文信息提交的时候，通过一个中文到拼音的转换程序将其数据转换为拼音的格式存入数据库中，然后用户检索时同样是输入中文，也将检索词转换为拼音，到数据库中进行匹配，返回结果。但是这样做有两个缺点:第一个是，检索是成功了，可是没有中文原数据，如何返回有效结果？第二个是，中文转换拼音如果中文篇幅过长，转换时间过长怎么办？所以催生了第二种解决方法，如图2所示。
图2 改进后的检索思路
如图2所示，用户输入中文按照正常方式直接提交至数据库，然后有一个后台转换程序循环执行，将中文转换为拼音存入另外一张全文检索表中，其他用户在进行全文检索时由于词语长度有限，所以可以采用即时转换拼音，然后进行数据库查询匹配。
MySQL中文全文检索实现
1．数据库表结构设计
此处用到两张表，以为人员信息建立全文检索为例，分为：人员信息表(UserInfo)和全文检索表(IndexSeek)，表结构如图3所示。
图3 人员信息表和全文检索表
人员信息表(UserInfo)各列含义为: ID(id)，自增序列;姓名(user_name);个人介绍(user_introduction)；全文检索状态(user_state)，新添加或者修改后状态为0，拼音转换完成状态为1。
全文检索表(IndexSeek) 各列含义为：ID(id)，自增序列；关联人员信息ID(user_id)，对应UserInfo表中ID；索引项(index_item)，该列设置为FullText全文检索；索引标示(index_mark)，标示为哪个列的索引，本文中涉及两个索引，分别为姓名(user_name)、介绍(user_introduction)。
两个表中的log字段记录该行修改的时间和产生的错误提示。在本文中无实际用途。
2．用户输入中文资料
用户自己输入信息添加至数据库，例如，各项为:“1”、“张三”、“爱好编程，PHP，MySQL”、“0”。
3．后台转换拼音过程
后台应用程序按照规则进行中文到拼音的转换，笔者在网上下载了内码→拼音转换数据包pinyin.dat，该文件的下载网址为：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();
}
}
//截取字符函数
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;
}
//计算字符长度的函数，汉字中文都当作是长度为1
function StrLenW($str)
{
$count = 0;
$len = strlen($str);
for($i=0; $i< $len; $i++,$count++)
if(ord($str[$i])>=128)
$i++;
return $count;
}
//批量中文转换拼音函数，转换后每个字符中间加上空格，判断非中文字符如符号，英文，数字则不作变动
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;
}
将该程序循环执行，查询user_state为0的行，取出user_name、user_introduction两列数据，分别进行拼音转换，然后插入全文检索表(IndexSeek)表中，添加两行数据，其中第一行为：
关联人员信息ID(user_id):1；//对应UserInfo表中ID，此处为1
索引项(index_item)：zhang1 san1；
索引标示(index_mark)： 姓名(user_name)。
第二行为：
关联人员信息ID(user_id)：1；//对应UserInfo表中ID，此处为1
索引项(index_item)：ai4 hao3 bian1 cheng2 ，PHP，MySQL；
索引标示(index_mark)：介绍(user_introduction)。
至此，添加中文数据转换拼音过程完毕。修改用户信息同样步骤进行，只是不再新添加表，删除数据则根据关联id全部删除。
4．用户检索过程
由于用户检索时输入的关键词数量有限，则采用页面应用程序直接转换拼音进行匹配。例如用户输入检索词为：“编程”，提交给服务器，由服务器端程序处理转换为“bian1 cheng2”进行数据表IndexSeek的全文检索，全文检索SQL为：
SELECT * FROM IndexSeekWHERE MATCH (index_item) AGAINST (' bian1 cheng2');
得出关联人员ID为1，然后取得用户资料表中全部信息返回给用户。由于中文转换拼音过程同上，在此不再列出代码。
更多的英文全文检索技巧请参看MySQL手册，这里不再一一介绍。上面便是整个MySQL中文全文检索实现过程。
本文所提供的方法并不能完全解决MySQL的全文检索问题，由于中文字词存在同音现象，比如："清华"和"轻划"检索结果相同，这也是不可避免的，但是对于MySQL的用户来说，该方法是目前不错的一种折衷手段。
（计算机世界报 2006年09月25日 第37期 B28、B29）
