mysql 传数组参数

基础概念

MySQL 是一种关系型数据库管理系统，广泛用于存储和管理结构化数据。在 MySQL 中，通常使用 SQL 语句来执行数据的增删改查操作。数组是一种数据结构，用于存储多个相同类型的数据项。然而，MySQL 本身并不直接支持数组类型，但可以通过一些方法来处理数组参数。

类型

字符串数组：将数组转换为字符串，通过分隔符（如逗号）分隔各个元素。
JSON 数组：将数组转换为 JSON 格式，MySQL 从版本 5.7 开始支持 JSON 数据类型。

应用场景

批量插入：将多个记录一次性插入数据库。
批量更新：根据数组中的条件批量更新记录。
批量删除：根据数组中的条件批量删除记录。

示例代码

字符串数组传递

假设我们有一个用户表 users，包含 id 和 name 字段，我们希望通过数组批量插入用户。

-- 假设数组为 ['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;

JSON 数组传递

假设我们有一个用户表 users，包含 id 和 info 字段，info 字段为 JSON 类型。

-- 假设数组为 [{'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);

遇到的问题及解决方法

问题：数组元素数量不匹配

原因：传递的数组元素数量与 SQL 语句中的占位符数量不匹配。

解决方法：确保数组元素数量与 SQL 语句中的占位符数量一致。

-- 错误示例
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;

问题：JSON 数组解析错误

原因：传递的 JSON 数组格式不正确。

解决方法：确保 JSON 数组格式正确，并使用 JSON_VALID 函数验证 JSON 数据的有效性。

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

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