mysql 基础知识(7) - JSON 字段
在前公司的时候,大家习惯在每个表加一个 extra 字段来表示一些额外的字段,然后在 ORM 中使用的时候再解析出来,方便了扩展字段,但是缺点也很明显,extra 字段只能读取而无法进行查询。MySQL 5.7 终于支持了 json 字段,相当于加入了一些 NoSQL 的特性,这样就可以很方便得查询了。
json 字段的使用
建表:
CREATE TABLE `book` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`tags` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
直接使用 json 类型就可以了。注意 json 字段不可以作为主键,不可以作为外键,不过既然是 json 字段了,谁会这么做呢。。
插入:
INSERT INTO `book` (`title`, `tags`)
VALUES (
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
);
使用一个 json 字符串作为值插入即可。或者你也可以使用 json 相关的函数来表示json。
json 相关函数
json path
-- returns "SitePoint":
SELECT JSON_EXTRACT(
'{"id": 1, "website": "SitePoint"}',
'$.website'
);
json path 的语法,用 $ 开头,然后跟着下面的选择器:
.
点后面跟着跟着一个字典里的名字, 比如 $.website[N]
表示数组里的第 N 个元素.[*]
表示选择字典里的所有元素[*]
表示选择数组里的所有元素prefix**suffix
表示以 prefix 开头,suffix 结尾的所有路径
举个栗子
{
"a": 1,
"b": 2,
"c": [3, 4],
"d": {
"e": 5,
"f": 6
}
}
the following paths:
$.a returns 1
$.c returns [3, 4]
$.c[1] returns 4
$.d.e returns 5
$**.e returns [5]
构造、修改 json 的函数
函数都比较简单,看注释就明白了。
-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');
-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);
-- returns ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');
-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');
-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');
-- returns 1:
SELECT JSON_VALID('[1, 2, "abc"]');
还有其他一些函数,可以查看文档:
- JSON_SET(doc, path, val[, path, val]...) — inserts or updates data in the document
- JSON_INSERT(doc, path, val[, path, val]...) — inserts data into the document
- JSON_REPLACE(doc, path, val[, path, val]...) — replaces data in the document
- JSON_MERGE(doc, doc[, doc]...) — merges two or more documents
- JSON_ARRAY_APPEND(doc, path, val[, path, val]...) — appends values to the end of an array
- JSON_ARRAY_INSERT(doc, path, val[, path, val]...) — inserts an array within the document
- JSON_REMOVE(doc, path[, path]...) — removes data from the document.
查询 json 函数
用于 where 子句中的函数
json_contains 用于选取数组中包含某个元素的行
-- all books with the 'JavaScript' tag:
SELECT * FROM `book`
WHERE JSON_CONTAINS(tags, '["JavaScript"]');
json_search 用于选取字典中包含某个值的行
-- all books with tags starting 'Java':
SELECT * FROM `book`
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;
用于 select 子句中的 json 函数
可以使用 json path 语法从得到的 json 文档中抽取出某个值。
select 语句
要想在select语句中使用 json path 抽取元素可以使用下面的语法,也就是 column->path
SELECT
name,
tags->"$[0]" AS `tag1`
FROM `book`;
一个更复杂一点的例子:
id | name | profile |
---|---|---|
1 | Craig | |
2 | SitePoint |
SELECT
name, profile->"$.twitter" AS `twitter`
FROM `user`;
SELECT
name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
profile->"$.twitter" IS NOT NULL;
REF:
- https://www.sitepoint.com/use-json-data-fields-mysql-databases/