PostgreSQL 8.3 以上的中文全文索引使用介绍
From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community
Jump to: navigation, search
目录
[隐藏]
1 前提
1.1 安装 PostgreSQL 4E 包
1.2 初始化 PostgreSQL 4E 包
1.3 配置PostgreSQL 4E 包
2 概念
3 实例
3.1 场景设计
3.2 设计相关表
3.3 书写触发器
3.3.1 触发器函数
3.3.2 创建触发器
3.4 创建全文索引
3.5 运行上面SQL代码
3.6 测试！
3.7 全文搜索
3.8 获取摘要
[编辑] 前提
[编辑] 安装 PostgreSQL 4E 包
使用全文索引最简单的方法是使用 4E 的 PostgreSQL 包，已经有大量预编译好的包存在，可以在获取安装包里头找到。
[编辑] 初始化 PostgreSQL 4E 包
参考管理员手册里面的初始化 PostgreSQL 和/或集群。
[编辑] 配置PostgreSQL 4E 包
参考管理员手册里面的配置 PostgreSQL 和/或集群。
特别要注意的是default_text_search_config的配置，一定要按照配置来搞。
[编辑] 概念
基于 PostgreSQL 的全文搜索的使用是很简单的，其基本概念是：
在数据库中有个特殊的数据类型，叫做 tsvector；
tsvector 代表“语意”，语意就是一个词的概念；
tsvector 中包括词和词的位置，用于相关性（ranking）的计算；
数据库具备完整的框架：自动分词和计算每个词的位置；
数据库具备完整的框架：可以由用户选定分词方法（模块）和使用的词典；
数据库对 tsvector 类型进行倒排索引或者是深度的b-tree（平衡树索引）提供快速的检索服务；
索引本身如同标准数据库字段一样，是在INSERT和UPDATE的时候自动更新的；
查询是以SQL语言方式进行的
标准的 SELECT
一些特殊的操作符，比如 @@ 是全文匹配的操作符
一个特殊的 ts_query 类型，用于对查询本身进行分词、标记位置等处理，通过全文匹配操作符与 tsvector 类型进行比对
更多信息可以参考官方的文档
[编辑] 实例
[编辑] 场景设计
假设我们设计一个保存BBS的帖子的表，为了简化，我们只有两个字段：标题和正文，表结构如下：
[编辑] 设计相关表
我们设计一个很简单的表：
create table content(
id serial primary key,
subject text,
body text);
其中 subject 字段保存帖子的标题，body字段保存帖子的正文。
然后我们要设计一个保存全文索引内容的表：
create table content_search(
id integer primary key,
subject_fti tsvector,
body_fti tsvector,
foreign key (id) references content(id));
注意这里的数据类型：tsvector，是PostgreSQL为了全文索引专门制作的数据类型。请用这个类型保存分词后的内容字段。
[编辑] 书写触发器
写一个更新全文的触发器，触发器的文档详情可以参考：
http://www.pgsqldb.org/pgsqldoc-8.1c/plpgsql-trigger.html
我在这里直接写代码，很简单：
[编辑] 触发器函数
create or replace function content_trigger() returns trigger as $
begin
if TG_OP = 'INSERT' then
insert into content_search(id, subject_fti, body_fti) values(NEW.id,
to_tsvector(NEW.subject), to_tsvector(NEW.body));
end if;
return NEW;end;$
language plpgsql;
我这里先考虑了INSERT的情况，UPDATE的一会儿更新版本的时候再说。
[编辑] 创建触发器
create trigger content_table_trig after insert or update on content
for each row execute procedure content_trigger();
[编辑] 创建全文索引
给全文索引的表创建索引，我们需要建两个，给标题和正文各自创建一个：
create index content_search_subject_idx on content_search using gin(subject_idx);
create index content_search_body_idx on content_search using gin(body_idx);
这里using后头也可以用gist类型的索引，我在gist和gin的索引类型的区别里讨论他们的区别。
[编辑] 运行上面SQL代码
我们创建所有这些对象，方法是把上面的SQL语句保存在一个文件里，比如文件名叫 my_fti_test.sql，然后用psql执行：
/usr/local/pgsql/bin psql -d testdb -f my_fti_test.sql
你可能需要换成自己的数据库名字（-d 后面的），如果还没有数据库的话，可能需要创建之：
/usr/local/pgsql/bin createdb testdb
这里的psql和 createdb的路径如果用安装包的话，缺省在 /usr/local/pgsql/bin里头，我在上面使用了缺省的路径。
[编辑] 测试！
添加些测试数据：
insert into content(subject, body) values('linux和linux',
'linux,linux,linux,linux,linux,linux,linux,linux,linux');
insert into content(subject, body) values('linux使用和linux安装',
'linux,linux,linux,linux,linux应用是一个很复杂的问题');
insert into content(subject, body) values('linux应用', 'linux应用是一个
很复杂的问题');
insert into content(subject, body) values('linux',
'linux,linux,linux,linux,linux,linux,linux,linux');
注意这里我忽略了 ID 字段，因为我用了缺省值。
可以查询一下这些表：
select * from content;
select * from content_search;
看看是啥东西。
[编辑] 全文搜索
执行下面的查询：
select subject from mail_archive where id in (
select id from mail_archive_fti where subject_fti @@ 'linux' order by
ts_rank_cd(subject_fti, to_tsquery('linux')), ts_rank_cd(mail_body_fti,
to_tsquery('linux')) );
注意，我们已经有基本的排序：先看subject的关键字密度，然后再看正文的。下面的章节将介绍更多的内容。
[编辑] 获取摘要
搜索结果都希望能把摘要拿出来，而不是其中一段东西，那么，这个事情在 4E 的 PostgreSQL 包里面可以这么用：
select fts_summary(mail_body, to_tsquery('linux'), 'HighlightAll=1') from
mail_archive where id in
(select id from mail_archive_fti where mail_body_fti @@ to_tsquery('linux')) limit 1;
注意一下输出的结果，会发现在搜索词周围添加了的高亮标识，而且，查出来的东西就是关键词左近的一些原文。