mysql 如何处理数组

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    hobbies JSON
);

-- 插入数据
INSERT INTO users (id, hobbies) VALUES (1, '["reading", "swimming"]');

-- 查询数据
SELECT hobbies->"$[0]" AS first_hobby FROM users WHERE id = 1;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    hobbies TEXT
);

-- 插入数据
INSERT INTO users (id, hobbies) VALUES (1, '["reading", "swimming"]');

-- 查询数据
SELECT JSON_EXTRACT(hobbies, '$[0]') AS first_hobby FROM users WHERE id = 1;

-- 创建主表
CREATE TABLE users (
    id INT PRIMARY KEY
);

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

-- 插入数据
INSERT INTO users (id) VALUES (1);
INSERT INTO user_hobbies (user_id, hobby) VALUES (1, 'reading'), (1, 'swimming');

-- 查询数据
SELECT u.id, uh.hobby FROM users u JOIN user_hobbies uh ON u.id = uh.user_id WHERE u.id = 1;