MySQL使用json查询

考虑考虑
1,276 阅读3分钟

前言

在使用MySQL过程中，有时候使用json存储时，需要查询里面的字段，MySQL5.7版本以上提供了一种新的字段json格式存储，跟Mongodb类似，可以查询json字段里面的数据

MySQL操作json

创建json字段

在MySQL中创建json字段，执行以下SQL语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '名字',
  `phone_no` int NOT NULL DEFAULT 0 COMMENT '电话号码',
  `extra` json NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

或者工具类可以手动创建

image.png **备注:**本文使用的是MySQL9

json操作

先插入两条数据

INSERT INTO `test`.`t_user` (`id`, `name`, `phone_no`, `extra`) VALUES (1, 'aaa', 0, '{\"id\": 1, \"name\": \"aaa\"}');
INSERT INTO `test`.`t_user` (`id`, `name`, `phone_no`, `extra`) VALUES (2, 'bbb', 1, '{\"id\": 2, \"name\": \"aaa1\"}');
$操作

$可以指定json里面的字段，比如

select * from t_user t where t.extra -> '$.id' = 2;

代表跟节点

json_extract

使用json_extract()，可以从json中返回想要的字段

用法：json_extract(字段名,$.json字段名)

用法如下:

select id, JSON_EXTRACT(t.extra, '$.name') as jsonName from t_user t where t.extra -> '$.id' = 2 ;

返回如下

image.png

或者

select  * from t_user t where JSON_EXTRACT(t.extra, '$.name') = 'aaa1'

结果如下:

image.png

JSON_CONTAINS()

使用JSON_CONTAINS()，可以查询JSON格式数据是否在字段中包含特定对象，使用如下

SELECT * FROM t_user t WHERE JSON_CONTAINS(t.extra->'$.name', '"aaa1"');

备注: 这里中的name值，要加""，否则会报错

SELECT * FROM t_user t WHERE JSON_CONTAINS(t.extra->'$.id', '2');

还可以这么写

SELECT * FROM t_user t  WHERE JSON_CONTAINS(t.extra, '1', '$.id');
JSON_OBJECT()

JSON_OBJECT()将一个键值对列表转换成json对象

示例如下:

SELECT JSON_OBJECT("userName","hello1")

结果如下：

image.png 还可以这么写

SELECT * FROM t_user t WHERE JSON_CONTAINS(t.extra, JSON_OBJECT("name","aaa1"));

结果如下

image.png

JSON_ARRAY()

使用JSON_ARRAY()创建json数组

示例如下

SELECT JSON_ARRAY('aa', 'bb', 'cc') as name;

结果如下

image.png

执行inert操作，插入一条数据

INSERT INTO `test`.`t_user` (`id`, `name`, `phone_no`, `extra`) VALUES (3, 'ccc', 2, '{\"id\": 3, \"name\": \"aaa3\", \"roles\": [\"aa\", \"bb\", \"cc\"]}');

然后使用使用JSON_ARRAY查询

SELECT * from t_user t WHERE JSON_CONTAINS(t.extra->'$.roles',JSON_ARRAY("aa"))

结果为

image.png

JSON_TYPE

使用JSON_TYPE()，可以查询属性字段属性是什么类型

用法：JSON_TYPE(json_val)

使用如下

SELECT t.extra->'$.id' as jsonId ,JSON_TYPE(t.extra->'$.id') as jsonType FROM t_user t;

输出结果如下

image.png

JSON_EXTRACT

使用JSON_EXTRACT()函数来提取json中特定的值，示例如下

SELECT * FROM t_user t WHERE JSON_EXTRACT(t.extra,'$.name') like '%aa1%';

输出结果如下

image.png

JSON_KEYS()

JSON_KEYS()函数返回json中的key值

SELECT JSON_KEYS(t.extra) FROM t_user t

输出结果为:

image.png

JSON_INSERT()

向一个 JSON 文档中插入数据并返回新的 JSON 文档，使用如下

update t_user t set t.extra = json_insert(t.extra,"$.age","20") where id = 1;


select * from t_user t where t.id = 1;

输出结果为:

1732070679468.jpg 备注: 但是注意的是，已经存在的字段，不会更新

update t_user t set t.extra = json_insert(t.extra,"$.age","22") where id = 1;

执行以上这句，不会更新age字段

JSON_REPLACE

JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档

使用如下:

update t_user t set t.extra = JSON_REPLACE(t.extra,"$.age","22") where id = 1;

select * from t_user t where t.id = 1;

输出结果如下:

image.png 备注: 但是注意的是，如果json中不存在这字段，不会更新

update t_user t set t.extra = JSON_REPLACE(t.extra,"$.age","22") where id = 2;

select * from t_user t where t.id = 2;

输出结果为:

image.png

JSON_REMOVE()

JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档 使用如下

update t_user t set t.extra = JSON_REMOVE(t.extra,"$.age") where id = 1;

select * from t_user t where t.id = 1;

输出结果为

image.png

JSON_CONTAINS_PATH()

JSON_CONTAINS_PATH() 函数检查一个 JSON 文档中在指定的路径上是否有值存在

参数

  • json

    必需的。一个 JSON 文档。

  • one_or_all

    必需的。可用值：'one', 'all'。它指示是否检查所有的路径。

  • path

    必需的。您应该至少指定一个路径表达式

使用如下:

select * from t_user t where JSON_CONTAINS_PATH(t.extra, 'all', '$.name');

返回值如下:

image.png

详情可以参考以下文档

https://www.sjkjc.com/mysql-ref/json_contains_path/
JSON_SEARCH()

JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径

参数

  • json

    必需的。一个 JSON 文档。

  • one_or_all

    必需的。可用值：'one', 'all'。 JSON_SEARCH() 根据 one_or_all 参数决定是否返回所有匹配的路径：

    • 如果是 'one'，JSON_SEARCH() 函数将返回第一个匹配的路径。
    • 如果是 'all'，JSON_SEARCH() 函数将返回所有匹配的路径。所有的路径会包装在一个数组内返回。

  • search_str

    必需的。被搜索的字符串。 您可以在 search_str 参数中使用 % 和 _ 通配符，就像 LIKE 一样：

    • % 匹配任意数量的任意字符。
    • _ 匹配一个任意字符。

  • escape_char

    可选的。 如果 search_str 中包含 % 和 _，需要在他们之前添加转移字符。默认是 ``。

  • path

    可选的。只能在此路径下进行搜索。

使用如下

select * from t_user t where JSON_SEARCH(t.extra, 'all', 'aaa');

返回值为

image.png

总结

MySQL的json查询可以作为一个拓展查询用到项目，是一个非常强大的功能，可以根据具体项目决定采用不采用

avatar
文章
阅读
粉丝