MySQL|MySQL 5.7 JSON特性支持

Reference MySQL 5.7 The JSON Data Type
Practice

mysql> load data infile '//bigJson.json' into table `json_test`; ERROR 1301 (HY000): Result of json_binary::serialize() was larger than max_allowed_packet (4194304) - truncated

Json 类型:
  • 不能设定默认值
  • 无法直接建立索引(类似blob,text类型), 通过Generated Column 实现间接对document的指定路径过滤器做索引
  • 本身长度限制和longtext、longblob相当
对使用方来说,使用JSON类型同使用TEXT character set utf8mb4 可以认为是等同的,只是多了一层插入更新的有效性检查。
常用过滤、搜索函数 JSON_EXTRACT(json_doc, path[, path] ...)
json_doc中取出对应路径path路径下的值
同时支持该函数的语法糖写法 ->, 如:select JSON_EXTRACT(c, "$.id") from json_test; 等价于 select c->"$.id" from json_test;
mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name"); +-------------------------------+-----------+------+ | c| c->"$.id" | g| +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3"|3 | | {"id": "4", "name": "Betty"}| "4"|4 | | {"id": "2", "name": "Wilma"}| "2"|2 | +-------------------------------+-----------+------+3 rows in set (0.00 sec)mysql> SELECT c, c->"$.id", g > FROM jemp > WHERE c->"$.id" > 1 > ORDER BY c->"$.name"; +-------------------------------+-----------+------+ | c| c->"$.id" | g| +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3"|3 | | {"id": "4", "name": "Betty"}| "4"|4 | | {"id": "2", "name": "Wilma"}| "2"|2 | +-------------------------------+-----------+------+3 rows in set (0.00 sec)

关于 path (过滤路径) 和命令行jq的filter语法类似,但是没有提供完全一样的功能,如: [1:2] 数组区间过滤是不支持的。
PATH(过滤路径)简单语法介绍
  • $符号起始,如:$.a$[1]
  • 对象使用.来选择属性,如: $.a.b {"a":{"b":{"id":1}}}下的a对象下的b对象 {"id":1};
  • 数组使用[]来选择元素
  • 通配符 * 可以用于对象、数组
JSON JSON -> PATH RESULT
{"id": "4", "name": "Betty"} $."name" "Betty"
{"id": "5", "name": "Jo"} $.* ["5", "Jo"]
{"id": "2", "name": "Wilma","house": {"address": {"id": 1}}} $."house"."address" {"id":1}
[{"id":"2", "name": "Bob"},{"id": "2", "name": "John"}] $[1]."name" "John"
[{"id":"2", "name": "Bob"},{"id": "2", "name": "John"}] $[*]."name" ["Bob","John"]
JSON_CONTAINS(target, candidate[, path])
target JSON串 [指定的路径path下],是否含有 candidate json串
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; mysql> SET @j2 = '1'; mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.a') | +-------------------------------+ |1 | +-------------------------------+mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.b') | +-------------------------------+ |0 | +-------------------------------+mysql> SET @j2 = '{"d": 4}'; mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.a') | +-------------------------------+ |0 | +-------------------------------+mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.c') | +-------------------------------+ |1 | +-------------------------------+

额外
【MySQL|MySQL 5.7 JSON特性支持】5.6 版本之前碰到过使用方存表情符,但是字段字符集就是utf8,团队里面的人表示很惊讶 本质上还是编码、解码的问题
mysql> select json_unquote(json_unquote('"\\"\\uD83C\\uDF39\\""')); +------------------------------------------------------+ | json_unquote(json_unquote('"\\"\\uD83C\\uDF39\\""')) | +------------------------------------------------------+ || +------------------------------------------------------+

    推荐阅读