xml地图|网站地图|网站标签 [设为首页] [加入收藏]

热门视频

当前位置:美高梅游戏网站 > 热门视频 > Mysql全文搜索match against的用法_Mysql_脚本之家

Mysql全文搜索match against的用法_Mysql_脚本之家

来源:http://www.gd-chuangmei.com 作者:美高梅游戏网站 时间:2019-11-26 15:15

开发Web应用时,你经常要加上搜索功能。甚至还不知能要搜什么,就在草图上画了一个放大镜。

对于大的数据库,将数据装载到一个没有 FULLTEXT 索引的表中,然后再使用 ALTER TABLE 创建索引,这将是非常快的。将数据装载到一个已经有 FULLTEXT 索引的表中,将是非常慢的。1.使用Mysql全文检索fulltext的先决条件 表的类型必须是MyISAM建立全文检索的字段类型必须是char,varchar,text2.建立全文检索先期配置由于Mysql的默认配置是索引的词的长度是4,所以要支持中文单字的话,首先更改这个.*Unix用户要修改my.cnf,一般此文件在/etc/my.cnf,如果没有找到,先查找一下find / -name 'my.cnf'在 [mysqld] 位置内加入: ft_min_word_len = 2 其它属性还有 ft_wordlist_charset = gbk ft_wordlist_file = /home/soft/mysql/share/mysql/wordlist-gbk.txt ft_stopword_file = /home/soft/mysql/share/mysql/stopwords-gbk.txt 稍微解释一下: ft_wordlist_charset 表示词典的字符集, 目前支持良好的有(UTF-8, gbk, gb2312, big5) ft_wordlist_file 是词表文件, 每行包括一个词及其词频 ft_stopword_file 表示过滤掉不索引的词表, 一行一个. ft_min_word_len 加入索引的词的最小长度, 缺省是 4, 为了支持中文单字故改为 2 3.建立全文检索在建表中用FullText关键字标识字段,已存在的表用 ALTER TABLE 创建索引CREATE fulltext INDEX index_name ON table_name;4.使用全文检索 在SELECT的WHERE字句中用MATCH函数,索引的关键词用AGAINST标识,IN BOOLEAN MODE是只有含有关键字就行,不用在乎位置,是不是起启位置.SELECT * FROM articles WHERE MATCH AGAINST ;5.详细的说明请参数Mysql官方网站 5.1的,不过4.X也可以做为参考,基本一置.我用的就是Mysql 4.1.

搜索是项非常重要的功能,所以像elasticsearch和SOLR这样的基于lucene的工具变得很流行。它们都很棒。但使用这些大规模“杀伤性”的搜索武器前,你可能需要来点轻量级的,但又足够好的搜索工具。

MySQL支持全文索引(Full-Text) 已经很久了,目前,fulltext是一种只适用于MyISAM表的一个索引类型,而且对定义索引列的数据类型也有限制,只能是以下三种的组合char、 varchar、text。fulltext可以在创建表的同时就一起定义好,或者在表创建完成之后,通过语句alter table或create index来追加索引,总之先后的效果是一样的,但是两者的效率却是存在很大差异的,大量的实验证明,对于大数量的表来说,先加载数据再来定义全文索引的 速度要远远优于在一个已经定义好全文索引的表里面插入大量数据的速度。一定会问:这是问什么呢?其实,道理很简单,前者只需要一次性对你的索引列表进行操 作,排序比较都是在内存中完成,然后写入硬盘;后者则要一条一条去硬盘中读取索引表然后再进行比较最后写入,自然这样速度就会很慢。MySQL是 通过match这两个函数来实现它的全文索引查询的功能。match()中的字段名称要和fulltext中定义的字段一致,如 果采用boolean模式搜索,也允许只包括fulltext中的某个字段,不需要全部列出。against()中定义的是所要搜索的字符串以及要求数据 库通过哪种模式去执行全文索引的搜索查询。下面通过一个例子分别介绍一下fulltext所支持的3中搜索模式。

所谓“足够好”,我是指一个搜索引擎拥有下列的功能:

MySQL全文索引与中文分词总结及一般的关键词搜索流程

词根 排名/提升 支持多种语言 对拼写错误模糊搜索 方言的支持

mysql 全文检索 中文分词

幸运的是PostgreSQL对这些功能全支持。

支持中文的MySQL 5.1+ 全文检索分词插件

使用PostgreSQL,同时又不想安装其它的搜索引擎。 使用其它的数据库,同时需要更好的全文搜索功能。

家用一下搜索引擎就会发现,分词的情况只是出现在当整词命中为0的情况下。 而具体怎样分词,大家可以参考一下baidu搜索试验结果: ·如果搜“徐祖宁宁”,结果为“徐祖”+“宁宁”。(搜人名的情况下,它可能有一个百家姓词典,自动将姓后第一个字归前) ·搜“徐宁愿”,结果为“徐宁愿”。(说明“宁愿”归“徐”所有。同上。因为徐是姓。) ·搜“徐祖宁愿”,结果为“徐祖”+“宁愿”。 ·搜“徐祖宁高”,结果为“徐祖宁”。(因为“宁高”不是关键字,所以“宁”归前词所有。而“高”可能因为是单字,为提高前词搜索效率故被省略。)

本文中我们将通过下面的表和数据说明PostgreSQL的全文搜索功能。

CREATE TABLE author( id SERIAL PRIMARY KEY, name TEXT NOT NULL);CREATE TABLE post( id SERIAL PRIMARY KEY, title TEXT NOT NULL, content TEXT NOT NULL, author_id INT NOT NULL references author;CREATE TABLE tag( id SERIAL PRIMARY KEY, name TEXT NOT NULL );CREATE TABLE posts_tags( post_id INT NOT NULL references post, tag_id INT NOT NULL references tag;INSERT INTO author  VALUES , , ; INSERT INTO tag  VALUES , , ; INSERT INTO post (id, title, content, author_id) VALUES (1, 'Endangered species', 'Pandas are an endangered species', 1 ), (2, 'Freedom of Speech', 'Freedom of speech is a necessary right missing in many countries', 2), (3, 'Star Wars vs Star Trek', 'Few words from a big fan', 3); INSERT INTO posts_tags  VALUES ;

这是一个类博客的应用。它有post表,带有title和content字段。post通过外键关联到author。post自身还有多个标签。

什么是全文搜索

首先,让我们看一下定义:

在文本检索中,全文搜索是指从全文数据库中搜索计算机存储的单个或多个文档的技术。全文搜索不同于基于元数据的搜索或根据数据库中原始文本的搜索。

这个定义中引入了文档的概念,这很重要。当你搜索数据时,你在寻找你想要找到的有意义的实体,这些就是你的文档。PostgreSQL的文档中解释地很好。

文档是全文搜索系统中的搜索单元。比如,一篇杂质文章或是一封邮件消息。

-- Postgres 文档

这里的文档可以跨多个表,代表为我们想要搜索的逻辑实体。构建我们的文档

上一节,我们介绍了文档的概念。文档与表的模式无关,而是与数据相关,把字段联合为一个有意义的实体。根据示例中的表的模式,我们的文档由这些组成:

post.title post.content post的author.name 关联到post的所有tag.name

根据这些要求产生文档,SQL查询应该是这样的:

SELECT post.title || ' ' || post.content || ' ' || author.name || ' ' || coalesce((string_agg as document FROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id; document -------------------------------------------------- Endangered species Pandas are an endangered species Pete Graham politics Freedom of Speech Freedom of speech is a necessary right missing in many countries Rachid Belaid politics Star Wars vs Star Trek Few words from a big fan Robert Berry politics

由于用post和author分组了,因为有多个tag关联到一个post,我们使用string_agg()作聚合函数。即使author是外键并且一个post不能有多个author,也要求对author添加聚合函数或者把author加到GROUP BY中。

我们还用了coalesce()。当值可以是NULL时,使用coalesce()函数是个很好的办法,否则字符串连接的结果将是NULL。

至此,我们的文档只是一个长string,这没什么用。我们需要用to_tsvector()把它转换为正确的格式。

SELECT to_tsvector || to_tsvector || to_tsvector || to_tsvector(coalesce((string_agg as documentFROM postJOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON tag.id = posts_tags.tag_idGROUP BY post.id, author.id; document -------------------------------------------------- 'endang':1,6 'graham':9 'panda':3 'pete':8 'polit':10 'speci':2,7'belaid':16 'countri':14 'freedom':1,4 'mani':13 'miss':11 'necessari':9 'polit':17 'rachid':15 'right':10 'speech':3,6'berri':13 'big':10 'fan':11 'polit':14 'robert':12 'star':1,4 'trek':5 'vs':3 'war':2 'word':7

这个查询将返回适于全文搜索的tsvector格式的文档。让我们尝试把一个字符串转换为一个tsvector。

SELECT to_tsvector('Try not to become a man of success, but rather try to become a man of value');

这个查询将返回下面的结果:

 to_tsvector----------------------------------------------------------------------'becom':4,13 'man':6,15 'rather':10 'success':8 'tri':1,11 'valu':17

发生了怪事。首先比原文的词少了,一些词也变了,而且后面还有数字。怎么回事?

一个tsvector是一个标准词位的有序列表,标准词位就是说把同一单词的各种变型体都被标准化相同的。

标准化过程几乎总是把大写字母换成小写的,也经常移除后缀。这样可以搜索同一个字的各种变体,而不是乏味地输入所有可能的变体。

数字表示词位在原始字符串中的位置,比如“man"出现在第6和15的位置上。你可以自己数数看。

Postgres中to_tesvetor的默认配置的文本搜索是“英语“。它会忽略掉英语中的停用词(stopword,译注:也就是am is are a an等单词)。

这解释了为什么tsvetor的结果比原句子中的单词少。后面我们会看到更多的语言和文本搜索配置。

查询

我们知道了如何构建一个文档,但我们的目标是搜索文档。我们对tsvector搜索时可以使用@@操作符,使用说明见此处。看几个查询文档的例子。

> select to_tsvector('If you can dream it, you can do it') @@ 'dream'; ?column?---------- t > select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible'; ?column?---------- f

第二个查询返回了假,因为我们需要构建一个tsquery,使用@@操作符时,把字符串转型成了tsquery。下面显示了这种l转型和使用to_tsquery()之间的差别。

SELECT 'impossible'::tsquery, to_tsquery; tsquery | to_tsquery--------------+------------ 'impossible' | 'imposs'

但"dream"的词位与它本身相同。

SELECT 'dream'::tsquery, to_tsquery; tsquery | to_tsquery--------------+------------ 'dream' | 'dream'

从现在开始我们使用to_tsquery查询文档。

 SELECT to_tsvector('It''s kind of fun to do the impossible') @@ to_tsquery; ?column?---------- t

tsquery存储了要搜索的词位,可以使用&逻辑操作符。可以使用圆括号给操作符分组。

> SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery; ?column?---------- f > SELECT to_tsvector('If the facts don''t fit the theory, change the facts') @@ to_tsquery; ?column?---------- f > SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery; ?column?---------- t

我们也可以使用:*来表达以某词开始的查询。

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery; ?column?---------- t

既然我们知道了怎样使用全文搜索查询了,我们回到开始的表模式,试着查询文档。

SELECT pid, p_titleFROM (SELECT post.id as pid, post.title as p_title, to_tsvector || to_tsvector || to_tsvector || to_tsvector(coalesce(string_agg as document FROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id) p_search WHERE p_search.document @@ to_tsquery('Endangered & Species'); pid | p_title-----+-------------------- 1 | Endangered species

这个查询将找到文档中包含Endangered和Species或接近的词。

语言支持

Postgres 内置的文本搜索功能支持多种语言: 丹麦语,荷兰语,英语,芬兰语,法语,德语,匈牙利语,意大利语,挪威语,葡萄牙语,罗马尼亚语,俄语,西班牙语,瑞典语,土耳其语。

SELECT to_tsvector('english', 'We are running'); to_tsvector------------- 'run':3SELECT to_tsvector('french', 'We are running'); to_tsvector---------------------------- 'are':2 'running':3 'we':1

基于我们最初的模型,列名可以用来创建tsvector。 假设post表中包含不同语言的内容,且它包含一列language。

ALTER TABLE post ADD language text NOT NULL DEFAULT;

为了使用language列,现在我们重新编译文档。

SELECT to_tsvector(post.language::regconfig, post.title) || to_tsvector(post.language::regconfig, post.content) || to_tsvector('simple', author.name) || to_tsvector('simple', coalesce((string_agg as documentFROM postJOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON tag.id = posts_tags.tag_idGROUP BY post.id, author.id;

如果缺少显示的转化符::regconfig,查询时会产生一个错误:

ERROR: function to_tsvector does not exist

regconfig是对象标识符类型,它表示Postgres文本搜索配置项。:

现在,文档的语义会使用post.language中正确的语言进行编译。

我们也使用simple,它也是Postgres提供的一个文本搜索配置项。simple并不忽略禁用词表,它也不会试着去查找单词的词根。使用simple时,空格分割的每一组字符都是一个语义;对于数据来说,simple文本搜索配置项很实用,就像一个人的名字,我们也许不想查找名字的词根。

SELECT to_tsvector('simple', 'We are running'); to_tsvector---------------------------- 'are':2 'running':3 'we':1

当你建立一个搜索引擎支持多种语言时你也需要考虑重音问题。在许多语言中重音非常重要,可以改变这个词的含义。Postgres附带一个unaccent扩展去调用 unaccentuate内容是有用处的。

CREATE EXTENSION unaccent;SELECT unaccent; unaccent---------- eeee

让我们添加一些重音的你内容到我们的post表中。

INSERT INTO post (id, title, content, author_id, language) VALUES (4, 'il était une fois', 'il était une fois un h?tel ...', 2,'french')

如果我们想要忽略重音在我们建立文档时,之后我们可以简单做到以下几点:

SELECT to_tsvector(post.language, unaccent || to_tsvector(post.language, unaccent || to_tsvector('simple', unaccent || to_tsvector('simple', unaccent(coalesce(string_aggJOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON author.id = post.author_idGROUP BY p.id

这样工作的话,如果有更多错误的空间它就有点麻烦。 我们还可以建立一个新的文本搜索配置支持无重音的字符。

本文由美高梅游戏网站发布于热门视频,转载请注明出处:Mysql全文搜索match against的用法_Mysql_脚本之家

关键词: