在postgresql中使用全文索引介绍
在近期的开发中由于需要处理比较大的数据量,但是由于没有资金购买商业数据库,而且最好不能用盗版的,所以没办法就用了这个Postgresql对象关系型数据库。首先介绍一下这个数据库,百度百科:“PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、多版本并发控制。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言。并且,因为许可证的灵活,任何人都可以以任何目的免费使用,修改,和分发 PostgreSQL, 不管是私用,商用,还是学术研究使用”。
它是和mysql一样开源的数据库,但是其功能却比mysql强大多了,它可以处理上百万甚至上千万条的数据,据我了解mysql到来100W条数据时,其效率将会受到严重的影响。本篇文章主要讲一下给数据库全文索引的基本使用。全文索引在商业型数据管理系统中是非常常用的,因为它可以提高数据的检索效率,在没有全文索引的时候我们一般都是用like子句来匹配我们搜索的关键词,我们知道like子句执行的效率是非常低的,这严重影响了系统的运行效率。因此全文索引就可以提高数据的检索效率。全文索引是为我们需要被用户检索的信息进行创建全文索引,独立的存储在一个表中或者是一个字段上面。一下面将就在表中和字段中创建全文索引来进行介绍。
一、使用独立的表存储全文索引的信息
下面我举例介绍:(注意postgresql在8.3以后在自带了全文索引,以前的版本需要安装其他的包,具体我也不是很清楚,有兴趣的可以自己看看)
首先我们创建一个数据表:
CREATE TABLE author
(
id bigserial NOT NULL,
"name" text NOT NULL,
author_key text NOT NULL,
CONSTRAINT author_key PRIMARY KEY (id),
CONSTRAINT "key" UNIQUE (author_key)
)
WITH (
OIDS=FALSE
);
我们为该表的的name字段创建一个全文索引,并将它的信息独立的存储在一个表中,表如下:
CREATE TABLE full_text_index
(
id bigserial NOT NULL,
text_fti tsvector NOT NULL,)WITH ( OIDS=FALSE);ALTER TABLE full_text_index OWNER TO postgres;
);
我们注意看字段text_fti,它的类型是tsvector,说明该字段是存储全文索引的,这就是postgresql数据库中全文索引的字段类型。
那么如何将我们的信息自动的创建全文索引信息添加到我们的full_text_index 中呢?那么这里将要用到sql中的触发器,
如下创建触发器函数:
CREATE OR REPLACE FUNCTION node_text_fti()
RETURNS trigger AS
$BODY$begin
if TG_OP = 'INSERT' then
insert into full_text_index (text_fti) values(to_tsvector(coalesce(NEW.name,'')));
end if;
return NEW;end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION node_text_fti() OWNER TO postgres;
上面的insert语句的values中一个to_tsvector(coalesce(NEW.name,'')),这就是这个数据库中一个创建全文索引的函数,我们通过调用它,并把相应需创建索引的值给他,就会自动给我们分词。同时需要在author表中创建一个触发器:
CREATE TRIGGER create_author_index
AFTER INSERT
ON author
FOR EACH ROW
EXECUTE PROCEDURE node_text_fti();
这样在你向author表中插入数据的时候将会自动在full_text_index中创建全文索引,此处需要注意的是,需要将这两个表关联起来,可以设置相同的自动递增字段id,那么可以通过这个id来关联这两个表,从而使得在full_text_index表中查询能够映射到author表中。在查询的时候需要通过如下的sql语句进行查询:
select * from author as a join full_text_index as fti on a.id=fti.id where fti.text_fti @@ to_tsquery('keyword');
通过join运算将两个表关联起来,这就实现了全文索引,一般情况下都是采用使用一个独立的表来存储全文索引信息。
二、使用字段存储全文索引信息
首先也创建一个数据表:
CREATE TABLE test_fti
(
"ID" integer NOT NULL DEFAULT nextval('"testFTI_ID_seq"'::regclass),
"content" text NOT NULL,
content_fti tsvector,
CONSTRAINT "testID" PRIMARY KEY ("ID")
)
WITH (
OIDS=FALSE
);
ALTER TABLE test_fti OWNER TO postgres;
此处的字段content_fti则是存储全文索引的信息,此处也类似需要创建触发器函数:
CREATE OR REPLACE FUNCTION messages_trigger()
RETURNS trigger AS
$BODY$
begin
new.content_fti :=
setweight(to_tsvector( coalesce(new.content,'')));
return new;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION messages_trigger() OWNER TO postgres;
在表test处创建一个触发器:
CREATE TRIGGER ftitesttrigger
BEFORE INSERT OR UPDATE
ON test_fti
FOR EACH ROW
EXECUTE PROCEDURE messages_trigger();
这是在你想test中插入数据的时候就会自动将指定字段的信息创建全文索引,并添加到对应的记录中,此处的查询语句就比较简单:
select * from test where content_fti @@ to_tsquery('keyword');
本文到次就将在postgresql中简单使用全文索引介绍完了,介绍有不到位支出希望指出,谢谢浏览!
分享到:
相关推荐
PostgreSQL数据库内核分析PostgreSQL数据库内核分析PostgreSQL数据库内核分析PostgreSQL数据库内核分析PostgreSQL数据库内核分析PostgreSQL数据库内核分析PostgreSQL数据库内核分析PostgreSQL数据库内核分析...
C# 操作PostgreSQL 数据库 .
《PostgreSQL数据库内核分析》彭智勇 完整版44M
PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统(RDBMS),是一个非常健壮的软件包,有很多在大型商业RDBMS中所具有的特性,包括事务、子选择、触发器、视图、外键引用完整性和复杂...
postgresql数据库jdbc驱动,jar包。。。。。。。。。。。。
postGREsql数据库资料,经典资料,带完整书签。可以复制文字
asp连接postgresql数据库 的源码
Python编写PostgreSQL数据库结构比对程序源代码,该源代码导入PyCharm即可运行使用。
1.nacos服务,适配postgresql数据库。 2.提供nacos,postgresql的创建nacos数据库脚本。 3.nacos/conf/nacos-pg.sql数据库脚本文件。 4.nacos版本1.4.2。
PostgreSQL数据库内核分析完整版。 作者简介: 彭智勇:武汉大学计算机学院教授,博士生导师,日本京都大学博士,武汉大学珞珈特聘教授,中国计算机学会数据库专业委员会委员,长期从事数据库理论与技术的研究和教学...
如何恢复PostgreSQL数据库. 如何恢复PostgreSQL数据库
postgresql数据库定时备份脚本(linux),亲测可用总结
Window 下的PostgreSQL 数据库备份和恢复工具[GUI],供大家一起共同分享使用。
postgresQL数据库备份脚本,添加到任务计划中就可以备份数据库文件了。
Linux下的PostgreSQL数据库配置与管理
PostgreSQL数据库对象名大小写敏感