使用Erlang的mysql-otp驱动批量插入多行数据的方法问询

嘿，我完全懂你的困扰——mysql-otp的文档确实没把批量插入的细节讲透，你试的那种嵌套元组列表的写法确实走不通，因为驱动明确要求Params是扁平的term列表，而不是嵌套结构。不过别担心，有两种实用的方式可以实现批量插入：

方法一：构造多行VALUES的单条SQL语句 #

这是效率最高的方式，适合插入大量数据。核心思路是把所有行的参数扁平化成一个列表，同时在SQL里生成对应的多个(?, ?)子句。你可以写个小工具函数来自动拼接SQL和处理参数：

batch_insert(Pid, Table, Columns, Rows) ->
    % 生成每个行对应的(?, ?)子句
    ValueClause = "(" ++ string:join(lists:duplicate(length(Columns), "?"), ", ") ++ ")",
    % 把所有子句用逗号连接
    ValueClauses = string:join(lists:duplicate(length(Rows), ValueClause), ", "),
    % 拼接完整的INSERT语句
    Sql = io_lib:format("INSERT INTO ~s (~s) VALUES ~s", [Table, string:join(Columns, ", "), ValueClauses]),
    % 把嵌套的元组列表扁平化为一维参数列表
    FlatParams = lists:flatten(Rows),
    % 执行查询
    mysql:query(Pid, Sql, FlatParams).

% 使用示例
ok = batch_insert(Pid, "mytable", ["id", "bar"], [{1, 42}, {2, 36}, {3, 12}]).

这个函数会自动生成类似INSERT INTO mytable (id, bar) VALUES (?, ?), (?, ?), (?, ?)的SQL，参数则是[1,42,2,36,3,12]，完全符合驱动的参数要求。

方法二：用事务批量执行单行插入 #

如果插入的行数不多，这种方式更直观，不需要拼接复杂的SQL，还能保证原子性（要么全成功，要么全回滚）：

batch_insert_with_transaction(Pid, Rows) ->
    % 开启事务
    ok = mysql:query(Pid, "START TRANSACTION"),
    try
        % 遍历每一行执行插入
        lists:foreach(fun({Id, Bar}) ->
            ok = mysql:query(Pid, "INSERT INTO mytable (id, bar) VALUES (?, ?)", [Id, Bar])
        end, Rows),
        % 提交事务
        ok = mysql:query(Pid, "COMMIT")
    catch
        % 出错时回滚事务
        _:_ ->
            ok = mysql:query(Pid, "ROLLBACK"),
            error(batch_insert_failed)
    end.

% 使用示例
ok = batch_insert_with_transaction(Pid, [{1, 42}, {2, 36}, {3, 12}]).

这种方式的缺点是每一行都要发一次请求，数据量很大时性能不如第一种方法，但胜在代码简单易懂，适合小批量场景。

为什么你原来的写法不行？ #

你试的[(1,42),(2,36),(3,12)]是一个包含三个元组的列表，而驱动期望的是和SQL占位符数量完全匹配的扁平列表。比如你的SQL里有2个占位符，但你传了3个参数（每个元组算一个），驱动会因为参数数量不匹配而报错，所以这种写法确实不可行。

内容的提问来源于stack exchange，提问作者Roman Rabinovich