MySQL与JSON-优快云博客

本文链接：https://blog.youkuaiyun.com/zhchs2012/article/details/80424700

JSON简介

百度百科走一发：JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。类似XML，但比XML更小、更快，更易解析。

JSON语法规则

数据表示为键值对
数据由逗号分隔
花括号保存对象
方括号保存数组

一个典型的JSON长这样：

{
"sites": [
{ "name":"我的博客" , "url":"https://blog.youkuaiyun.com/zhchs2012" }, 
{ "name":"百度" , "url":"www.baidu.com" }, 
{ "name":"微博" , "url":"www.weibo.com" }
]
}

MySQL对JSON的支持

MySQL5.7.8开始，MySQL提供了一个原生的Json类型，在此之前JSON是以字符串的形式存储的。
新类型的优势：

JSON数据类型，会自动校验数据是否为JSON格式，如果不是JSON格式数据，则会报错；
MySQL提供了一组操作JSON数据的内置函数（重点！！！）；
优化的存储格式，存储在JSON列中的JSON数据被转换成内部的存储格式。允许快速读取；
可以修改特定的键值，而不需要更新整个JSON内容。

MySQL对JSON的操作

作为一个数据分析人员，对JSON的查询操作是最为关心的，其他诸如创建相关的了解一下就好，大不了用时再查嘛。据我所知实际工作中很多程序员也是这样工作的，斜眼笑-。-

1、JSON_TYPE()

用于得知JSON字符串的类型。

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

2、JSON_OBJECT()

用于构造一个JSON类型。

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

3、JSON_VALID()

检查是否是正确的JSON类。

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

4、JSON_MERGE()

用于合并两个JSON。

mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3}                      |
+----------------------------------------------------+

5、JSON_EXTRACT()

JSON提取函数，我们最关心最常使用的函数。

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

5.1、->操作符

用处跟JSON_EXTRACT()是一样的，另一种写法。

SELECT req_info -> "$.bankcard_no" FROM t_trade_record;

等同于

SELECT JSON_EXTRACT(req_info, "$.bankcard_no") FROM t_trade_record;

6、JSON_KEYS()

JSON_KEYS(json_doc, [path]) 以列表返回顶层所有key，或者path路径下的所有顶层key

7、JSON_SEARCH()

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])查找函数，返回search_str所在的位置，支持通配符%，类似于like语句。第二个参数one是查找一个，all是查找所有，如果有多个匹配结果则以列表返回，否则与one的结果一致。