mysql数据库中如何使用JSON数据类型

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    info JSON
);

INSERT INTO users (name, info) VALUES 
('Alice', '{"age": 28, "city": "Beijing", "hobbies": ["reading", "swimming"]}'),
('Bob',   '{"age": 32, "city": "Shanghai", "is_active": true}');

-- 提取年龄
SELECT name, JSON_EXTRACT(info, '$.age') AS age FROM users;
<p>-- 使用 -> 简写
SELECT name, info->'$.city' AS city FROM users;</p><p>-- 提取数组第二个爱好（索引从0开始）
SELECT name, info->'$.hobbies[1]' AS hobby FROM users;</p><p>-- 提取字符串值时不带引号
SELECT name, info->>'$.city' AS city FROM users;</p>

-- 更新城市并添加新字段
UPDATE users 
SET info = JSON_SET(info, '$.city', 'Guangzhou', '$.level', 3)
WHERE name = 'Alice';
<p>-- 删除某个字段
UPDATE users 
SET info = JSON_REMOVE(info, '$.is_active')
WHERE name = 'Bob';</p>

-- 查找年龄大于30的用户
SELECT * FROM users WHERE info->'$.age' > 30;
<p>-- 查找爱好包含'swimming'的用户（需用JSON_CONTAINS）
SELECT * FROM users 
WHERE JSON_CONTAINS(info->'$.hobbies', '"swimming"');</p>

ALTER TABLE users 
ADD COLUMN city_v AS (info->>'$.city') STORED;
<p>CREATE INDEX idx_city ON users(city_v);</p>