mysql对JSON数据操作

总所周知，mysql5.7以上提供了一种新的字段格式-json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍。

准备数据

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `user` ( `name`, `json_value`) VALUES ( '张三', '{"age": 26, "sex": "男", "report": {"maths": 100, "chinese": 100}}');
INSERT INTO `user` ( `name`, `json_value`) VALUES ( '李四', '{"age": 18, "sex": "男", "report": {"maths": 89, "chinese": 60}}');
INSERT INTO `user` ( `name`, `json_value`) VALUES ( '王五', '{"age": 30, "sex": "女", "report": {"maths": 94, "chinese": 73}}');

一般基础查询操作

1、使用 json字段名->’$.json属性’ 进行查询条件

查询年龄是26岁的人

SELECT * FROM USER where json_value -> '$.age' = 26;

image.png

2、使用 json字段名进行多条件查询

SELECT * FROM USER where json_value -> '$.age' = 26 and  json_value -> '$.sex' = '男';

image.png

如果涉及多层使用'.'进行拼接

SELECT * FROM USER where json_value -> '$.report.maths' = 89;

image.png

mysql 提供的函数

官网地址：https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

INSERT INTO `user` ( `name`, `json_value`) VALUES ( '牛', '{"age": 30, "sex": "女", "report": {"maths": 94, "chinese": 73}, "message": [{"area_code": "11111", "area_name": "test1"}, {"area_code": "22222", "area_name": "test2"}, {"area_code": "33333", "area_name": "test3"}]}}');

查询area_code有11111和44444

SELECT
    * 
FROM
    user q 
WHERE
    JSON_CONTAINS(
        q.json_value -> '$.message',
        JSON_ARRAY(
        JSON_OBJECT( 'area_code', '11111' ))) 
    OR JSON_CONTAINS(
        q.json_value -> '$.message',
        JSON_ARRAY(
        JSON_OBJECT( 'area_code', '44444' )))