MySQL|MySQL 5.7 JSON特性支持 MySQL5.7JSON特性支持

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\\""')) | +------------------------------------------------------+ || +------------------------------------------------------+