MySQL中使用JSON存储数据的实现示例

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    data JSON
);
INSERT INTO my_table VALUES (1, '{"name": "Tom", "age": 30}'), (2, '{"name": "Jerry", "age": 20}');

SELECT data->"$.name" AS name, data->"$.age" AS age
FROM my_table;

SELECT data->"$.name" AS name, data->"$.age" AS age 
FROM my_table
WHERE data->"$.age" > 25;

UPDATE my_table 
SET `data` = JSON_SET(`data`, "$.age", 31)
WHERE id = 1;

UPDATE my_table 
SET `data` = JSON_MERGE(`data`, '{"gender":"male"}')
WHERE id = 1;

UPDATE my_table
SET `data` = JSON_REMOVE(`data`, "$.gender")
WHERE id = 1;

SELECT *
FROM my_table
ORDER BY data->>"$.age";

ALTER TABLE my_table 
add name VARCHAR(20) GENERATED ALWAYS AS (`data` ->> '$.name');
CREATE INDEX `name_idx` ON `my_table`(`name`);  

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    data JSON,
    name VARCHAR(20) GENERATED ALWAYS AS (data->"$.name") VIRTUAL,
    INDEX name_idx (name)
);