旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

MySQL5.7的JSON基础操纵(代码示例)【MySQL教程】,mysql,php

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:75评论:0


导读:本篇文章给人人带来的内容是关于MySQL5.7的JSON基础操纵(代码示例),有肯定的参考价值,有须要的朋侪能够参考一下,愿望对你有所协助。MySQL5.7的JSON基础操纵...

本篇文章给人人带来的内容是关于MySQL5.7的JSON基础操纵(代码示例),有肯定的参考价值,有须要的朋侪能够参考一下,愿望对你有所协助。

MySQL5.7的JSON基础操纵

MySQL从5.7版本最先就支撑JSON花样的数据,操纵用起来挺轻易的。

建表
在新建表时字段范例能够直接设置为json范例,比方我们建立一张表:

mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);

json范例字段能够为NULL

插进去数据:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');

json范例的字段必需时一个有用的json字符串

能够运用JSON_OBJECT()函数组织json对象:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));

运用JSON_ARRAY()函数组织json数组:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

如今检察test_user表中的数据:

mysql> select * from test_user; 
+----+-----------+--------------------------------------------+ 
| id | name      | info                                       |
+----+-----------+--------------------------------------------+ 
|  1 | xiaoming  | {"age": 18, "sex": 1, "nick_name": "小萌"} | 
|  2 | xiaohua   | {"age": 17, "sex": 0}                      |
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]}   | 
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)

查询
表达式: 对象为json列->'$.键', 数组为json列->'$.键[index]'

mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; 
+-----------+---------------------+---------------+------------------+ 
| name      | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | 
+-----------+---------------------+---------------+------------------+ 
| xiaoming  | "小萌"              | 1             | NULL             | 
| xiaohua   | NULL                | 0             | NULL             | 
| xiaozhang | NULL                | 1             | 3                | 
+-----------+---------------------+---------------+------------------+ 
3 rows in set (0.04 sec)

等价于:对象为JSON_EXTRACT(json列 , '$.键'),数组为JSON_EXTRACT(json列 , '$.键[index]')

mysql> select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]')  from test_user;
 +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| name      | JSON_EXTRACT(info, '$.nick_name') | JSON_EXTRACT(info, '$.sex') | JSON_EXTRACT(info, '$.tag[0]') 
| +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| xiaoming  | "小萌"                            | 1                           | NULL                           |
| xiaohua   | NULL                              | 0                           | NULL                           | 
| xiaozhang | NULL                              | 1                           | 3                              | 
+-----------+-----------------------------------+-----------------------------+--------------------------------+ 
3 rows in set (0.04 sec)

不过看到上面"小萌"是带双引号的,这不是我们想要的,能够用JSON_UNQUOTE函数将双引号去掉

mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming'; 
+----------+-----------------------------------+ 
| name     | JSON_UNQUOTE(info->'$.nick_name') | 
+----------+-----------------------------------+ 
| xiaoming | 小萌                              | 
+----------+-----------------------------------+ 
1 row in set (0.05 sec)

也能够直接运用操纵符->>

mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming';
+----------+----------------------+ 
| name     | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.06 sec)

固然属性也能够作为查询前提

mysql> select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌'; 
+----------+----------------------+ 
| name     | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.05 sec)

值得一提的是,能够经由过程假造列对JSON范例的指定属性举行疾速查询。

建立假造列:

mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;

注重用操纵符->>

运用时和一般范例的列查询是一样:

mysql> select name,nick_name from test_user where nick_name='小萌'; 
+----------+-----------+ 
| name     | nick_name | 
+----------+-----------+ 
| xiaoming | 小萌      | 
+----------+-----------+ 
1 row in set (0.05 sec)

更新
运用JSON_INSERT()插进去新值,但不会掩盖已存在的值

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

看下效果

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

运用JSON_SET()插进去新值,并掩盖已存在的值

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

看下效果

mysql> select * from test_user where id=3; 
+----+-----------+---------------------------------------------------------------+-----------+ 
| id | name      | info                                                          | nick_name | 
+----+-----------+---------------------------------------------------------------+-----------+ 
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小张"} | 小张      | 
+----+-----------+---------------------------------------------------------------+-----------+ 
1 row in set (0.06 sec)

运用JSON_REPLACE()只替代存在的值

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

看下效果

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

能够看到tag没有更新进去

删除

运用JSON_REMOVE()删除JSON元素

mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;

看下效果

mysql> select * from test_user where id=1; 
+----+----------+----------------------------------+-----------+ 
| id | name     | info                             | nick_name | 
+----+----------+----------------------------------+-----------+ 
|  1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌      | 
+----+----------+----------------------------------+-----------+ 
1 row in set (0.05 sec)

本篇文章到这里就已悉数完毕了,更多其他精彩内容能够关注ki4网的MySQL教程视频栏目!

以上就是MySQL5.7的JSON基础操纵(代码示例)的细致内容,更多请关注ki4网别的相干文章!

标签:mysqlphp


欢迎 发表评论: