MySQL查询JSON字符串

MySQL版本：8.0.27

表结构及数据

/*
 Navicat Premium Data Transfer

 Source Server         : Local
 Source Server Type    : MySQL
 Source Server Version : 80027 (8.0.27)
 Source Host           : 127.0.0.1:3306
 Source Schema         : mumangguo

 Target Server Type    : MySQL
 Target Server Version : 80027 (8.0.27)
 File Encoding         : 65001

 Date: 26/04/2024 22:31:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for json
-- ----------------------------
DROP TABLE IF EXISTS `json`;
CREATE TABLE `json`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `ip_info` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ip地址信息',
  `user_info` json NULL COMMENT '用户信息',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of json
-- ----------------------------
INSERT INTO `json` VALUES (1, '{\"ipConfig\":20,\"ip\":\"10.1.40.0\",\"exchangePort\":\"11\",\"pmPort\":\"12\"}', '{\"id\": 1, \"age\": \"20\", \"name\": \"木芒果\", \"birthday\": \"2003-12-05\"}');
INSERT INTO `json` VALUES (2, '{\"ipConfig\":20,\"ip\":\"10.1.40.1\",\"exchangePort\":\"11\",\"pmPort\":\"12\"}', '{\"id\": 2, \"age\": \"21\", \"name\": \"张三\", \"birthday\": \"2004-11-01\"}');
INSERT INTO `json` VALUES (3, '{\"ipConfig\":20,\"ip\":\"10.1.40.3\",\"exchangePort\":\"11\",\"pmPort\":\"12\"}', '{\"id\": 3, \"age\": \"22\", \"name\": \"李四\", \"birthday\": \"2006-9-06\"}');
INSERT INTO `json` VALUES (4, '[{\"ipConfig\":20,\"ip\":\"10.1.40.4\",\"exchangePort\":\"111\",\"pmPort\":\"112\"},{\"ipConfig\":20,\"ip\":\"10.1.40.5\",\"exchangePort\":\"111\",\"pmPort\":\"112\"}]', '{\"id\": 4, \"age\": \"24\", \"name\": \"王五\", \"birthday\": \"2023-3-03\"}');
INSERT INTO `json` VALUES (5, '[{\"exchangePort\":\"[T1-01][10.20.8.204][GE1/0/06]\",\"ip\":\"10.10.20.41\",\"ipConfig\":28,\"pmPort\":\"1\"}]', '{\"id\": 5, \"age\": \"22\", \"name\": \"赵六\", \"birthday\": \"2003-5-03\"}');
INSERT INTO `json` VALUES (6, '{\"exchangePort\":\"[T1-02][10.20.8.208][GE1/0/02]\",\"pmPort\":\"HDM\"}', '[{\"id\": 6, \"age\": \"44\", \"name\": \"陈启\", \"birthday\": \"1998-2-01\"}, {\"id\": 7, \"age\": \"34\", \"name\": \"周八\", \"birthday\": \"1988-1-21\"}]');
INSERT INTO `json` VALUES (7, NULL, NULL);
INSERT INTO `json` VALUES (8, '{}', '{}');
INSERT INTO `json` VALUES (9, '[]', '[]');
INSERT INTO `json` VALUES (10, '\"\"', '\"\"');

SET FOREIGN_KEY_CHECKS = 1;

一般基础查询操作

#一般基础查询操作，只针对存储单个json的情况
SELECT * from `json` WHERE `ip_info`->'$.ip'='10.1.40.0';
#{"ipConfig":20,"ip":"10.1.40.0","exchangePort":"11","pmPort":"12"}

JSON_EXTRACT函数

#JSON_EXTRACT函数：从json中返回想要的字段
#查询数据类型为varchar的单个json数组
SELECT *   
FROM `json`   
WHERE JSON_EXTRACT(`ip_info`, '$.ip') = "10.1.40.1";
#{"ipConfig":20,"ip":"10.1.40.0","exchangePort":"11","pmPort":"12"}

#查询数据类型为json的单个json数组
SELECT *
FROM `json`
WHERE JSON_EXTRACT(`user_info`, '$.name') LIKE '%木%';
#{"id": 1, "age": "20", "name": "木芒果", "birthday": "2003-12-05"}

JSON_CONTAINS函数

#JSON_CONTAINS函数：JSON格式数据是否在字段中包含特定对象
#查询数据类型为varchar的多个json数组和json
SELECT *   
FROM `json`   
WHERE JSON_CONTAINS(`ip_info`, '{"ip":"10.1.40.1"}', '$');
#[{"ipConfig":20,"ip":"10.1.40.4","exchangePort":"111","pmPort":"112"},{"ipConfig":20,"ip":"10.1.40.5","exchangePort":"111","pmPort":"112"}]

#查询数据类型为json的多个json数组和json
SELECT *
FROM `json`
WHERE JSON_CONTAINS(`user_info`, '{"name":"陈启"}', '$');
#[{"id": 6, "age": "44", "name": "陈启", "birthday": "1998-2-01"}, {"id": 7, "age": "34", "name": "周八", "birthday": "1988-1-21"}]

REGEXP表达式

#使用正则表达式查询json
SELECT *   
FROM `json`   
WHERE `ip_info` REGEXP '"ip":"10.1.40.1"';
--
SELECT *   
FROM `json`   
WHERE `user_info` REGEXP '"name": "陈启"';