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(过滤路径)简单语法介绍path
(过滤路径) 和命令行jq的filter语法类似,但是没有提供完全一样的功能,如: [1:2] 数组区间过滤是不支持的。
$
符号起始,如:$.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"] |
在
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\\""')) |
+------------------------------------------------------+
||
+------------------------------------------------------+
推荐阅读
- py连接mysql
- 2019-01-18Mysql中主机名的问题
- MySql数据库备份与恢复
- mysql|InnoDB数据页结构
- mysql中视图事务索引与权限管理
- MYSQL主从同步的实现
- MySQL数据库的基本操作
- javaweb|基于Servlet+jsp+mysql开发javaWeb学生成绩管理系统
- Python3|Python3 MySQL 数据库连接
- MySQL|MySQL 存储过程语法及实例