mysql建表 数组类型

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

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

-- 查询数据
SELECT preferences->>'$.theme' AS theme FROM users WHERE id = 1;

-- 创建表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    tag_list TEXT
);

-- 插入数据
INSERT INTO tags (name, tag_list) VALUES ('Article1', '["tech", "programming", "mysql"]');

-- 查询数据
SELECT tag_list FROM tags WHERE id = 1;

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

CREATE TABLE roles (
    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),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

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

-- 查询数据
SELECT u.name AS user_name, r.name AS role_name FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;