[lua, mysql] 将多条记录数据组合成一条sql插入语句(for mysql)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

-- 演示将多条记录数据组合成一条sql插入语句(for mysql)   function getTpl0(tname)     -- 获取表各个字段     local t = {         tpl_pack = {"packId","itemId","`group`","num","rate","rateType"},     }     for k, v in pairs(t) do         if tname == k then             return t[k]         end     end end   --tpl = {3813,10,0,2,0,1,1,0,350,5,220,6,0,0,0,0,154,0,0,0,210,80,29} tpl9122 = { -- "packId","itemId","`group`","num","rate","rateType"     {9122, 3294, '', 1, 1, 2},     {9122, 3295, '', 1, 1, 2},     {9122, 3296, '', 1, 1, 2},     {9122, 3297, '', 1, 1, 2},     {9122, 3298, '', 1, 1, 2},       {9122, 9004, '', 2, 4, 2},     {9122, 117, '', 8, 4, 2},     {9122, 118, '', 8, 4, 2},     {9122, 119, '', 8, 4, 2},     {9122, 120, '', 8, 4, 2},     {9122, 322, '', 2, 4, 2},     {9122, 160, '', 5, 5, 2},     {9122, 327, '', 5, 5, 2},     {9122, 2900, '', 1, 6, 2},     {9122, 9101, '', 20, 7, 2},     {9122, 115, '', 15, 10, 2},     {9122, 114, '', 15, 12, 2},     {9122, 112, '', 15, 13, 2},     {9122, 113, '', 15, 13, 2}, }   tpl9123 = { -- "packId","itemId","`group`","num","rate","rateType"     {9123, 3299, '', 1, 1, 2},     {9123, 3300, '', 1, 1, 2},     {9123, 3301, '', 1, 1, 2},     {9123, 3302, '', 1, 1, 2},     {9123, 3303, '', 1, 1, 2},       {9123, 9004, '', 2, 4, 2},     {9123, 117, '', 8, 4, 2},     {9123, 118, '', 8, 4, 2},     {9123, 119, '', 8, 4, 2},     {9123, 120, '', 8, 4, 2},     {9123, 322, '', 2, 4, 2},     {9123, 160, '', 5, 5, 2},     {9123, 327, '', 5, 5, 2},     {9123, 2900, '', 1, 6, 2},     {9123, 9101, '', 20, 7, 2},     {9123, 115, '', 15, 10, 2},     {9123, 114, '', 15, 12, 2},     {9123, 112, '', 15, 13, 2},     {9123, 113, '', 15, 13, 2}, }   function createInsertSql(tname, tpl)     local tpl0 = getTpl0(tname)     -- 获取表各个字段     local ret = {}                  -- 插入记录sql       table.insert(ret, string.format("insert into `%s`(", tname))     for k, v in pairs(tpl0) do         if k > 1 then             table.insert(ret, ",")         end         table.insert(ret, v)     end     table.insert(ret, ") values ")       for k, v in pairs(tpl) do         if k > 1 then             table.insert(ret, ",")         end         table.insert(ret, "(")         for k2, v2 in pairs(v) do             if k2 > 1 then                 table.insert(ret, ",")             end             if type(v2) == "string" then                 table.insert(ret, string.format("'%s'", v2))             else                 table.insert(ret, v2)             end         end         table.insert(ret, ")")     end     table.insert(ret, ";")       local result = table.concat(ret)    -- 最终的sql语句     print(result)     print() end createInsertSql("tpl_pack", tpl9122) createInsertSql("tpl_pack", tpl9123)

 最终的执行结果如下:

?

1 2 3 4

[zcm@lua 6]$lua t1.lua insert into `tpl_pack`(packId,itemId,`group`,num,rate,rateType) values (9122,3294,'',1,1,2),(9122,3295,'',1,1,2),(9122,3296,'',1,1,2),(9122,3297,'',1,1,2),(9122,3298,'',1,1,2),(9122,9004,'',2,4,2),(9122,117,'',8,4,2),(9122,118,'',8,4,2),(9122,119,'',8,4,2),(9122,120,'',8,4,2),(9122,322,'',2,4,2),(9122,160,'',5,5,2),(9122,327,'',5,5,2),(9122,2900,'',1,6,2),(9122,9101,'',20,7,2),(9122,115,'',15,10,2),(9122,114,'',15,12,2),(9122,112,'',15,13,2),(9122,113,'',15,13,2);   insert into `tpl_pack`(packId,itemId,`group`,num,rate,rateType) values (9123,3299,'',1,1,2),(9123,3300,'',1,1,2),(9123,3301,'',1,1,2),(9123,3302,'',1,1,2),(9123,3303,'',1,1,2),(9123,9004,'',2,4,2),(9123,117,'',8,4,2),(9123,118,'',8,4,2),(9123,119,'',8,4,2),(9123,120,'',8,4,2),(9123,322,'',2,4,2),(9123,160,'',5,5,2),(9123,327,'',5,5,2),(9123,2900,'',1,6,2),(9123,9101,'',20,7,2),(9123,115,'',15,10,2),(9123,114,'',15,12,2),(9123,112,'',15,13,2),(9123,113,'',15,13,2);