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

-- 演示将多条记录数据组合成一条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)
-- 演示将多条记录数据组合成一条sql插入语句(for mysql)

functi