Mariadb 10.2 JSON

最新推荐文章于 2024-07-01 13:13:33 发布

随便起个名吧

最新推荐文章于 2024-07-01 13:13:33 发布

阅读量6.1k

点赞数

分类专栏： MySQL分支 MariaDB 文章标签： MariaDB JSON maria db MySQL

本文链接：https://blog.csdn.net/huzilin/article/details/77994375

版权

这篇博客详细介绍了MariaDB 10.2中对JSON数据的操作，包括创建表、插入JSON数据、添加虚拟列和索引、约束以及一系列的JSON函数，如JSON_VALUE、JSON_QUERY、JSON_CONTAINS等，用于从JSON对象中提取、查询和修改数据。

摘要由CSDN通过智能技术生成

Mariadb 10.2 JSON

Create Table

CREATE TABLE `codis3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `info` varchar(4096) DEFAULT NULL,
  `person_in_charge` varchar(5) DEFAULT NULL,
  `phone` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `CONSTRAINT_1` CHECK (json_valid(`info`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert JSON data

insert into codis3 (info,person_in_charge,phone) value ('{"product_name":"cache27","fe_url":"192.168.1.124:9090/#cache27","proxy_info":[{"proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"},{"proxy_addr":"192.168.1.162:19000","admin_addr":"192.168.1.162:21000"}],"dashboard_info":{"admin_addr":"192.168.1.124:18080","zookeeper_addr":"192.168.1.124:2181,192.168.1.125:2181,192.168.1.127:2181"},"group_info":[{"group_id":1,"server":["192.168.1.161:6360","192.168.1.30:6360"]},{"group_id":2,"server":["192.168.1.161:6361","192.168.1.30:6361"]},{"group_id":3,"server":["192.168.1.161:6362","192.168.1.30:6362"]},{"group_id":4,"server":["192.168.1.161:6363","192.168.1.30:6364"]},{"group_id":5,"server":["192.168.1.161:6364","192.168.1.30:6364"]},{"group_id":6,"server":["192.168.1.162:6365","192.168.1.31:6365"]},{"group_id":7,"server":["192.168.1.162:6366","192.168.1.31:6366"]},{"group_id":8,"server":["192.168.1.162:6367","192.168.1.31:6367"]},{"group_id":9,"server":["192.168.1.162:6368","192.168.1.31:6368"]},{"group_id":10,"server":["192.168.1.162:6369","192.168.1.31:6369"]}]}', '某某', 123456789012);

Add virtual column

alter table codis3 add column product_name varchar(10) as (JSON_VALUE(info, '$.product_name')) VIRTUAL;

select * from codis3\G
*************************** 1. row ***************************
              id: 1
            info: {
   "product_name":"cache27","fe_url":"192.168.1.124:9090/#cache27","proxy_info":[{
   "proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"},{
   "proxy_addr":"192.168.1.162:19000","admin_addr":"192.168.1.162:21000"}],"dashboard_info":{
   "admin_addr":"192.168.1.124:18080"