mysql5.7新特性JSON数据类型解析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

废话不多说,直接上实例。


一、json结构

创建测试表

1
2
3
4
5
6
CREATE  TABLE  `article` (
   `id`  int (11) unsigned  NOT  NULL  AUTO_INCREMENT,
   `category` json  NOT  NULL ,
   `tags` json  NOT  NULL ,
   PRIMARY  KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4  DEFAULT  CHARSET=utf8

分析:article表中的字段category与tags均为json类型


填写测试数据

1
2
3
4
INSERT  INTO  `article`  VALUES 
(1, '{\"id\": 1, \"name\": \"php\"}' , '[\"php\", \"mysql\", \"linux\", \"nginx\", \"redis\", \"memcache\", \"mongodb\"]' ),
(2, '{\"id\": 2, \"name\": \"java\"}' , '[\"java\", \"mysql\", \"oracel\", \"linux\", \"nginx\", \"redis\", \"memcache\", \"mongodb\"]' ),
(3, '{\"id\": \"3\", \"name\": \"c#\"}' , '[\"c\", \"c++\", \"OS\", \"linux\", \"unix\", \"IBM\"]' );


总体预览

1.png



二、json查询

select id,json_extract(category,'$.name') as name from test.article;#提取json字段里面的信息

# column->path形式 访问json中的元素 category->'$.name'
select id,category->'$.name' as name from test.article;#提取json字段里面的信息(访问json中的元素 category->'$.name')
select id,json_unquote(json_extract(category,'$.name')) as name from test.article;#提取json字段里面的信息,json_unqoute去双引号
select id,json_unquote(category->'$.name') as name from test.article;#提取json字段里面的信息,json_unqoute去双引号
select id,category->>'$.name' as name from test.article;

select * from test.article where category='{"id": 1, "name": "php"}'; #json不同于字符串,不能当作字符串做比较

select * from test.article where category=cast('{"id": 1, "name": "php"}' as JSON); #通过CAST将字符串转换成JSON形式


select * from test.article where category->'$.name'='java';

select * from test.article where category->>'$.name'='java';

#JSON 中的元素搜索是严格区分变量类型的,比如说整型和字符串是严格区分的

select * from test.article where category->'$.id'='2';#字符号串

select * from test.article where category->'$.id'=2;#整形

select * from test.article where category->'$.id'='3';#字符号串


select * from test.article where json_extract(category,'$.id')='3';#字符号串

select * from test.article where json_contains(category,'2','$.id');#整数

select * from test.article where json_contains(category,'"3"','$.id');#字符号串


select * from test.article where json_contains(tags,'"linux"');#字符号串


2、查询json格式的字段
mysql> select jsn_extract(data, '.name),jsnextract(data,.address') from user;  
+-----------------------------+-------------------------------+  
| jsn_extract(data, '.name)|jsnextract(data,.address') |  
+-----------------------------+-------------------------------+  
| "David" | "Shangahai" |  
| "Amy" | NULL |  
+-----------------------------+-------------------------------+  
2 rows in set (0.00 sec) 
3、给json格式的某个键字段创建索引。首先创建虚拟列,之后在改虚拟列上创建索引。
mysql> ALTER TABLE user ADD user_name varchar(128)  
    -> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL;  
Query OK, 0 rows affected (0.01 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
   
mysql> select user_name from user;  
+-----------+  
| user_name |  
+-----------+  
| "Amy"     |  
| "David"   |  
+-----------+  
2 rows in set (0.00 sec)  
   
mysql> alter table user add index idx_username (user_name);  
Query OK, 2 rows affected (0.01 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
4、之后通过虚拟列名对json特定列进行索引查询:
mysql> explain select * from user where user_name='"Amy"'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: idx_username
          key: idx_username
      key_len: 131
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)


三、json更新


更新 JSON

如果是整个 json 更新的话,和插入时类似的。

mysql> UPDATE lnmp SET tags = '[1, 3, 4]' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+ 
| id | category                     | tags      | 
+----+------------------------------+-----------+ 
| 1  | {"id": 1, "name": "lnmp.cn"} | [1, 3, 4] | 
| 2  | {"id": 2, "name": "php.net"} | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)

但如果要更新 JSON 下的元素,MySQL 并不支持 column->path 的形式

mysql> UPDATE lnmp SET category->'$.name' = 'lnmp', tags->'$[0]' = 2 WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->'$.name' = 'lnmp', tags->'$[0]' = 2 WHERE id = 1' at line 1

则可能要用到以下几个函数

JSON_INSERT() 插入新值,但不会覆盖已经存在的值

mysql> UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------+-----------+
| id | category                                           | tags      |
+----+----------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                       | [1, 3, 5] |
+----+----------------------------------------------------+-----------+
2 rows in set (0.00 sec)

可以看到 name 没有被修改,但新元素 url 已经添加进去

JSON_SET() 插入新值,并覆盖已经存在的值

mysql> UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                                                     | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

可以看到 host 已经插入,url 已经被修改

JSON_REPLACE() 只替换存在的值

mysql> UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}                                                         | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

可以看到 name 已经被替换,url 不存在被忽略。

JSON_REMOVE() 删除 JSON 元素

mysql> UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}     | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)

更多函数请参考:http://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html

MySQL JSON 在 PHP 中的表现

虽然在 MySQL 是个JSON 类型,但实际在 PHP 应用中返回的是 JSON 格式的字符串

array(2) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["category"]=>
    string(28) "{"id": 1, "name": "lnmp.cn"}"
    ["tags"]=>
    string(9) "[1, 3, 4]"
  }
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["category"]=>
    string(24) "{"id": 2, "name": "php"}"
    ["tags"]=>
    string(9) "[1, 3, 5]"
  }
}









本文转自 hgditren 51CTO博客,原文链接:http://blog.51cto.com/phpme/2057161,如需转载请自行联系原作者
目录
相关文章
|
12天前
|
SQL 关系型数据库 MySQL
MySQL8.0索引新特性
MySQL8.0索引新特性
18 0
|
6天前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息,使用SQL完成业务操作。SQL分为DQL(查询)、DML(增删改)、DDL(定义)、DCL(权限控制)和TCL(事务)五大类。DDL用于创建、修改和删除数据库结构,DML处理数据,DCL控制权限,TCL管理事务,DQL则用于查询数据。MySQL有多种数据类型,如数值型(整数、小数)、日期型和字符串型等,选择合适的数据类型是高效开发的关键。
17 0
|
9天前
|
存储 关系型数据库 MySQL
MySQL数据类型详解及实例应用
MySQL数据类型详解及实例应用
|
9天前
|
SQL Kubernetes 关系型数据库
实时计算 Flink版产品使用合集之怎么解析 MySQL DDL 语句
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
12天前
|
存储 JSON 数据处理
从JSON数据到Pandas DataFrame:如何解析出所需字段
从JSON数据到Pandas DataFrame:如何解析出所需字段
27 1
|
4天前
|
SQL JSON 监控
实时计算 Flink版产品使用合集之直接将 JSON 字符串解析为数组的内置函数如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
10天前
|
SQL JSON 关系型数据库
MYSQL--JSON_OBJECT 和 JSON_ARRAYAGG
MYSQL--JSON_OBJECT 和 JSON_ARRAYAGG
18 0
|
12天前
|
负载均衡 关系型数据库 MySQL
MySQL读写分离技术深度解析
在高并发、大数据量的互联网应用环境中,数据库作为数据存储的核心组件,其性能直接影响着整个系统的运行效率。MySQL作为最常用的开源关系型数据库之一,虽然功能强大,但在处理大量并发读写请求时,单点服务器的性能瓶颈逐渐显现。为了解决这一问题,MySQL读写分离技术应运而生,成为提升数据库性能、实现负载均衡的有效手段。
|
12天前
|
SQL 安全 关系型数据库
【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
|
12天前
|
SQL 存储 关系型数据库
【MySQL-6】DDL的表结构的数据类型盘点&案例演示
【MySQL-6】DDL的表结构的数据类型盘点&案例演示

推荐镜像

更多