mysql数据库json数据的操作

2019-07-18 本文已影响5人小石读史

经常忘记mysql数据中json数据的操作方式，笔记记录一下

#创建表

CREATE TABLE `test_json` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',

`name` varchar(255) DEFAULT '' COMMENT '姓名',

`attach` varchar(1000) DEFAULT '{}' COMMENT ' 拓展信息',

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='json数据测试表';

#插入记录

INSERT INTO `test_json` (`name`, `attach`) VALUES ('名字1', '{\"parentChannelId\": 1, \"discountActivityId\": 10}');

INSERT INTO `test_json` (`name`, `attach`) VALUES ('名字2', '{\"parentChannelId\": 2, \"discountActivityId\": 20}');

INSERT INTO `test_json` (`name`, `attach`) VALUES ('名字3', '{\"parentChannelId\": 3, \"discountActivityId\": 30}');

#增加键

UPDATE test_json SET attach = json_set(attach,'$.ip','127.0.0.1') where id = 3;

#删除键

UPDATE test_json SET attach = json_remove(attach,'$.ip') where id = 3;

#变更值

UPDATE test_json SET attach = json_set(attach,'$.ip','127.0.0.3') where id = 3;