mysql json基本使用

摘要

mysql5.7过后就支持了json格式的数据存储，可以方便我们一些数据不需要额外增加子表的情况，下面将介绍常用的用法

方法表

Name	Description
JSON_APPEND()	Append data to JSON document
JSON_ARRAY()	Create JSON array
JSON_ARRAY_APPEND()	Append data to JSON document
JSON_ARRAY_INSERT()	Insert into JSON array
->	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()	Whether JSON document contains any data at path
JSON_DEPTH()	Maximum depth of JSON document
JSON_EXTRACT()	Return data from JSON document
->>	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT()	Insert data into JSON document
JSON_KEYS()	Array of keys from JSON document
JSON_LENGTH()	Number of elements in JSON document
JSON_MERGE()	Merge JSON documents
JSON_OBJECT()	Create JSON object
JSON_QUOTE()	Quote JSON document
JSON_REMOVE()	Remove data from JSON document
JSON_REPLACE()	Replace values in JSON document
JSON_SEARCH()	Path to value within JSON document
JSON_SET()	Insert data into JSON document
JSON_TYPE()	Type of JSON value
JSON_UNQUOTE()	Unquote JSON value
JSON_VALID()	Whether JSON value is valid

存储JSON对象的基本使用

这里的info_json存储的对象属性有province,city,district

• 创建表和基础数据

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
  `info_json` json DEFAULT NULL COMMENT '其他信息',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user` VALUES (1478605726684860417, 'name1478605726684860417', '{\"city\": \"市0\", \"district\": \"区0\", \"province\": \"省0\"}', NULL);
INSERT INTO `user` VALUES (1478605726991044610, 'name1478605726991044610', '{\"city\": \"市1\", \"district\": \"区1\", \"province\": \"省1\"}', NULL);
INSERT INTO `user` VALUES (1478605727007821826, 'name1478605727007821826', '{\"city\": \"市2\", \"district\": \"区2\", \"province\": \"省2\"}', NULL);
INSERT INTO `user` VALUES (1478605727527915521, 'name1478605727527915521', '{\"city\": \"市3\", \"district\": \"区3\", \"province\": \"省3\"}', NULL);
INSERT INTO `user` VALUES (1478605727691493378, 'name1478605727691493378', '{\"city\": \"市4\", \"district\": \"区4\", \"province\": \"省4\"}', NULL);
INSERT INTO `user` VALUES (1478605727880237057, 'name1478605727880237057', '{\"city\": \"市5\", \"district\": \"区5\", \"province\": \"省5\"}', NULL);
INSERT INTO `user` VALUES (1478605727901208577, 'name1478605727901208577', '{\"city\": \"市6\", \"district\": \"区6\", \"province\": \"省6\"}', NULL);
INSERT INTO `user` VALUES (1478605727951540226, 'name1478605727951540226', '{\"city\": \"市7\", \"district\": \"区7\", \"province\": \"省7\"}', NULL);
INSERT INTO `user` VALUES (1478605727968317442, 'name1478605727968317442', '{\"city\": \"市8\", \"district\": \"区8\", \"province\": \"省8\"}', NULL);
INSERT INTO `user` VALUES (1478605728199004161, 'name1478605728199004161', '{\"city\": \"市9\", \"district\": \"区9\", \"province\": \"省9\"}', NULL);

• 基础查询

# 查询json中的city='市0'  的值
SELECT
	`user`.id,
	`user`.`name`,
	`user`.`info_json` -> '$.city' as city,
	`user`.`info_json` ->> '$.city' as city1
FROM
	`user` 
WHERE
	`user`.`info_json` ->> '$.city' = '市0'
	
# 这里的 -> 指的是从该对象中取字段；
# ->> 多一个箭头的是取出来的值不包含双引号
1478605726684860417	name1478605726684860417	"市0"	市0
1478605826278608898	name1478605826278608898	"市0"	市0

# 下面的写法和上面结果一样，只不过用的函数，上面用了新特性的简写，我个人推荐简写
SELECT
	`user`.id,
	`user`.`name`,
	JSON_EXTRACT( info_json, '$.city' ) AS city,
	JSON_UNQUOTE(JSON_EXTRACT( info_json, '$.city' )) AS city1
FROM
	`user` 
WHERE
	JSON_CONTAINS(
		info_json,
	JSON_OBJECT( "city", "市0" ))
	
# 其他的用法匹配如 > < like等等的聚合函数都一样的用法

• 提升json的效率

创建虚拟的列，该列的值是从json中读取出来的；并创建索引，表结构如下

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
  `city` varchar(20) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`info_json`,'$.city'))) VIRTUAL,
  `info_json` json DEFAULT NULL COMMENT '其他信息',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# 增加的city字段用法和和其他字段一致，只是不能插入数据
select * from `user` where `user`.city = '市0'
#如下数据
1478605726684860417	name1478605726684860417	市0	{"city": "市0", "district": "区0", "province": "省0"}
1478605826278608898	name1478605826278608898	市0	{"city": "市0", "district": "区0", "province": "省0"}	

json数组对象的处理

数组对象唯一不同的就是存储在该字段中是以数组的形式，查询的时候就用JSON_CONTAINS(info_json,JSON_OBJECT( "city", "市0" ))，这种方式才能更好匹配出来。数组字段的使用，用下表和属性名称就可以取出来，如下

• sql如下

SELECT
	`user`.id,
	`user`.`name`,
	`user`.`info_json` ->> '$[0].city' AS city,
	JSON_EXTRACT( info_json, '$[*].city' ) AS city 
FROM
	`user` 
WHERE
	JSON_CONTAINS(
		info_json,
	JSON_OBJECT( "city", "市0" ))