PostgreSQL pg_trgm 模糊搜索完全指南
更新時間：2025年11月12日 10:18:48 作者：中年如酒
PostgreSQL的pg_trgm擴(kuò)展通過三字符組合實(shí)現(xiàn)模糊文本搜索,支持處理拼寫錯誤和部分匹配,下面就來詳細(xì)的介紹一下PostgreSQL pg_trgm 模糊搜索完全指南,感興趣的可以了解一下
什么是 pg_trgm？
pg_trgm 是 PostgreSQL 的一個擴(kuò)展模塊，用于實(shí)現(xiàn)基于三元組（trigram）的模糊文本搜索。它可以幫你找到拼寫錯誤、部分匹配的文本，非常適合搜索功能。
三元組（Trigram）原理
三元組是將文本分解為連續(xù)的三個字符的組合：
SELECT show_trgm('iPhone');
-- 結(jié)果: {" i"," ip","hon","iph","ne ","one","pho"}
通過比較兩個字符串的三元組重疊度，可以計算相似度。
一、環(huán)境準(zhǔn)備
1. 創(chuàng)建擴(kuò)展
CREATE EXTENSION IF NOT EXISTS pg_trgm;
2. 創(chuàng)建測試表
CREATE TABLE products ( tenant_id uuid, id integer, name text, description text, PRIMARY KEY (tenant_id, id) );
3. 創(chuàng)建索引（性能關(guān)鍵）
有兩種索引類型可選：
-- GiST 索引：平衡型，更新快，占用空間小 CREATE INDEX trgm_idx_products_name ON products USING gist (name gist_trgm_ops); -- GIN 索引：查詢更快，但更新慢，占用更多空間 CREATE INDEX trgm_gin_idx_products_name ON products USING gin (name gin_trgm_ops);
選擇建議：
- 讀多寫少 → 用 GIN
- 頻繁更新 → 用 GiST
二、插入測試數(shù)據(jù)
-- 插入產(chǎn)品（注意第二條有拼寫錯誤 "iPhne"）
INSERT INTO products (tenant_id, id, name, description) VALUES
('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'iPhone 13 Pro', 'Latest Apple smartphone'),
('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'iPhne 13', 'Budget Apple smartphone'),
('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Samsung Galaxy S21', 'Android flagship phone');
三、核心查詢方法
1. 計算相似度
-- 返回 0 到 1 之間的數(shù)字（1 = 完全相同）
SELECT similarity('iPhone', 'iPhne');
-- 結(jié)果: 0.5454545
2. 整體相似度匹配（% 操作符）
-- 查找與 'iPhone' 相似的產(chǎn)品名 SELECT name, similarity(name, 'iPhone') AS sim FROM products WHERE name % 'iPhone' -- 相似度超過閾值 ORDER BY sim DESC;
輸出結(jié)果：
name | sim
---------------+------------
iPhone 13 Pro | 0.5
iPhne 13 | 0.33333334
(2 rows)
3. 子串相似度匹配（%> 操作符）
適合搜索包含某個詞的文本：
-- 查找包含 'phone' 的產(chǎn)品 SELECT name, similarity(name, 'phone') AS sim FROM products WHERE name %> 'phone' ORDER BY sim DESC;
輸出：
name | sim
---------------+------------
iPhone 13 Pro | 0.33333334
(1 row)
四、高級功能
1. 調(diào)整相似度閾值
默認(rèn)閾值是 0.3，可以調(diào)整
SET pg_trgm.similarity_threshold = 0.5;
再次查詢，只返回相似度 ≥ 0.5 的結(jié)果
SELECT name FROM products WHERE name % 'iPhone';
2. 單詞相似度（Word Similarity）
更適合匹配完整單詞：
- word_similarity: 從左到右查找最相似的詞
SELECT name, word_similarity('iPhone', name) as sim
FROM products
ORDER BY sim DESC;- strict_word_similarity: 更嚴(yán)格的單詞邊界匹配
SELECT name, strict_word_similarity('iPhone', name) as sim
FROM products
ORDER BY sim DESC;
五、實(shí)戰(zhàn)場景
場景 1：容錯搜索（處理拼寫錯誤）
方法 1：降低相似度閾值
- 先查看相似度
SELECT similarity('iPhone 13 Pro', 'ipone'); -- 結(jié)果約 0.25
- 臨時降低閾值（針對單次查詢）
BEGIN; SET LOCAL pg_trgm.similarity_threshold = 0.2; SELECT name, similarity(name, 'ipone') AS score FROM products WHERE tenant_id = 'd1c06023-3421-4fbb-9dd1-c96e42d2fd02' AND name % 'ipone' ORDER BY score DESC LIMIT 5; COMMIT; -- 或 ROLLBACK，閾值會自動恢復(fù)
輸出：
name | score
---------------+-------
iPhone 13 Pro | 0.25
iPhne 13 | 0.25
(2 rows)
方法 2：不用 % 操作符（推薦）
直接用 similarity() 函數(shù)，手動過濾：
SELECT name, similarity(name, 'ipone') AS score FROM products WHERE tenant_id = 'd1c06023-3421-4fbb-9dd1-c96e42d2fd02' AND similarity(name, 'ipone') > 0.15 -- 自定義閾值 ORDER BY score DESC LIMIT 5;
注意：方法 2 性能較差（無法用索引），適合小數(shù)據(jù)集。對于大表，使用方法 1 配合索引。
場景 2：自動補(bǔ)全（更好的解決方案）
用戶輸入 “iPh”，顯示候選項。使用 word_similarity 更適合前綴匹配：
SELECT name, word_similarity('iPh', name) AS score
FROM products
WHERE tenant_id = 'd1c06023-3421-4fbb-9dd1-c96e42d2fd02'
AND 'iPh' <% name -- word_similarity 操作符
ORDER BY score DESC
LIMIT 10;
或使用 LIKE（性能更好）：
SELECT name FROM products WHERE tenant_id = 'd1c06023-3421-4fbb-9dd1-c96e42d2fd02' AND name ILIKE 'iPh%' -- 大小寫不敏感 ORDER BY name LIMIT 10;
場景 3：去重（找到相似的重復(fù)數(shù)據(jù)）
SELECT p1.name, p2.name, similarity(p1.name, p2.name) AS sim FROM products p1 JOIN products p2 ON p1.id < p2.id WHERE p1.tenant_id = p2.tenant_id AND p1.name % p2.name AND similarity(p1.name, p2.name) > 0.7 ORDER BY sim DESC;
六、函數(shù)與操作符
主要函數(shù)
- similarity (text, text)：返回兩個字符串的相似度（0 至 1 之間）
- show_trgm (text)：顯示字符串中的三元組
- word_similarity (text, text)：返回基于單詞的相似度
- strict_word_similarity (text, text)：返回嚴(yán)格基于單詞的相似度
- show_limit ()：顯示當(dāng)前的相似度閾值
七、操作符速查表
| 操作符 | 示例 | 說明 |
|---|---|---|
| % | 相似度匹配 | name % ‘iPhone’ |
| %> | 子串相似度（左邊在右邊中） | ‘phone’ %> name |
| < % | 子串相似度（右邊在左邊中） | name <% ‘phone’ |
| similarity() | 計算相似度分?jǐn)?shù) | similarity(name, ‘iPhone’) |
| word_similarity() | 單詞相似度 | word_similarity(‘iPhone’, name) |
大小寫敏感性：默認(rèn)區(qū)分大小寫，可以用 LOWER() 轉(zhuǎn)換
WHERE LOWER(name) % LOWER('iphone')
八、索引類型
pg_trgm 支持兩種索引類型:
GiST：
CREATE INDEX trgm_gist_idx ON table_name USING gist (column_name gist_trgm_ops);
- 搜索與更新的性能平衡
- 更小的索引體積
- 適用于動態(tài)數(shù)據(jù)
- GIN索引：
CREATE INDEX trgm_gin_idx ON table_name USING gin (column_name gin_trgm_ops);
- 搜索速度更快
- 更新速度較慢
- 索引體積更大
- 更適用于靜態(tài)數(shù)據(jù)
九、最佳實(shí)踐
1.索引選擇
- 以讀取操作為主的數(shù)據(jù)，使用 GIN 索引
- 頻繁更新的數(shù)據(jù)，使用 GiST 索引
- 僅為頻繁搜索的列創(chuàng)建索引
2.閾值調(diào)整
- 較低閾值（如 0.2）可獲得更多匹配結(jié)果
- 較高閾值（如 0.5）可實(shí)現(xiàn)更嚴(yán)格的匹配
- 結(jié)合自身數(shù)據(jù)測試，找到最優(yōu)值
3.性能優(yōu)化
- 全詞匹配場景使用 word_similarity () 函數(shù)
- 僅對特定列創(chuàng)建索引，而非所有文本列
- 監(jiān)控索引體積，必要時重建索引
十、性能注意事項
- GIN 索引搜索速度更快，但更新速度較慢
- 包含大量唯一值的文本列，索引體積可能較大
- 大型表可考慮使用部分索引
- 根據(jù)誤報 / 漏報率，監(jiān)控并調(diào)整相似度閾值
十一、局限性
- 不適用于極短字符串（少于 3 個字符）
- 可能產(chǎn)生誤報結(jié)果
- 大型文本列的索引體積可能較大
- 不適合精確匹配（建議使用標(biāo)準(zhǔn)索引）
十二、總結(jié)
pg_trgm 是實(shí)現(xiàn)模糊搜索的強(qiáng)大工具，適用于：
- 模糊搜索功能
- 拼寫檢查建議
- 自動補(bǔ)全功能
- 查找相似產(chǎn)品名稱
- 匹配含拼寫錯誤的地址
- 容忍拼寫錯誤的搜索
關(guān)鍵是創(chuàng)建合適的索引和調(diào)整相似度閾值，就能在保證性能的前提下提供出色的用戶體驗。
到此這篇關(guān)于PostgreSQL pg_trgm 模糊搜索完全指南的文章就介紹到這了,更多相關(guān)PostgreSQL pg_trgm 模糊搜索內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
postgresql varchar字段regexp_replace正則替換操作
這篇文章主要介紹了postgresql varchar字段regexp_replace正則替換操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL教程(二)：模式Schema詳解,本文講解了創(chuàng)建模式、public模式、權(quán)限、刪除模式、模式搜索路徑等內(nèi)容,需要的朋友可以參考下2015-05-05
postgresql 實(shí)現(xiàn)查詢出的數(shù)據(jù)為空,則設(shè)為0的操作
這篇文章主要介紹了postgresql 實(shí)現(xiàn)查詢出的數(shù)據(jù)為空,則設(shè)為0的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql查詢鎖表以及解除鎖表操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了基于pgrouting的路徑規(guī)劃處理,根據(jù)pgrouting已經(jīng)集成的Dijkstra算法來，結(jié)合postgresql數(shù)據(jù)庫來處理最短路徑，需要的朋友可以參考下2022-04-04
這篇文章主要介紹了Postgresql 實(shí)現(xiàn)清理WAL日志的方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL支持大部分的SQL標(biāo)準(zhǔn)并且提供了很多其他現(xiàn)代特性，如復(fù)雜查詢、外鍵、觸發(fā)器、視圖、事務(wù)完整性、多版本并發(fā)控制等這篇文章主要介紹了shell腳本操作postgresql,需要的朋友可以參考下2022-12-12
Postgresql在mybatis中報錯:操作符不存在:character varying == unknown的問題
這篇文章主要介紹了Postgresql在mybatis中報錯： 操作符不存在 character varying == unknown的問題，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-01-01
這篇文章主要給大家介紹了關(guān)于postgresql中l(wèi)tree類型使用的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用postgresql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
這篇文章主要介紹了PgSQL條件語句與循環(huán)語句,pgSQL中有兩種條件語句分別為if與case語句，每種語句通過示例代碼給大家介紹的非常詳細(xì)，需要的朋友可以參考下2022-07-07