我正在使用 Erlang 的 mysql-otp 驱动程序。它似乎工作正常,但没有关于使用它在表中插入多行的文档。
单行插入的简单用例:
ok = mysql:query(Pid, "INSERT INTO mytable (id, bar) VALUES (?, ?)", [1, 42]).
但是我需要插入多个值,我可以做这样的事情吗?
ok = mysql:query(Pid, "INSERT INTO mytable (id, bar) VALUES (?, ?)", [(1, 42),(2, 36), (3,12)]).
文档指出 Params = [term((],所以可能不是,这很糟糕。
您当然可以在参数上组合 lists:foldl/3 和 lists:join/2 来创建所需的查询格式:
L = [[1, 42],[2, 36], [3,12]],
PreparedList = lists:foldl(fun (Params, Inserts) -> Inserts ++ [io_lib:format("(~p,~p)", Params)] end, [], L),
%% Then you need to join these with a comma:
Prepared = lists:flatten(lists:join(",", PreparedList)),
%% this will result in "(1,42),(2,36),(3,12)"
现在你只需要用这个Prepared
变量调用 mysql 插入:
ok = mysql:query(Pid, "INSERT INTO mytable (id, bar) VALUES ?", [Prepared]).
%% The query will look like: "INSERT INTO mytable (id, bar) VALUES (1,42),(2,36),(3,12)"
我不认为这个驱动程序或mysql可以做这种事情。
我认为你应该像下面一样做
insert_mytable(Data)->
{ok,Ref} = mysql:prepare(Pid,insert_mytable,"INSERT INTO mytable (id, bar) VALUES (?, ?)"),
loop_insert(_Pid,Ref,Data).
loop_insert(_Pid,_Ref,[])-> ok;
loop_insert(Pid,Ref,[H|T])->
ok = mysql:execute(Pid,Ref,H),
loop_insert(Pid,Ref,T).