mysql存储json数据

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    preferences JSON
);

-- 插入数据
INSERT INTO users (name, preferences) VALUES
('Alice', '{"theme": "dark", "notifications": true}'),
('Bob', '{"theme": "light", "notifications": false}');

-- 查询特定字段
SELECT preferences->>'$.theme' AS theme FROM users WHERE name = 'Alice';

-- 更新JSON数据
UPDATE users SET preferences = JSON_SET(preferences, '$.notifications', false) WHERE name = 'Alice';

SELECT JSON_EXTRACT(preferences, '$.theme') AS theme FROM users WHERE name = 'Alice';

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    preferences JSONB
);

INSERT INTO users (name, preferences) VALUES
('Alice', '{"theme": "dark", "notifications": true}');