MySql JSON数据结构和搜索

背景

在mysql-v5.7.8中原生支持了json数据结构, 也提供了一系列json操作函数, 在mysql-v8.0中又新增了一系列的辅助函数, 尝试了一下以后发现已经达到了比较好用的程度, 可以开始在正式环境中使用了, 具体帮助可以参考官方文档

性能

• mysql提供基于json-key创建虚拟列索引的方式, 查询性能极高
• json大小是需要考虑的问题, 太大的字段不建议使用json存储
• json使用json-partial-updates特性来提高更新效率, 只更新指定字段, 但是对比普通字段来说更新性能仍然较低, 如果是经常更新的字段还是建议抽离出来单独作为一列

常用方法

数组中存在一项(in)

@json可以是表中的列, 也可以是传入的参数, @val同样如此

SET @json = '["a", "b", "c"]';
SET @val = 'a';
SELECT 1 AS result WHERE JSON_SEARCH(@json, 'one', @val) IS NOT NULL;

数组相等

SET @json = '["a", "b", "c"]';
SELECT 1 AS result WHERE @json = '["a", "b", "c"]';

从数组中提取值(map)

SELECT JSON_EXTRACT('[{"a":0},{"a":1},{"a":2}]', '$[*].a') AS r;
-- r
-- -----------
-- [0, 1, 2]

两个数组中有相同值(val in field_a && val in field_b)

-- 使用`JSON_SEARCH`
SELECT
  t.*
FROM
  (SELECT NOW() AS now_time, '["a", "b", "c"]' AS json_data ) AS t,
  JSON_TABLE (
    t.json_data,
    "$" COLUMNS (
      NESTED PATH '$[*]' COLUMNS (tag VARCHAR (50) PATH '$')
    )
  ) AS t_tag
WHERE JSON_SEARCH(
    '["a", "bb", "cc"]',
    'one',
    t_tag.tag
  ) IS NOT NULL;

-- 使用`in`
SELECT
  t.*
FROM
  (SELECT
    NOW() AS now_time,
    '["a", "b", "c"]' AS json_data) AS t,
  JSON_TABLE (
    t.json_data,
    "$" COLUMNS (
      NESTED PATH '$[*]' COLUMNS (tag VARCHAR (50) PATH '$')
    )
  ) AS t_tag
WHERE t_tag.tag IN ('a', 'bb', 'cc')

在mysql-8.0.17之后, 可以直接使用JSON_OVERLAPS来实现

SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
SELECT JSON_OVERLAPS('[4,5,6,7]', '6');

对某个字段创建索引

CREATE TABLE temp (
    c JSON,
    g INT GENERATED ALWAYS AS (c->"$.id"),
    INDEX i (g)
);

-- 查询
SELECT c->>"$.name" AS name
    FROM temp WHERE g > 2;