详解Mysql中的JSON系列操作函数

CREATE TABLE `article` (

 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(200) NOT NULL,

 `tags` json DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

INSERT INTO `article` (`title`, `tags`)

VALUES (

'体验 Mysql JSON',

'["Mysql", "Database"]'

);

SELECT * FROM `article`

WHERE JSON_CONTAINS(tags, '["Mysql"]');

SELECT * FROM `article`

WHERE JSON_SEARCH(tags, 'one', 'Data%') IS NOT NULL;

SELECT JSON_EXTRACT(

'{"id": 1, "name": "mysql"}',

'$.name'

);

{

"num": 123,

"arr": [1, 2],

"obj": {

"a": 3,

"b": 4

}

}

$.num //结果：123

$.arr //结果：[1, 2]

$.arr[1] //结果：1

$.obj.a //结果：3

$**.b //结果：4

SELECT

tags->"$[0]" as 'tag'

FROM `article`;

UPDATE `article`

SET tags = JSON_MERGE(tags, '["dev"]')

WHERE

JSON_SEARCH(tags, 'one', 'dev') IS NULL

AND

JSON_SEARCH(tags, 'one', 'Mysql') IS NOT NULL;

UPDATE `article` set tags = JSON_SET(tags, ‘$[0]', ‘Mysql 5.7.13') ;

JSON_INSERT(doc, path, val[, path, val]…)

JSON_REPLACE(doc, path, val[, path, val]…)

JSON_ARRAY_APPEND(doc, path, val[, path, val]…)

JSON_REMOVE(doc, path[, path]…)