MySQL中JSON类型使用

@[toc]

1. 结构

1.1 表结构

CREATE TABLE test
(
  id int(10）NOT NULL AUTO_INCREMENT,
  content JSON default null,
  arrs JSON default null,
  PRIMARY KEY (id)
);

1.2 content 结构

type content struct {
  name string
  age int
}

1.3 arrs 结构

var arrs []int

2. 插入

2.1 字符串插入

INSERT INTO test('id','content', 'arrs') VALUES (1,'{"name": "pibigstar","age": 20}', '[1,2,3]' )

2.2 JSON函数插入

INSERT INTO test('id','content', 'arrs') VALUES (1, JSON_OBJECT("name","pibigstar","age",20), JSON_ARRAY(1,2,3))

3. 查询

查询JSON中的数据用 column->path的形式，其中对象类型path的表示方式 .path

3.1 查询内容

SELECT id,content->'$.name' AS name, content->'$.age' AS age, arrs->'$[0]' AS arr1,arrs->'$[1]' AS arr2 FROM test;

当JSON里面的字段为string时，使用上面的方式查出来内容的带 “”，可使用 ->> 方式去掉 “”

SELECT id,content->>'$.name' AS name FROM test;

3.2 搜索条件

3.2.1 全匹配

SELECT * FROM test WHERE content = CAST('{"name": "pibigstar","age": 20}' AS JSON);

3.2.2 JSON某字段匹配

SELECT * FROM test WHERE content->'$.name' = 'pibigstar';

忽略类型

使用 ->>的方式会忽略其类型

# 查不到
SELECT * FROM test WHERE category->'$.age' = '20' 

# 能查到
SELECT * FROM test WHERE category->>'$.age' = '20'

3.2.2 使用JSON函数

SELECT * FROM test WHERE JSON_CONTAINS(content,'20','$.age');

4. 更新

4.1 全量更新

UPDATE test
SET content = JSON_OBJECT("name","haimian","age",22)
WHERE id = 1;

4.2 插入新值（不覆盖已存在值）

UPDATE test
SET content = JSON_INSERT(content, '$.name', 'haimian', '$.sex', '男')
WHERE id = 1;

执行完之后，name值还是 pibigstar，但会新增加一个 sex字段

4.3 插入新值，并覆盖已存在的值

UPDATE test
SET content = JSON_SET(content, '$.name', 'haimian', '$.age', 22, '$.sex', '男')
WHERE id = 1

执行完之后，name会改变，但会新增加一个 sex字段

4.4 替换某字段值

UPDATE test
SET content = JSON_REPLACE(content, '$.name', 'haimian')
WHERE id = 1

将name从原先的 pibigstar 改为 haimian，它只会替换已存在的字段的值

4.5 删除某字段的值

UPDATE test
SET content = JSON_REMOVE(content, '$.name', '$.age')
WHERE id = 1

删除 name 和 age 字段