mysql设置数组

CREATE TABLE example (
    id INT PRIMARY KEY,
    array_data TEXT
);
INSERT INTO example (id, array_data) VALUES (1, '[1, 2, 3, 4, 5]');

CREATE TABLE example_items (
    id INT PRIMARY KEY,
    example_id INT,
    item_value INT,
    FOREIGN KEY (example_id) REFERENCES example(id)
);
INSERT INTO example_items (example_id, item_value) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5);

SELECT * FROM example WHERE JSON_CONTAINS(array_data, '3');

SELECT e.id, ei.item_value FROM example e JOIN example_items ei ON e.id = ei.example_id WHERE ei.item_value = 3;

UPDATE example SET array_data = JSON_SET(array_data, '$[2]', 10) WHERE id = 1;

UPDATE example_items SET item_value = 10 WHERE example_id = 1 AND item_value = 3;
INSERT INTO example_items (example_id, item_value) VALUES (1, 6);