需求场景:
目录以 jsonb 格式存储在数据库表 t 的 chapter 字段中, 需要菜单路径中包含指定字符串 (比如 "语文" 或者 "上学期") 的 menu
以下为 chapter 字段存储 JSON 示例:
- {
- "menu": {
- "text": "第一级菜单 (语文)> 第二级菜单 (上学期)> 第三级菜单(第一章节)",
- "menuItem": [
- {
- "root": true,
- "id": "1",
- "pId": "",
- "text": "第一级菜单(语文)"
- },
- {
- "root": false,
- "id": "2",
- "pId": "1",
- "text": "第二级菜单(上学期)"
- },
- {
- "root": false,
- "id": "3",
- "pId": "2",
- "text": "第三级菜单(第一章节)"
- }
- ]
- }
- }
实现(有关 PostgreSQL JSON 类型支持的操作符可以参考: 官方文档,):
SELECT chapter FROM t WHERE chapter #>>'{menu,text}'like '% 语文 %'
对应 mybatis mapper 配置文件:
- <if test="chapter != null and chapter!=''">
- chapter #>>'{menu,text}' LIKE concat('%',#{chapter},'%')
- </if>
优化(创建全文索引):
CREATE INDEX i_chapter_text_jsonb_gin ON resource USING gin((chapter #>>'{menu,text}') gin_trgm_ops);
创建索引可能会遇到的问题:
1.ERROR: operator class "gin_trgm_ops" does not exist for access method "gin"
解决方案:
先执行 CREATE EXTENSION pg_trgm;
2.ERROR: could not open extension control file "/usr/pgsql-9.6/share/extension/pg_trgm.control": No such file or directory
解决方案:
- Ubuntu/Debian:
- sudo apt install PostgreSQL-contrib
- RedHat/CentOS
- sudo dnf install postgresql10-contrib
另外关于索引可以参考(一篇大杂烩):
https://juejin.im/entry/586b448761ff4b00578c1b7a
来源: http://www.bubuko.com/infodetail-2956173.html