mysql 传数组参数

-- 假设数组为 ['Alice', 'Bob', 'Charlie']
SET @names = 'Alice,Bob,Charlie';

INSERT INTO users (name) VALUES
(SUBSTRING_INDEX(SUBSTRING_INDEX(@names, ',', numbers.n), ',', -1))
FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
WHERE numbers.n <= LENGTH(@names) - LENGTH(REPLACE(@names, ',', '')) + 1;

-- 假设数组为 [{'name': 'Alice'}, {'name': 'Bob'}, {'name': 'Charlie'}]
SET @json_array = '[{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}]';

INSERT INTO users (info) VALUES
(JSON_EXTRACT(@json_array, CONCAT('$[', numbers.n, ']')))
FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
WHERE numbers.n < JSON_LENGTH(@json_array);

-- 错误示例
SET @names = 'Alice,Bob';
INSERT INTO users (name) VALUES (SUBSTRING_INDEX(SUBSTRING_INDEX(@names, ',', numbers.n), ',', -1))
FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) numbers;

-- 正确示例
SET @names = 'Alice,Bob';
INSERT INTO users (name) VALUES (SUBSTRING_INDEX(SUBSTRING_INDEX(@names, ',', numbers.n), ',', -1))
FROM (SELECT 1 n UNION ALL SELECT 2) numbers;

-- 错误示例
SET @json_array = '["Alice", "Bob", "Charlie"]'; -- 缺少大括号

-- 正确示例
SET @json_array = '[{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}]';
SELECT JSON_VALID(@json_array); -- 返回 1，表示 JSON 数据有效