mysql数据库json数据的操作

经常忘记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;