mysql设置字段数组

-- 序列化存储
CREATE TABLE example (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO example (id, data) VALUES (1, '["apple", "banana", "cherry"]');

-- 关联表
CREATE TABLE main_table (
    id INT PRIMARY KEY
);

CREATE TABLE array_elements (
    id INT PRIMARY KEY,
    main_id INT,
    element VARCHAR(255),
    FOREIGN KEY (main_id) REFERENCES main_table(id)
);

INSERT INTO main_table (id) VALUES (1);
INSERT INTO array_elements (id, main_id, element) VALUES (1, 1, 'apple');
INSERT INTO array_elements (id, main_id, element) VALUES (2, 1, 'banana');
INSERT INTO array_elements (id, main_id (3, 1, 'cherry');

-- 序列化存储
SELECT JSON_EXTRACT(data, '$[1]') AS element FROM example WHERE id = 1;

-- 关联表
SELECT element FROM array_elements WHERE main_id = 1;

-- 序列化存储
UPDATE example SET data = JSON_SET(data, '$[1]', 'orange') WHERE id = 1;

-- 关联表
UPDATE array_elements SET element = 'orange' WHERE id = 2;