MySQL 5.7 新特性 JSON 的创建，插入，查询，更新

从 MySQL 5.7.8 开始，MySQL 支持原生的 JSON 数据类型。

创建 JSON类似 varchar，设置 JSON 主要将字段的 type 是 json, 不能设置长度，可以是 NULL 但不能有默认值。

mysql> CREATE TABLE lnmp (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `category` JSON,
     `tags` JSON,
     PRIMARY KEY (`id`)
 );
mysql> DESC lnmp;
 +----------+------------------+------+-----+---------+----------------+
 | Field    | Type             | Null | Key | Default | Extra          |
 +----------+------------------+------+-----+---------+----------------+
 | id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
 | category | json             | YES  |     | NULL    |                |
 | tags     | json             | YES  |     | NULL    |                |
 +----------+------------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)</pre>
//就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式

 mysql> INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]');
 Query OK, 1 row affected (0.01 sec)
 mysql> INSERT INTO `lnmp` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "php.net"), JSON_ARRAY(1, 3, 5));
 Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM lnmp;
 +----+------------------------------+-----------+
 | id | category                     | tags      |
 +----+------------------------------+-----------+
 |  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
 |  2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |
 +----+------------------------------+-----------+

mysql> CREATE TABLE lnmp (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`category` JSON,

`tags` JSON,

PRIMARY KEY (`id`)

);

mysql> DESC lnmp;

+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)</pre>

//就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式

mysql> INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `lnmp` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "php.net"), JSON_ARRAY(1, 3, 5));

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM lnmp;

+----+------------------------------+-----------+

| id | category | tags |

+----+------------------------------+-----------+

| 1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |

| 2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |

+----+------------------------------+-----------+

//*******************************【select】****************************************************
//查询 json 中的数据用 *column->path *的形式，其中对象类型 path 这样表示 .path∗,而数组类型则是 ∗[index]
SELECT id, category->'$.id' c_id, category->>'$.name' c_name, tags-'$[0]' t_0, tags->'$[2]' t_2 FROM lnmp;

// 从 【MySQL 5.7.13】 起也可以通过这个【操作符 *->> *这个和 JSON_UNQUOTE】 是等价的
SELECT id, category->'$.name', JSON_UNQUOTE(category->'$.name'), category->>'$.name' FROM lnmp;

//通过 【CAST】 将字符串转成 JSON 的形式比较
SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' AS JSON);

//通过 JSON 中的【元素进行查询】, 对象型的查询同样可以通过 column->path，【作为条件-> 和 ->> 结果是一样的】
SELECT * FROM lnmp WHERE category->'$.name' = 'lnmp.cn';
SELECT * FROM lnmp WHERE category->>'$.name' = 'lnmp.cn';

//要特别注意的是，JSON 中的元素搜索是【严格区分变量类型】的，比如说整型和字符串是严格区分的
SELECT * FROM lnmp WHERE category->'$.id' = 1;

//【JSON_CONTAINS】 第二个参数是不接受整数的，无论 json 元素是整型还是字符串
SELECT * FROM lnmp WHERE JSON_CONTAINS(category, '1', '$.id');
SELECT * FROM lnmp WHERE JSON_CONTAINS(tags, '2');

//*******************************【update】**************************************************
//如果是整个 json 更新的话，和插入时类似的。
UPDATE lnmp SET tags = '[1, 3, 4]' WHERE id = 1;

//【JSON_INSERT()】 插入新值，但不会覆盖已经存在的值
UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;

//【JSON_SET()】 插入新值，并覆盖已经存在的值
UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;

//【JSON_REPLACE()】 只替换存在的值
UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;

//【JSON_REMOVE()】 删除 JSON 元素
UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;

//*******************************【insert】************************************************
//就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式
INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]');

//MySQL 也有专门的函数 【JSON_OBJECT】，【JSON_ARRAY】 生成 json 格式的数据
INSERT INTO `lnmp` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "php.net"), JSON_ARRAY(1, 3, 5));

//*******************************【select】****************************************************

//查询 json 中的数据用 *column->path *的形式，其中对象类型 path 这样表示 .path∗,而数组类型则是 ∗[index]

SELECT id, category->'$.id' c_id, category->>'$.name' c_name, tags-'$[0]' t_0, tags->'$[2]' t_2 FROM lnmp;

// 从【MySQL 5.7.13】起也可以通过这个【操作符 *->> *这个和 JSON_UNQUOTE】是等价的

SELECT id, category->'$.name', JSON_UNQUOTE(category->'$.name'), category->>'$.name' FROM lnmp;

//通过【CAST】将字符串转成 JSON 的形式比较

SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' AS JSON);

//通过 JSON 中的【元素进行查询】, 对象型的查询同样可以通过 column->path，【作为条件-> 和 ->> 结果是一样的】

SELECT * FROM lnmp WHERE category->'$.name' = 'lnmp.cn';

SELECT * FROM lnmp WHERE category->>'$.name' = 'lnmp.cn';

//要特别注意的是，JSON 中的元素搜索是【严格区分变量类型】的，比如说整型和字符串是严格区分的

SELECT * FROM lnmp WHERE category->'$.id' = 1;

//【JSON_CONTAINS】第二个参数是不接受整数的，无论 json 元素是整型还是字符串

SELECT * FROM lnmp WHERE JSON_CONTAINS(category, '1', '$.id');

SELECT * FROM lnmp WHERE JSON_CONTAINS(tags, '2');

//*******************************【update】**************************************************

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

UPDATE lnmp SET tags = '[1, 3, 4]' WHERE id = 1;

//【JSON_INSERT()】插入新值，但不会覆盖已经存在的值

UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;

//【JSON_SET()】插入新值，并覆盖已经存在的值

UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;

//【JSON_REPLACE()】只替换存在的值

UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;

//【JSON_REMOVE()】删除 JSON 元素

UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;

//*******************************【insert】************************************************

//就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式

INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]');

//MySQL 也有专门的函数【JSON_OBJECT】，【JSON_ARRAY】生成 json 格式的数据

INSERT INTO `lnmp` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "php.net"), JSON_ARRAY(1, 3, 5));

如无特殊说明，文章均为本站原创，转载请注明出处

转载请注明来源：MySQL 5.7 新特性 JSON 的创建，插入，查询，更新
本文永久链接地址：http://www.5k5c.com/blog/353

该文章由哆啦A梦发布

本站内容来自自己编写和互联网，不用作商业用途，如有侵权请和管理员联系予以删除，文章仅供学习、参考……