天天看點
傳回

Mariadb 10.2 JSONMariadb 10.2 JSON

Mariadb 10.2 JSON

Create Table 

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

Insert JSON data

Add virtual column 

select * from codis3\G
***************************  row ***************************
              id: 
            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","zookeeper_addr":"192.168.1.124:2181,192.168.1.125:2181,192.168.1.127:2181"},"group_info":[{"group_id":,"server":["192.168.1.161:6360","192.168.1.30:6360"]},{"group_id":,"server":["192.168.1.161:6361","192.168.1.30:6361"]},{"group_id":,"server":["192.168.1.161:6362","192.168.1.30:6362"]},{"group_id":,"server":["192.168.1.161:6363","192.168.1.30:6364"]},{"group_id":,"server":["192.168.1.161:6364","192.168.1.30:6364"]},{"group_id":,"server":["192.168.1.162:6365","192.168.1.31:6365"]},{"group_id":,"server":["192.168.1.162:6366","192.168.1.31:6366"]},{"group_id":,"server":["192.168.1.162:6367","192.168.1.31:6367"]},{"group_id":,"server":["192.168.1.162:6368","192.168.1.31:6368"]},{"group_id":,"server":["192.168.1.162:6369","192.168.1.31:6369"]}]}
person_in_charge: 某某
           phone: 
    product_name: cache27

Add Index 

mysql> explain select * from codis3 where product_name='cache27';
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | codis3 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set ( sec)

mysql> alter table codis3 add index idx_pn(product_name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from codis3 where product_name='cache27';
+------+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
| id   | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+------+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
|    1 | SIMPLE      | codis3 | ref  | idx_pn        | idx_pn | 33      | const |    1 | Using where |
+------+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
1 row in set ( sec)

ADD CONSTRAINT 

alter table codis3 ADD CONSTRAINT check_info
CHECK(
JSON_TYPE(JSON_QUERY(info, '$.proxy_info')) = 'ARRAY' and
JSON_TYPE(JSON_QUERY(info, '$.dashboard_info')) = 'OBJECT' and
JSON_TYPE(JSON_QUERY(info, '$.group_info')) = 'ARRAY' and
JSON_EXISTS(info, '$.product_name') =  and
JSON_EXISTS(info, '$.fe_url') =  and
JSON_EXISTS(info, '$.proxy_info') =  and
JSON_EXISTS(info, '$.dashboard_info') =  and
JSON_EXISTS(info, '$.group_info') = );

還有以下校驗規則

JSON_TYPE(JSON_VALUE(attr, '$.disks')) = 'INTEGER' and
JSON_EXISTS(attr, '$.video.resolution') = 1 and
JSON_EXISTS(attr, '$.video.aspectRatio') = 1 and
JSON_LENGTH(JSON_QUERY(attr, '$.cuts')) > 0 and
JSON_LENGTH(JSON_QUERY(attr, '$.audio')) > 0);

Function

JSON_VALUE

查詢json中的一個value

select JSON_VALUE(info, "$.group_info[0].server[0]") from codis3;
+-----------------------------------------------+
| json_value(info, "$.group_info[0].server[0]") |
+-----------------------------------------------+
| 192.168.1.161:6360                            |
+-----------------------------------------------+

JSON_QUERY

讀取一個array

select JSON_QUERY(info, '$.proxy_info')  from codis3;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_QUERY(info, '$.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"}] |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
           
select JSON_QUERY(info, '$.proxy_info[0]')  from codis3;
+-------------------------------------------------------------------------+
| JSON_QUERY(info, '$.proxy_info[0]')                                     |
+-------------------------------------------------------------------------+
| {"proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"} |
+-------------------------------------------------------------------------+

讀取一個object

select JSON_QUERY(info, '$.dashboard_info')  from codis3;
+------------------------------------------------------------------------------------------------------------------+
| JSON_QUERY(info, '$.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"} |
+------------------------------------------------------------------------------------------------------------------+

JSON_CONTAINS

判斷array中是否包含給定值，包含傳回1，否則傳回0

select id from codis3 where JSON_CONTAINS(info, '\"192.168.1.161:6360\"', '$.group_info[0].server');
+----+
| id |
+----+
|  1 |
+----+

JSON_OBJECT

将多個列json化

select JSON_OBJECT('id', id, 'product_name', product_name, 'person_in_charge', person_in_charge, 'phone', phone) from codis3;
+-----------------------------------------------------------------------------------------------------------+
| JSON_OBJECT('id', id, 'product_name', product_name, 'person_in_charge', person_in_charge, 'phone', phone) |
+-----------------------------------------------------------------------------------------------------------+
| {"id": 1, "product_name": "cache27", "person_in_charge": "\u67D0\u67D0", "phone": 123456789012}           |
+-----------------------------------------------------------------------------------------------------------+

JSON_INSERT

插入一個field

update codis3 set info=JSON_INSERT(info, "$.test", "192.168.1.161:6364") where id=;

select JSON_VALUE(info, "$.test") from codis3;
+----------------------------+
| JSON_VALUE(info, "$.test") |
+----------------------------+
| 192.168.1.161:6364         |
+----------------------------+

插入一個array

update codis3 set info=JSON_INSERT(info, "$.test_arr", JSON_ARRAY('English', 'French')) where id=;

select JSON_QUERY(info, "$.test_arr") from codis3;
+--------------------------------+
| JSON_QUERY(info, "$.test_arr") |
+--------------------------------+
| ["English", "French"]          |
+--------------------------------+

插入一個帶array的object

update codis3 set info=JSON_INSERT(info, "$.test_obj", JSON_OBJECT("group_id", , "server", JSON_ARRAY('192.168.1.32:6370', '192.168.1.163:6370'))) where id=;

select JSON_QUERY(info, "$.test_obj") from codis3;
+-------------------------------------------------------------------------+
| JSON_QUERY(info, "$.test_obj")                                          |
+-------------------------------------------------------------------------+
| {"group_id": 11, "server": ["192.168.1.32:6370", "192.168.1.163:6370"]} |
+-------------------------------------------------------------------------+

JSON_ARRAY_APPEND

向array中append值

update codis3 set info=JSON_ARRAY_APPEND(info, "$.group_info", JSON_OBJECT("group_id", , "server", JSON_ARRAY('192.168.1.32:6370', '192.168.1.163:6370'))) where id=;

select JSON_QUERY(info, "$.group_info") from codis3\G
***************************  row ***************************
JSON_QUERY(info, "$.group_info"): [{"group_id": , "server": ["192.168.1.161:6360", "192.168.1.30:6360"]}, {"group_id": , "server": ["192.168.1.161:6361", "192.168.1.30:6361"]}, {"group_id": , "server": ["192.168.1.161:6362", "192.168.1.30:6362"]}, {"group_id": , "server": ["192.168.1.161:6363", "192.168.1.30:6364"]}, {"group_id": , "server": ["192.168.1.161:6364", "192.168.1.30:6364"]}, {"group_id": , "server": ["192.168.1.162:6365", "192.168.1.31:6365"]}, {"group_id": , "server": ["192.168.1.162:6366", "192.168.1.31:6366"]}, {"group_id": , "server": ["192.168.1.162:6367", "192.168.1.31:6367"]}, {"group_id": , "server": ["192.168.1.162:6368", "192.168.1.31:6368"]}, {"group_id": , "server": ["192.168.1.162:6369", "192.168.1.31:6369"]}, {"group_id": , "server": ["192.168.1.32:6370", "192.168.1.163:6370"]}]
 row in set ( sec)

JSON_REMOVE

删除某一部分

MySQL分支 mariadb json maria db mysql
上一篇: 【資料庫學習】 之 函數依賴及其公理/定理思維導圖一、總概括
下一篇: 使用MyBatis+Mysql實作分頁的插件PageInfo使用介紹1. 引入Jar包 2.使用maven

繼續閱讀