MySQL支持JSON类型
2019-10-29 本文已影响0人
keaiduoduo
由于最近实验室接手了一个新的项目，涉及了大量的数据变量作存储，想到如果一个数据变量作为一个字段来存储的话效率太低，当某些数据为空时会造成大量的资源浪费，然后就发现了MySQL的JSON支持，先点个赞！
MySQL从5.7版本之后开始支持JSON数据类型，相比于JSON格式的字符串类型有如下优势：
- 存储在JSON列中的JSON文档的会被自动验证。无效的文档会产生错误；
- 最佳存储格式。存储在JSON列中的JSON文档会被转换为允许快速读取文档元素的内部格式。
存储在JSON列中的任何JSON文档的大小都受系统变量max_allowed_packet的值的限制，可以使用JSON_STORAGE_SIZE()函数获得存储JSON文档所需的空间。
下面直接上代码
1、创建JSON值
mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array'));
Query OK, 1 row affected (0.19 sec)
mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello'));
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2)));
Query OK, 1 row affected (0.14 sec)
mysql> SELECT * FROM t_json;
+--------------------------------------+
| jdoc |
+--------------------------------------+
| [1, 2] |
| {"key1": "value1", "key2": "value2"} |
| "HELLO" |
| ["json_array"] |
| {"key": "hello"} |
| [{"key": "hello"}, 1, 2] |
+--------------------------------------+
6 rows in set (0.00 sec)
2、搜索和修改JSON值
路径语法
-
.keyName：JSON对象中键名为keyName的值； - 对于不合法的键名（如有空格），在路径引用中必须用双引号
"将键名括起来，例."key name"； -
[index]：JSON数组中索引为index的值，JSON数组的索引同样从0开始； -
[index1 to index2]：JSON数组中从index1到index2的值的集合； -
.*: JSON对象中的所有value； -
[*]: JSON数组中的所有值； -
prefix**suffix: 以prefix开头并以suffix结尾的路径； -
**.keyName为多个路径，如对于JSON对象'{"a": {"b": 1}, "c": {"b": 2}}','$**.b'指路径$.a.b和$.c.b； - 不存在的路径返回结果为NULL；
- 前导$字符表示当前正在使用的JSON文档；
- 例子：对于数组
[3, {"a": [5, 6], "b": 10}, [99, 100]]-
$[1]为{"a": [5, 6], "b": 10}； -
[1].a为[5, 6]； -
$[1].a[1]为6； -
$[1].b为10； -
$[2][0]为99。
-
2.1 搜索
- JSON对象
mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name');
+--------------------------------------------------------+
| JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name') |
+--------------------------------------------------------+
| "Taylor" |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*');
+-----------------------------------------------------+
| JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*') |
+-----------------------------------------------------+
| [29, "Taylor"] |
+-----------------------------------------------------+
1 row in set (0.00 sec)
- JSON数组
mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');
+-----------------------------------------+
| JSON_EXTRACT('["a", "b", "c"]', '$[1]') |
+-----------------------------------------+
| "b" |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]');
+----------------------------------------------+
| JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]') |
+----------------------------------------------+
| ["b", "c"] |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]');
+-----------------------------------------+
| JSON_EXTRACT('["a", "b", "c"]', '$[*]') |
+-----------------------------------------+
| ["a", "b", "c"] |
+-----------------------------------------+
1 row in set (0.00 sec)
2.2 修改
-
JSON_REPLACE和JSON_SET的区别
// 旧值存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere');
+----------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+----------------------------------------------------------------+
| {"id": 29, "name": "Mere"} |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere");
+------------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+------------------------------------------------------------+
| {"id": 29, "name": "Mere"} |
+------------------------------------------------------------+
1 row in set (0.00 sec)
// 旧值不存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+---------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+---------------------------------------------------------------+
| {"id": 29, "name": "Taylor"} |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+-----------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+-----------------------------------------------------------+
| {"id": 29, "cat": "Mere", "name": "Taylor"} |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
-
JSON_INSERT与JSON_SET的区别
// 旧值存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[1]', 4) |
+-------------------------------------+
| [1, 2, 3] |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[1]', 4) |
+----------------------------------+
| [1, 4, 3] |
+----------------------------------+
1 row in set (0.00 sec)
//旧值不存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[4]', 4) |
+-------------------------------------+
| [1, 2, 3, 4] |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[4]', 4) |
+----------------------------------+
| [1, 2, 3, 4] |
+----------------------------------+
1 row in set (0.00 sec)
