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 node
WHERE '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