MySQL对JSON数据的增删改查，用起来很方便！

MySQL从5.7版本开始就支持JSON格式的数据，操作用起来挺方便的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

建表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

在新建表时字段类型可以直接设置为json类型，比如我们创建一张表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

CREATE TABLE test_user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR ( 50 ) NOT NULL,
info JSON
);
json类型字段可以为NULL文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

插入数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

INSERT INTO test_user(name, info) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

json类型的字段必须时一个有效的json字符串文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

可以使用JSON_OBJECT()函数构造json对象：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

INSERT INTO test_user(name, info) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

使用JSON_ARRAY()函数构造json数组文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

INSERT INTO test_user(name, info) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

查询test_user所有的数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select * from test_user;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

查询文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

表达式： 对象为json列->'$.键', 数组为json列->'$.键[index]'文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

等价于：对象为JSON_EXTRACT(json列 , '$.键') ，数组为JSON_EXTRACT(json列 , '$.键[index]')文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]') from test_user;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

不过看到上面"小萌"是带双引号的，这不是我们想要的，可以用JSON_UNQUOTE函数将双引号去掉文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

也可以直接使用操作符->>文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select name, info->>'$.nick_name' from test_user where name='xiaoming';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

当然属性也可以作为查询条件文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

SELECT LoanNo FROM wbhj_loan lo WHERE lo.contractInfo ->> '$.contractNo'= '11' AND lo.loanResult=1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

值得一提的是，可以通过虚拟列对JSON类型的指定属性进行快速查询。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

创建虚拟列:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

ALTER TABLE test_user ADD nick_name VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

注意用操作符->>文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

使用时和普通类型的列查询是一样:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

select name,nick_name from test_user where nick_name='小萌';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

更新文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

使用JSON_INSERT()插入新值，但不会覆盖已经存在的值文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

使用JSON_SET()插入新值，并覆盖已经存在的值文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

使用JSON_REPLACE()只替换存在的值文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

可以看到tag没有更新进去文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

jsonArray格式查询文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html

SELECT
json_extract(a.summaryDesc,('$[0].sellerInfoSection[1].houseNo'))
FROM
channel_summary a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/24649.html