MySQL 8 JSON多值索引

背景介绍

咱们有个 node model，对应的存储表名也叫 node。这个model外面，有一些简单配置，是用 json 的格局存储的。这个json配置外面，有个字段会指向另一个node，所以在删除某个node的时候须要查看这个node是否被援用了，如果被援用则不容许删除。这个查看操作，不可能是全表扫描，因而须要创立索引。于是就有了要给 JSON 字段创立多值索引的需要。

建表 & 索引

表的schema就不放这里了，次要是有个叫 units_json 字段，其它不重要。

而后创立索引，留神这里是多值，所以用了ARRAY

ALTER TABLE `node`  ADD INDEX `jump_node_code_index`(     (CAST(units_json -> '$.units[*].decisionEntries[*].capsules[*].jumpNodeCode'           AS CHAR(50) ARRAY)) );

下面那个 $.units[*]... 是 jsonPath 的定义语法，不理解的同学能够自行谷歌一下。

查问

MySQL 8 中，JSON_EXTRACT 函数能够用 -> 操作符示意。上面这个查问语句，能够将所有 jumpNodeCode 查出来，也就是所有的跳转节点。

SELECT units_json -> '$.units[*].decisionEntries[*].capsules[*].jumpNodeCode' FROM   node;

条件查问

上面这个查问语句应用了 MEMBER OF 作为条件。意思是将所有援用了 xxxNode 的节点全副查出来。这个查问语句将用于删除查看。

SELECT node_id, `code`, `name` FROM nodeWHERE 'xxxNode' MEMBER OF (units_json -> '$.units[*].decisionEntries[*].capsules[*].jumpNodeCode');

编写jOOQ 工具类

咱们我的项目的ORM框架应用jOOQ，国内绝对MyBatis而言小众很多。但我比拟喜爱用jOOQ，感兴趣的同学可自行谷歌。
jOOQ 没有提供MySQL这种比较复杂语法，于是自行封装了个工具类

public class JooqSyntax {      /**   * * @param value 查看值   * @param field 列   * @param jsonPath json文档语法   * @return jooq 条件   */   public static Condition memberOfCondition(Object value, TableField<?, ?> field, String jsonPath) {          return DSL.condition("{0} MEMBER OF ({1})", value, jsonField(field, jsonPath)          );   }        public static Field<Object> jsonField(TableField<? extends Record, ?> field, String jsonPath) {          return DSL.field("{0} -> {1}", field, jsonPath);   }  }

单元测试，次要是用于阐明如何应用。

public class JooqSyntaxTest {        @Test       void test() {          DSLContext ctx = DSL.using(SQLDialect.MYSQL);             Condition condition = JooqSyntax.memberOfCondition(1, TOPIC.PERMISSION_JSON, "$.xxx");       System.out.println(                  ctx.selectFrom(TOPIC)                          .where(condition)          );         System.out.println(ctx                  .select(JooqSyntax.jsonField(TOPIC.PERMISSION_JSON, "$.xxx"))                  .from(TOPIC)          );   }  }

相干参考：
MySQL 8.0 新个性:多值索引 --如何给JSON数组增加索引（三）
MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type