Mysql MariaDB 的JSON字段处理

在Mysql中存储具有一些格式的数据的时候使用JSON格式存储会很方便,Mysql 5.7.8 开始支持存储原生JOSN格式的数据,MariaDB 10.0.1版本优化了JOSN格式的处理。

两者还是有区别的,在此记录下如何对JSON字段的CURD

创建JSON格式字段

创建JOSN字段不能设置长度,和默认值,可以是NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--MYSQL
CREATE TABLE happyhack(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`area` JSON,
`tags` JSON
);
--Mariadb
CREATE TABLE happyhack(
`area` BLOB
);

MariaDB [test]> DESC happyhack;
--------------
DESC happyhack
--------------

+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| area | longtext | YES | | NULL | |
| tags | longtext | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+

插入JSON数据

1
INSERT INTO `happyhack` (area,tags) VALUES ('{"id": 1, "name": "如皋"}', '[1, 2, 3]');

Mysql 也有两个函数用来插入Json格式的数据JSON_OBJECTJSON_ARRAY
MariaDB 使用COLUMN_CREATE

1
2
3
4
5
6
7
8
9
10
INSERT INTO `happyhack` (area, tags) VALUES (JSON_OBJECT("id", 2, "name", "江苏"), JSON_ARRAY(1, 3, 5));

-- 这个在MariaDB中没有成功..
INSERT INTO `happyhack` (area) VALUES(COLUMN_CREATE('id','1','name','中国'));
+----+-----------------------------+-----------+
| id | area | tags |
+----+-----------------------------+-----------+
| 1 | {"id": 1, "name": "如皋"} | [1, 2, 3] |
| 2 | {"id": 2, "name": "江苏"} | [1, 3, 5] |
+----+-----------------------------+-----------+

查询JSON格式字段

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

MariaDB 貌似不支持这种写法,使用JSON_EXTRACT函数可以实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [test]> SELECT area, JSON_EXTRACT(area,'$.id'),JSON_EXTRACT(tags,'$[0]') FROM happyhack;
--------------
SELECT area, JSON_EXTRACT(area,'$.id'),JSON_EXTRACT(tags,'$[0]') FROM happyhack
--------------

+-----------------------------+---------------------------+---------------------------+
| area | JSON_EXTRACT(area,'$.id') | JSON_EXTRACT(tags,'$[0]') |
+-----------------------------+---------------------------+---------------------------+
| {"id": 1, "name": "如皋"} | 1 | 1 |
| {"id": 2, "name": "江苏"} | 2 | 1 |
+-----------------------------+---------------------------+---------------------------+

--MariaDB
SELECT area, JSON_EXTRACT(area,'$.name'),JSON_EXTRACT(tags,'$[0]') FROM happyhack;
--Mysql
SELECT area,area->'$.name',tags->'$[0]' from happyhack;

取出来的对象数据会自带"号,可以使用JSON_UNQUOTE函数去掉,目前没有Mysql数据库,下次遇到在测试

按条件查询JSON字段数据

JSON格式的数据不同于字符串,使用字符串相等查询是查询不到的;
可以使用CAST函数将字符串转成JSON格式的查询。

1
2
3
4
SELECT area FROM happyhack WHERE area = CAST('{"id": 1, "name": "如皋"}' as JSON);

SELECT area FROM happyhack WHERE area->'$.name' ='如皋";
--以上两种方法没有在Mysql中测试,以后遇到会记录

使用JSON_EXTRACT查询

1
SELECT area FROM happyhack WHERE JSON_EXTRACT(area,'$.name') = '如皋';

PHP中将JSON存入DB注意点

使用json_encode方法转为json存数据库的时候,如果是中文字符会被转义成别的码…使用JSON_UNESCAPED_UNICODE就好啦!

1
json_encode($arr,JSON_UNESCAPED_UNICODE);

Laravel 中查询JSON

1
Client::whereRaw('JSON_EXTRACT(client_region, "$[0]") like ?', ["%$request->info%"])->get();

未完待续!

参考链接

http://www.lnmp.cn/mysql-57-new-features-json.html

https://www.omgdba.com/using-json-datatype-in-mariadb-and-mysql.html