mysql如何存储list

-- 创建表
CREATE TABLE user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    preferences JSON
);

-- 插入数据
INSERT INTO user_preferences (user_id, preferences) VALUES (1, '["reading", "swimming", "traveling"]');

-- 查询数据
SELECT preferences->>'$[0]' AS first_preference FROM user_preferences WHERE user_id = 1;

-- 创建主表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

-- 创建关联表
CREATE TABLE user_skills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    skill VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入数据
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO user_skills (user_id, skill) VALUES (1, 'reading'), (1, 'swimming'), (1, 'traveling');

-- 查询数据
SELECT u.name, us.skill FROM users u JOIN user_skills us ON u.id = us.user_id WHERE u.id = 1;