MySQL插入json问题

MySQL插入JSON问题可以分为以下几个步骤：

1. 创建表

创建表时需要用到json类型列，例如：

CREATE TABLE `example` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `json_data` JSON NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 准备JSON数据

例1.

{
  "name": "张三",
  "age": 28,
  "address": [
    {"city": "上海", "street": "南京路"},
    {"city": "北京", "street": "五道口"}
  ]
}

例2.

[
  {"name": "张三", "age": 28},
  {"name": "李四", "age": 30}
]

3. 插入JSON数据

3.1. 插入单个JSON数据

示例SQL：INSERT INTO example (json_data) VALUES ('{"name": "张三", "age": 28}');

3.2. 插入多个JSON数据

示例SQL：INSERT INTO example (json_data) VALUES ('{"name": "张三", "age": 28}'),('{"name": "李四", "age": 30}');

3.3. 插入JSON数组数据

示例SQL：INSERT INTO example (json_data) VALUES ('[
{"name": "张三", "age": 28},
{"name": "李四", "age": 30}
]');

3.4. 插入带有数组的JSON数据

示例SQL：INSERT INTO example (json_data) VALUES ('{
"name": "张三",
"age": 28,
"address": [
{"city": "上海", "street": "南京路"},
{"city": "北京", "street": "五道口"}
]
}');

4. 查询JSON数据

4.1. 查询单个JSON数据

示例SQL：SELECT json_data->>'$.name' as name from example where id = 1;

4.2. 查询数组JSON数据

示例SQL：SELECT json_data->'$.address' as address from example where id = 1;

4.3. 查询数组JSON数据中的属性值

示例SQL：SELECT json_data->'$.address[0].city' as city from example where id = 1;

通过以上操作，我们就可以在MySQL中插入和查询JSON数据了。对于多层嵌套的JSON数据，也是支持插入和查询的，只需要按照以上方法进行即可。