mysql 能存数组

-- 创建表
CREATE TABLE example (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data JSON
);

-- 插入数据
INSERT INTO example (data) VALUES ('["apple", "banana", "orange"]');

-- 查询数据
SELECT JSON_EXTRACT(data, '$[1]') AS fruit FROM example WHERE id = 1;

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

-- 创建关联表
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入数据
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1), (1, 2);

-- 查询数据
SELECT u.name, ur.role_id FROM users u JOIN user_roles ur ON u.id = ur.user_id WHERE u.name = 'Alice';