mysql从5.7.8版本后支持json格式数据。
MySQL :: MySQL 8.0 Reference Manual :: 14.17 JSON Functions
拆分json对象:
JSON_EXTRACT(jsonStr_column, '$.key') jsonStr_column->'$.key'
获取存粹的json字段数据
JSON_UNQUOTE(JSON_EXTRACT(jsonStr_column, '$.key') )
jsonStr_column->>'$.key'
拆分json集合
数据:
[{"id": 1, "name": "Tom"}, {"id": 2, "name": "Jerry"}]
拆分语句:
JSON_TABLE(details, '$[*]'
COLUMNS (
id INT PATH '$.id',
name VARCHAR(255) PATH '$.name'
)
虚拟列
在MySQL中,虚拟列有两种形式:VIRTUAL
和 STORED
。
VIRTUAL
列:此类列的值在被访问时动态生成,不会物理存储在记录中,也不会占用任何磁盘空间。它们相当于视图,但是应用在单个表的列级别。STORED
列:与VIRTUAL
列不同,STORED
列在表被修改时物理保存其值。它们在磁盘上占用空间,但有利于提高读取数据的性能,因为不需要在数据被访问时再计算值。
virtual_column_name data_type GENERATED ALWAYS AS (expression) [VIRTUAL,STORED]
price DECIMAL(10, 2_value DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity)
生成json对象
json_object('name', 'name123', 'code', 'code123')
判定json中的字段为null
json_type(JSON_EXTRACT(jsonStr_column, '$.key') ) = 'NULL'
判断json中是否包含某个字段
JSON_CONTAINS_PATH()
是MySQL中的一个JSON函数,这个函数用来检测指定的JSON文档中是否包含指定路径下的值。
这个函数接收最少三个参数:
- 第一个参数是JSON文档
- 第二个参数是一个字符串,定义了是否所有的路径都需要存在。可以是 ‘one’ 或 ‘all’, ‘one’ 表示只要有任意一个路径存在就返回
1
,‘all’ 表示所有路径都必须存在才返回1
。 - 第三个参数及以后的参数,是你要检测的路径字符串。
例如,我们有一个名为 json_data
的列包含以下JSON数据:
{
"id": 1,
"name": "John Doe",
"address": {
"street": "10 Downing Street",
"city": "London"
},
"phones": ["123456", "789012"]
}
我们可以使用下面的SQL来检查 address.street
和 address.postcode
是否存在:
SELECT JSON_CONTAINS_PATH(json_data, 'all', '$.address.street', '$.address.postcode') AS result
FROM json_table;
上面的语句将会返回 0
,因为 address.postcode
路径不存在。
注意,路径是大小写敏感的,并且路径的层数可以是多层的。例如 $
表示JSON文档的顶层,$.address.street
表示 address
对象中的 street
成员。
另外一种方式判断不存在该字段:
json_type(JSON_EXTRACT(jsonStr_column, '$.key') ) is null