MySQL数据类型之JSON

news/2025/3/16 5:06:54/

MySQL数据类型之JSON

SON类型是MySQL 5.7版本新增的数据类型，用好JSON数据类型可以有效解决很多业务中实际问题。

使用JSON数据类型，推荐用MySQL 8.0.17以上的版本，性能更好，同时也支持Multi-Valued Indexes；

JSON数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；

不要将有明显关系型的数据用JSON存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；

JSON数据类型推荐使用在不经常更新的静态数据存储。

JSON数据类型

mysql> create table json_user(id int primary key auto_increment, data json);
Query OK, 0 rows affected (0.09 sec)mysql> insert into json_user values(0, '{"name":"morris","age":18,"address":"china"}');
Query OK, 1 row affected (0.03 sec)mysql> insert into json_user values(0, '{"name":"tom","age":16,"mail":"tomcat@google.com"}');
Query OK, 1 row affected (0.01 sec)mysql> select * from json_user;
+----+---------------------------------------------------------+
| id | data                                                    |
+----+---------------------------------------------------------+
|  1 | {"age": 18, "name": "morris", "address": "china"}       |
|  2 | {"age": 16, "mail": "tomcat@google.com", "name": "tom"} |
+----+---------------------------------------------------------+
2 rows in set (0.01 sec)

常见JSON函数的使用
json_extract抽取字段

mysql> select json_extract('[1, 2, 3, [4, 5]]', '$[1]');
+-------------------------------------------+
| json_extract('[1, 2, 3, [4, 5]]', '$[1]') |
+-------------------------------------------+
| 2                                         |
+-------------------------------------------+
1 row in set (0.02 sec)mysql> select json_extract('{"age": 18, "name": "morris", "address": "china"}', '$.name');
+-----------------------------------------------------------------------------+
| json_extract('{"age": 18, "name": "morris", "address": "china"}', '$.name') |
+-----------------------------------------------------------------------------+
| "morris"                                                                    |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select data->"$.name" from json_user;
+----------------+
| data->"$.name" |
+----------------+
| "morris"       |
| "tom"          |
+----------------+
2 rows in set (0.00 sec)

json_object将对象转为json

mysql> select json_object('name','bob','age','22','email','bob@sina.com');
+-------------------------------------------------------------+
| json_object('name','bob','age','22','email','bob@sina.com') |
+-------------------------------------------------------------+
| {"age": "22", "name": "bob", "email": "bob@sina.com"}       |
+-------------------------------------------------------------+
1 row in set (0.01 sec)

json_insert插入数据
key存在则忽略，不存在则插入。

mysql> select json_insert('{"age": 18, "name": "morris", "address": "china"}', '$.male', 'male', '$.age', 22);
+-------------------------------------------------------------------------------------------------+
| json_insert('{"age": 18, "name": "morris", "address": "china"}', '$.male', 'male', '$.age', 22) |
+-------------------------------------------------------------------------------------------------+
| {"age": 18, "male": "male", "name": "morris", "address": "china"}                               |
+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

json_merge合并数据
json_merge（已不推荐使用）与json_merge_preserve类似，相同的key会合并为数组。

json_merge_patch中相同的key会使用后面的key替换前面的key。

mysql> select json_merge_patch('{"name": "enjoy"}', '{"id": 47, "name":"morris"}');
+----------------------------------------------------------------------+
| json_merge_patch('{"name": "enjoy"}', '{"id": 47, "name":"morris"}') |
+----------------------------------------------------------------------+
| {"id": 47, "name": "morris"}                                         |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select json_merge('{"name": "enjoy"}', '{"id": 47, "name":"morris"}');
+----------------------------------------------------------------+
| json_merge('{"name": "enjoy"}', '{"id": 47, "name":"morris"}') |
+----------------------------------------------------------------+
| {"id": 47, "name": ["enjoy", "morris"]}                        |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> select json_merge_preserve('{"name": "enjoy"}', '{"id": 47, "name":"morris"}');
+-------------------------------------------------------------------------+
| json_merge_preserve('{"name": "enjoy"}', '{"id": 47, "name":"morris"}') |
+-------------------------------------------------------------------------+
| {"id": 47, "name": ["enjoy", "morris"]}                                 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

json_array_append后面追加元素

mysql> select json_array_append('["a", "b", "c"]', "$", "x");
+------------------------------------------------+
| json_array_append('["a", "b", "c"]', "$", "x") |
+------------------------------------------------+
| ["a", "b", "c", "x"]                           |
+------------------------------------------------+
1 row in set (0.00 sec)

json_array_insert里面插入元素

mysql> select json_array_insert('["a", "b", "c"]', "$[1]", "x");
+---------------------------------------------------+
| json_array_insert('["a", "b", "c"]', "$[1]", "x") |
+---------------------------------------------------+
| ["a", "x", "b", "c"]                              |
+---------------------------------------------------+
1 row in set (0.00 sec)

json_contains包含某个json子串

mysql> select json_contains('{"a":1,"b":4}','{"a":1}');
+------------------------------------------+
| json_contains('{"a":1,"b":4}','{"a":1}') |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set (0.00 sec)mysql> select json_contains('{"age": 18, "name": "morris", "address": "china"}', '18', '$.name');
+------------------------------------------------------------------------------------+
| json_contains('{"age": 18, "name": "morris", "address": "china"}', '18', '$.name') |
+------------------------------------------------------------------------------------+
|                                                                                  0 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

json_array创建json数组

mysql> select json_array("a", "b", "c", "d");
+--------------------------------+
| json_array("a", "b", "c", "d") |
+--------------------------------+
| ["a", "b", "c", "d"]           |
+--------------------------------+
1 row in set (0.00 sec)

json_contains_path判断json是否包含某个key


mysql> select json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e');
+----------------------------------------------------------------------------+
| json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)mysql> select json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e');
+----------------------------------------------------------------------------+
| json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') |
+----------------------------------------------------------------------------+
|                                                                          0 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

json_keys查询json所有的key

mysql> select json_keys('{"a": 1, "b": 2, "c": {"d": 4}}');
+----------------------------------------------+
| json_keys('{"a": 1, "b": 2, "c": {"d": 4}}') |
+----------------------------------------------+
| ["a", "b", "c"]                              |
+----------------------------------------------+
1 row in set (0.00 sec)

json_pretty格式化json

mysql> select json_pretty('{"a": 1, "b": 2, "c": {"d": 4}}');
+-------------------------------------------------+
| json_pretty('{"a": 1, "b": 2, "c": {"d": 4}}')  |
+-------------------------------------------------+
| {"a": 1,"b": 2,"c": {"d": 4}
} |
+-------------------------------------------------+
1 row in set (0.00 sec)

json_depth查询json的深度

mysql> select json_depth('{"a": 1, "b": 2, "c": {"d": 4}}');
+-----------------------------------------------+
| json_depth('{"a": 1, "b": 2, "c": {"d": 4}}') |
+-----------------------------------------------+
|                                             3 |
+-----------------------------------------------+
1 row in set (0.00 sec)

json_length返回json元素的个数

mysql> select json_length('{"a": 1, "b": 2, "c": {"d": 4}}');
+------------------------------------------------+
| json_length('{"a": 1, "b": 2, "c": {"d": 4}}') |
+------------------------------------------------+
|                                              3 |
+------------------------------------------------+
1 row in set (0.00 sec)
————————————————

json_remove根据key删除元素

mysql> select json_remove('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c");
+-------------------------------------------------------+
| json_remove('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c") |
+-------------------------------------------------------+
| {"a": 1, "b": 2}                                      |
+-------------------------------------------------------+
1 row in set (0.00 sec)

json_replace替换元素

mysql> select json_replace('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c", "cc");
+--------------------------------------------------------------+
| json_replace('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c", "cc") |
+--------------------------------------------------------------+
| {"a": 1, "b": 2, "c": "cc"}                                  |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

json_search搜索元素

mysql> select json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc');
+--------------------------------------------------------------------------------------+
| json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc') |
+--------------------------------------------------------------------------------------+
| "$[0]"                                                                               |
+--------------------------------------------------------------------------------------+
1 row in set (0.01 sec)mysql> select json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc');
+--------------------------------------------------------------------------------------+
| json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc') |
+--------------------------------------------------------------------------------------+
| ["$[0]", "$[2].x"]                                                                   |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

json_set往json中插入元素
存在则覆盖，不存在则新增。

mysql> select json_set('{"a": 1, "b": 2, "c": {"d": 4}}', "$.a", "aaa", "$.e", "eee");
+-------------------------------------------------------------------------+
| json_set('{"a": 1, "b": 2, "c": {"d": 4}}', "$.a", "aaa", "$.e", "eee") |
+-------------------------------------------------------------------------+
| {"a": "aaa", "b": 2, "c": {"d": 4}, "e": "eee"}                         |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

json_storage_size查看json存储占用的空间大小

mysql> select json_storage_size('{"a": 1, "b": 2, "c": {"d": 4}}');
+------------------------------------------------------+
| json_storage_size('{"a": 1, "b": 2, "c": {"d": 4}}') |
+------------------------------------------------------+
|                                                   41 |
+------------------------------------------------------+
1 row in set (0.00 sec)

json_type查询json的类型

mysql> select json_type('{"a": 1, "b": 2, "c": {"d": 4}}');
+----------------------------------------------+
| json_type('{"a": 1, "b": 2, "c": {"d": 4}}') |
+----------------------------------------------+
| OBJECT                                       |
+----------------------------------------------+
1 row in set (0.01 sec)

json_valid判断json是否是有效的

mysql> select json_valid('{"a": 1, "b": 2, "c": {"d": 4}}');
+-----------------------------------------------+
| json_valid('{"a": 1, "b": 2, "c": {"d": 4}}') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

JSON索引
JSON类型数据本身无法直接创建索引，需要将需要索引的JSON数据重新生成虚拟列（Virtual Columns）之后，对该列进行索引。

mysql> create table test_json_index(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (json_extract(data, '$.name')));
mysql> insert into test_json_index(data) values('{"name":"morris","age":18,"address":"china"}');
mysql> insert into test_json_index(data) values('{"name":"tom","age":16,"mail":"tomcat@google.com"}');
mysql> select * from test_json_index;
+----+---------------------------------------------------------+----------+
| id | data                                                    | gen_col  |
+----+---------------------------------------------------------+----------+
|  1 | {"age": 18, "name": "morris", "address": "china"}       | "morris" |
|  2 | {"age": 16, "mail": "tomcat@google.com", "name": "tom"} | "tom"    |
+----+---------------------------------------------------------+----------+
mysql> select * from test_json_index where gen_col='morris'; -- 查不到数据
mysql> select * from test_json_index where gen_col='"morris"';
+----+---------------------------------------------------+----------+
| id | data                                              | gen_col  |
+----+---------------------------------------------------+----------+
|  1 | {"age": 18, "name": "morris", "address": "china"} | "morris" |
+----+---------------------------------------------------+----------+

要想在查询时不加引号，可以在加索引时使用json_unquote去除引号。

mysql> create table test_json_index2(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (json_unquote(json_extract(data, '$.name'))));
Query OK, 0 rows affected (0.08 sec)mysql> create table test_json_index3(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (JSON_UNQUOTE(data->'$.name')));
Query OK, 0 rows affected (0.08 sec)mysql> create table test_json_index4(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (data->>'$.name'));
Query OK, 0 rows affected (0.05 sec)

http://www.ppmy.cn/news/1124882.html

相关文章

Python3 如何实现 websocket 服务？

Python3 如何实现 websocket 服务？

Python 实现 websocket 服务很简单，有很多的三方包可以用，我从网上大概找到三种常用的包：websocket、websockets、Flask-Sockets。 但这些包很多都“年久失修”， 比如 websocket 在 2010 年就不维护了。 而 Flask-Sockets 也在 2…
阅读更多...
axios添加缓存请求，防止多次请求，单页面多个同一组件造成多次请求解决方案

axios添加缓存请求，防止多次请求，单页面多个同一组件造成多次请求解决方案

在axios中，添加 const cacheMap {};响应拦截添加 try {// 删除缓存const api res.config.url.replace(process.env.VUE_APP_BASE_API, "");if (cacheMap.hasOwnProperty(api)) {delete cacheMap[api];}} catch (err) {}创建两个请求方法 /*** Get缓存…
阅读更多...
qt6 qml cmakelists.txt如何添加qrc文件

qt6 qml cmakelists.txt如何添加qrc文件

在升级qt6之后，qt推荐使用CMakeLists.txt来管理Qt项目，这对于不熟悉CMake的开发者来说，很麻烦，例如在qml项目中，图片资源一般放到qrc文件中，但是CMakeLists.txt和qmake差别很大。例如下面qml的代码: import QtQuick import QtQuick.Controls import QtQuick.LayoutsAppl…
阅读更多...
SpringBoot整合QQ邮箱发送验证码

SpringBoot整合QQ邮箱发送验证码

1.项目介绍 基于SpringBoot QQ邮箱服务 Hutools实现的获取验证码功能，接下来从如何申请授权码，如何配置项目，如何启动项目，如何测试项目进行讲解，下面的图片是一个测试案例，使用postman进行测试&#xf…
阅读更多...
OpenCV实现模板匹配和霍夫线检测，霍夫圆检测

OpenCV实现模板匹配和霍夫线检测，霍夫圆检测

一，模板匹配 1.1代码实现 import cv2 as cv import numpy as np import matplotlib.pyplot as plt from pylab import mplmpl.rcParams[font.sans-serif] [SimHei]#图像和模板的读取 img cv.imread("cat.png") template cv.imread(r"E:\All_in\o…
阅读更多...
rabbimq之java.net.SocketException: Connection reset与MissedHeartbeatException分析

rabbimq之java.net.SocketException: Connection reset与MissedHeartbeatException分析

一、前言 在android前端中接入了rabbitmq消息队列来处理业务，在手机网络环境错综复杂，网络信号不稳定，可能导致mq的频繁断开与连接，在日志中，发现有很多这样的日志，java.net.SocketException: Connection …
阅读更多...
uniapp：如何修改组件默认样式

uniapp：如何修改组件默认样式

日历组件默认样式如下图，但是我不想要右上角的红点，并且日期下面的数字要加红色背景，变成圆形，还是先用元素检查找到元素的类名，然后通过/deep/来覆盖样式，需要注意的是，lang要scss或者less&…
阅读更多...
Linux学习第19天：Linux并发与竞争实例： 没有规矩不成方圆

Linux学习第19天：Linux并发与竞争实例： 没有规矩不成方圆

Linux版本号4.1.15 芯片I.MX6ULL 大叔学Linux 品人间百味 思文短情长 先说点题外话，上周参加行业年会，停更了一周。接下来的周五就要开启国庆中秋双节模式，所以有的时候，尤其是工作以后…
阅读更多...
最新文章

Copyright @ 2022~2023