MySQL JSON

作为前端的我，在日常前后端分离的今天对JSON再熟悉不过了，

什么是json?

{
        "name":"AursorDev",
        "age":1,
        "gender":true,
        "skills":["Node","Javascript","MySql"]
}

上面就是JSON。

在MySQL中如何存储JSON数据

表设计的时候将列的 DATA TYPE 设置成 JSON即可.

一些常用操作

---

存储JSON数据

UPDATE products
SET properties = JSON_OBJECT(
    'weight',10,
    'dimensions',JSON_ARRAY(1,2,4),
    'manufacturer',JSON_OBJECT('name','sony')
)

WHERE product_id = 1;

更新JSON数据的某个字段值

UPDATE products
SET properties = JSON_SET(
    properties,
    '$.weight','100',
    '$.age','21'
)

注意：JSON_SET返回的是一个新的JSON数据，所以我们这里可以用SET关键字

删除JSON数据的某个字段值

UPDATE products
SET properties = JSON_REMOVE(
    properties,
    '$.age'
)

JSON_REMOVE 与 JSON_SET 同理

查询数据

假如说我想查询这条数据里面的properties(properties 是个JSON)下面的weight字段

SELECT 
    product_id,
    JSON_EXTRACT(properties,'$.weight') AS weight
FROM products 
WHERE product_id = 1

SELECT 
    product_id,
    properties -> '$.weight' AS weight
FROM products 
WHERE product_id = 1

查数组里面的第一个元素

SELECT 
    product_id,
    properties -> '$.dimensions[0]' AS weight
FROM products 
WHERE product_id = 1

查嵌套数据

SELECT 
    product_id,
    properties ->> '$.manufacturer.name' AS name
FROM products 
WHERE  properties ->> '$.manufacturer.name' = 'sony'

查出来的JSON数据的值不带""

SELECT 
    product_id,
    properties ->> '$.weight' AS weight,
    properties ->> '$.age' AS age
FROM products
WHERE product_id = 1;