PGroonga 官网https://pgroonga.github.io/DescriptionPGroonga (píːzí:lúnɡά) is a PostgreSQL extension to use Groonga as the index.PostgreSQL supports full text search against languages that use only alphabet and digit. It means that PostgreSQL doesn’t support full text search against Japanese, Chinese and so on. You can use super fast full text search feature against all languages by installing PGroonga into your PostgreSQL!一、安装相关依赖包yuminstallwgetcurltargzipgcc gcc-cmakezlib zlib-devel msgpack msgpack-devel mecab mecab-devel lz4 lz4-devel二、下载安装 git注意编译要求Git版本为 V2.7.4及以上版本wgethttps://www.kernel.org/pub/software/scm/git/git-2.7.4.tar.gz --no-check-certificatetar-vzxfgit-2.7.4.tar.gzcdgit-2.7.4/#Configure./configure --with-openssl/usr/local/openssl#编译安装makemakeinstall#打开操作系统环境变量配置文件修改环境变量vi/etc/profile#在底部加上git相关配置exportPATH$PATH:/usr/local/git-2.7.4#:wq保存source命令生效source/etc/profile#查看git版本git--version三、下载编译安装 groongaDescriptionGroonga is an open-source fulltext search engine and column store. It lets you write high-performance applications that requires fulltext search.官网https://groonga.org/编译安装https://groonga.org/docs/install/centos.html#centos-7PS下载source进行源码编译官网上的groonga-release-latest.noarch.rpm直接在本地安装会有问题可能会存在文件缺失。wgethttps://packages.groonga.org/source/groonga/groonga-13.0.9.tar.gz --no-check-certificatetar-xvzfgroonga-13.0.9.tar.gzcdgroonga-13.0.9#Configure./configure#编译Buildmake-j$(grep^processor/proc/cpuinfo|wc-l)#installsudomakeinstall#install之后执行如下命令查看当前系统安装了哪些库pkg-config --list-all#查看是否能查到groonga若没有查到groonga头文件和库文件的位置编译器无法使用需要设置PKG_CONFIG_PATH环境变量#查找groonga.pc的位置find/-namegroonga.pc#设置PKG_CONFIG_PATH环境变量exportPKG_CONFIG_PATH/usr/local/lib/pkgconfig/#查看是否可输出groongapkg-config --list-all#正常可输出groonga Groonga - An Embeddable Fulltext Search Engine四、下载安装 xxHashDescription:xxHash is an Extremely fast Hash algorithm, processing at RAM speed limits. Code is highly portable, and produces hashes identical across all platforms (little / big endian).Vcpkg用于在Windows、Linux、Mac上管理C和C库极大简化了第三方库的安装它由微软开源源码地址https://github.com/Microsoft/vcpkg最新发布版本为2023.04.15 Release它的license为MIT。Building xxHash - Using vcpkgYou can download and install xxHash using the vcpkg dependency managergitclone https://github.com/Microsoft/vcpkg.gitcdvcpkg./bootstrap-vcpkg.sh ./vcpkg integrateinstall./vcpkginstallxxhash五、下载安装 pgroongagitclone--recursivehttps://github.com/pgroonga/pgroonga.gitcdpgroongamakemakeinstall六、登录数据库psql highgo sysdbahighgo# select * from pg_available_extensions where name like %roon%;name|default_version|installed_version|comment--------------------------------------------------------------------------------------------------------------pgroonga|3.1.6|3.1.6|Super fastandalllanguages supportedfulltextsearchindexbasedonGroonga pgroonga_database|3.1.6||PGroongadatabasemanagement module highgo# create extension pgroonga;错误: 扩展pgroonga已经存在 highgo# \dx已安装扩展列表 名称|版本|架构模式|描述----------------------------------------------------------------------------------------------------------------------------------------------hg_mac|1.0|information_schema|hgdb mandatory access control withoutusingselinux hg_permission|1.0|information_schema|hg permission mysqlface|1.0|public|administrative functionsforPostgreSQL orafce|3.9|public|Functionsandoperators that emulate a subsetoffunctionsandpackagesfromthe Oracle RDBMS passwordcheck|1.0|information_schema|passwordcheck pg_buffercache|1.3|public|examine the shared buffer cache pg_stat_statements|1.7|public|track executionstatisticsofallSQLstatements executed pgroonga|3.1.6|public|Super fastandalllanguages supportedfulltextsearchindexbasedonGroonga plpgsql|1.0|pg_catalog|PL/pgSQL procedurallanguagezhfts|1.1|public|RUMindexaccess method zhparser|2.2|public|a parserforfull-textsearchofChinese(11行记录)七、使用1、启用全文搜索作为文本类型的列CREATETABLEmemos(idinteger,contenttext);CREATEINDEXpgroonga_content_indexONmemosUSINGpgroonga(content);INSERTINTOmemosVALUES(1,PostgreSQL is a relational database management system.);INSERTINTOmemosVALUES(2,Groonga is a fast full text search engine that supports all languages.);INSERTINTOmemosVALUES(3,PGroonga is a PostgreSQL extension that uses Groonga as index.);INSERTINTOmemosVALUES(4,There is groonga command.);SETenable_seqscanoff;There are the following operators to perform full text search:~LIKEILIKE~operator You canuse~operatortoperformfulltextsearchbyquery syntax suchaskeyword1ORkeyword2: highgo# SELECT * FROM memos WHERE content ~ PGroonga OR PostgreSQL;id|content--------------------------------------------------------------------1|PostgreSQLisa relationaldatabasemanagement system.3|PGroongaisa PostgreSQL extension that uses Groongaasindex.(2行记录) operator You canuse operatortoperformfulltextsearchbyone keyword: highgo# SELECT * FROM memos WHERE content engine;id|content----------------------------------------------------------------------------2|Groongaisa fastfulltextsearchenginethat supportsalllanguages.(1行记录)LIKEoperator PGroonga supportsLIKEoperator.You can perform fastfulltextsearchbyPGroonga without changing existingSQL.columnLIKE%keyword%almost equalstocolumnkeyword: highgo# SELECT * FROM memos WHERE content LIKE %engine%;id|content----------------------------------------------------------------------------2|Groongaisa fastfulltextsearchenginethat supportsalllanguages.(1行记录)2、Score匹配精度排序You can usepgroonga.scorefunction to get precision as a number. If a record is more precision against searched query, the record has more higher number.You need to add primary key column intopgroongaindex to usepgroonga.scorefunction. If you don’t add primary key column intopgroongaindex,pgroonga.scorefunction always returns0.Here is a sample schema that includes primary key into indexed columns:CREATETABLEscore_memos(idintegerPRIMARYKEY,contenttext);CREATEINDEXpgroonga_score_memos_content_indexONscore_memosUSINGpgroonga(id,content);INSERTINTOscore_memosVALUES(1,PostgreSQL is a relational database management system.);INSERTINTOscore_memosVALUES(2,Groonga is a fast full text search engine that supports all languages.);INSERTINTOscore_memosVALUES(3,PGroonga is a PostgreSQL extension that uses Groonga as index.);INSERTINTOscore_memosVALUES(4,There is groonga command.);SETenable_seqscanoff;--执行全文检索并获得分数SELECT*,pgroonga_score(tableoid,ctid)FROMscore_memosWHEREcontentPGroongaORcontentPostgreSQL;id|content|pgroonga_score------------------------------------------------------------------------------------1|PostgreSQLisa relationaldatabasemanagement system.|13|PGroongaisa PostgreSQL extension that uses Groongaasindex.|2(2行记录)--可以使用ORDER by子句中的pgroonga_score函数按精度降序对匹配的记录进行排序highgo# SELECT *, pgroonga_score(tableoid, ctid)highgo-# FROM score_memoshighgo-# WHERE content PGroonga OR content PostgreSQLhighgo-# ORDER BY pgroonga_score(tableoid, ctid) DESC;id|content|pgroonga_score------------------------------------------------------------------------------------3|PGroongaisa PostgreSQL extension that uses Groongaasindex.|21|PostgreSQLisa relationaldatabasemanagement system.|1(2行记录)更多用法详见官网https://pgroonga.github.io/tutorial/Author HGDB-QIURU
