【1】Mysql Lua语言拼接实例
（1）基本语句
local sql = "select * from task_control where cycle_id = '" .. args["cycleid"] .. "' and state = 6;"
local sql = "update task_control set state = 7 where cycle_id = '" .. args["cycleid"] .. "';"
local sql = "select sum(if(cycle_low > 0, if(cycle_low > lastbalance, lastbalance, cycle_low), lastbalance)) 'gift' from cfg_acct_free_res " ..
" where product_id = '" .. args["productid"] .. "' and call_type & " .. args["calltype"] ..
" > 0 and disable_time > sysdate() and enable_time < sysdate();"local sql = "select lastbalance 'cash' from cfg_subscriber_prepay " .. " where product_id = '" .. args["productid"] .. "' and " .. " disable_time > '" .. cycleid_str .. "01' and enable_time < '" .. getNextcyceid(cycleid_str) .. "01' " .. " order by id DESC LIMIT 1;"
local sql = "select fee_rate 'fee_rate' from cfg_fee_rate c where c.product_id = '" .. args["productid"] .."' and call_type & " .. args["calltype"] .. " > 0 and disable_date > sysdate() and enable_date < sysdate() order by level ASC limit 1;"
local sql = 'call sdrrating(\'' .. args["account"] .. '\', \'' .. args["cycleid"] .. '\');'
local sql = "select id, product_id, start_time, end_time, call_type from bill_user_special where " ..
" cycle_id = '" .. cycleid_str .. "' and (isnull(process_cycleid) or process_cycleid <> '" .. cycleid_str .. "') " ..
" order by product_id;"local sql = "update view_dat_sdr_" .. cycleid_str .. " set service_qty = floor((duration + fee_unit - 1) / fee_unit), " ..
" service_fee = floor((duration + fee_unit - 1) / fee_unit) * fee_rate " ..
" where product_id = '" .. user["product_id"] .. "' and call_type = 1;"（2）replace into 语句
local sql = "replace into dat_bill_" .. args["cycleid"] .. "(" ..
"custom_id," .. -- 2
"contract_id," .. -- 3
"product_id," .. -- 4
"call_type," .. -- 5
-- 总费用
"total_fee," .. -- 6
"total_duration," .. -- 7
"total_times," .. -- 8
-- 结算费用
-- 过程控制*/
") select '" ..
args["cycleid"] .. "'," .. -- 1
"''," .. -- 2
"''," .. -- 3
"product_id," .. -- 4
"call_type," .. -- 5
-- 总费用
"sum(service_fee)," .. -- 6
"sum(duration)," .. -- 7
"0," .. -- 9
"0," .. -- 10
-- 结算费用
"source," .. -- 11
"sum(duration)," .. -- 12
"count(*)," .. -- 13
"sum(np_fee)," .. -- 14
-- 过程控制
"'00'," .. -- 15
" from view_dat_sdr_" .. args["cycleid"] .. " where product_id = '" .. args["account"] .. "' group by call_type;"（3）TRUNCATE TABLE 语句
local sql = "TRUNCATE TABLE cfg_subscriber_prepay;" .. " INSERT INTO cfg_subscriber_prepay SELECT * FROM cfg_subscriber_prepay_" .. args["cycleid"] .. ";" .. " DROP TABLE cfg_subscriber_prepay_" .. args["cycleid"] .. "; commit;"
local undoSql = "START TRANSACTION;" ..
" REPLACE INTO cfg_subscriber_prepay " ..
" SELECT * FROM cfg_subscriber_prepay_" .. args["cycleid"] .. " WHERE product_id = '" .. args["productid"] .. "';" ..
" DELETE FROM cfg_subscriber_prepay_" .. args["cycleid"] .. " WHERE product_id = '" .. args["productid"] .. "';" ..
" REPLACE INTO cfg_acct_free_res " ..
" SELECT * FROM acct_free_res_" .. args["cycleid"] .. " WHERE product_id = '" .. args["productid"] .. "';" ..
" DELETE FROM acct_free_res_" .. args["cycleid"] .. " WHERE product_id = '" .. args["productid"] .. "';" ..
" DELETE FROM cfg_prepay_history WHERE product_id = '" .. args["productid"] .. "' AND cycle_id = '" .. args["cycleid"] .. "';" ..
" DELETE FROM dat_bill_" .. args["cycleid"] ..
" WHERE cycle_id = '" .. args["cycleid"] .. "' AND product_id = '" .. args["productid"] .. "' AND call_type = '1048576';" ..
" COMMIT;"（4）SUM
local np_sum_fee = "SELECT product_id, IFNULL(SUM(duration), 0) AS np_sum_duration, COUNT(id) AS np_sum_cdrCnt, " .. " IFNULL(SUM(service_qty), 0) AS np_service_qty, IFNULL(SUM(service_fee), 0) AS np_service_fee, " .. " IFNULL(SUM(np_fee), 0) AS np_sum_fee, IFNULL(SUM(original_fee), 0) AS np_sum_original_fee " .. " FROM np_cdr_" .. cycleid .. " WHERE product_id = '" .. record["product_id"] .. "';"
（5）整体过程
local select_productid = "select product_id from np_cdr_" .. cycleid .. " group by product_id;"
local rtn, productid_set = executeSql(select_productid)
if rtn then
local sql_const = "insert into compare_fee_" .. cycleid .. " (product_id, np_id, np_sum_duration, np_total_times, " ..
"np_sum_qty, np_sum_fee, un_sum_duration, un_total_times, un_sum_fee, sum_np_fee, sum_original_fee) values "
if type(productid_set) == 'table' and next(productid_set) ~= nil then
for key, record in pairs(productid_set) do
local amount_table = {}
amount_table["product_id"] = record["product_id"]
amount_table["np_id"] = 'all'
-- 【1】统计
local np_sum_fee = "SELECT product_id, IFNULL(SUM(duration), 0) AS np_sum_duration, COUNT(id) AS np_sum_cdrCnt, " ..
" IFNULL(SUM(service_qty), 0) AS np_service_qty, IFNULL(SUM(service_fee), 0) AS np_service_fee, " ..
" IFNULL(SUM(np_fee), 0) AS np_sum_fee, IFNULL(SUM(original_fee), 0) AS np_sum_original_fee " ..
" FROM np_cdr_" .. cycleid .. " WHERE product_id = '" .. record["product_id"] .. "';"
local rtn, desc = executeSql(np_sum_fee)
if rtn and next(desc) ~= nil then
amount_table[]["np_sum_duration"]
amount_table[]["np_sum_cdrCnt"]
amount_table[]["np_service_qty"]
amount_table[]["np_service_fee"]
amount_table[]["np_sum_fee"]
amount_table[]["np_sum_original_fee"]
end
-- 【2】汇总
local un_sum_fee = "SELECT product_id, IFNULL(SUM(total_duration), 0) AS un_sum_duration, IFNULL(SUM(total_times), 0) AS un_sum_cdrCnt, " ..
" IFNULL(SUM(total_fee), 0) AS un_sum_fee FROM dat_bill_" .. cycleid .. " WHERE call_type & 4097 > 0 AND product_id = '" .. record["product_id"] .. "';"
local rtn, desc = executeSql(un_sum_fee)
if rtn and next(desc) ~= nil then
amount_table[]["un_sum_duration"]
amount_table[]["un_sum_cdrCnt"]
amount_table[]["un_sum_fee"]
end
-- 构建sql语句
then
insert_sql = insert_sql .. ","
else
insert_sql = sql_const
end
insert_sql = insert_sql .. "('" ..
amount_table["product_id"] .. "', '" ..
amount_table["np_id"] .. "', " ..
() .. ", " ..
() .. ", " ..
() .. ", " ..
() .. ", " ..
() .. ", " ..
().. ", " ..
() .. ", " ..
() .. ", " ..
() .. ")"
value_count = value_count +
== value_count then
sum_count = sum_count + value_count
insert_sql = insert_sql .. "; commit;"
local rtn, result = executeSql(insert_sql)
if not rtn then
rtnstr[
rtnstr["msg"] = "Error. amount_sum_fee insert into sum_fee num : " .. sum_count .. " sql : " .. insert_sql
return false
else
local result = "compare: success to amount_sum_fee insert into sum_fee num : " .. sum_count
ngx.log(ngx.ERR, result)
end
insert_sql = ""
value_count =
end
end
then
sum_count = sum_count + value_count
insert_sql = insert_sql .. "; commit;"
local rtn, result = executeSql(insert_sql)
if not rtn then
rtnstr[
rtnstr["msg"] = "Error. amount_sum_fee insert into sum_fee num : " .. sum_count .. " sql : " .. insert_sql
return false
else
local result = "compare: success to amount_sum_fee insert into sum_fee num : " .. sum_count
ngx.log(ngx.ERR, result)
end
insert_sql = ""
value_count =
end
end（6）常量sql 拼接其他sql语句
local insert_const_sql = "insert into dat_bill_" .. cycleid_str .. "(" ..
" custom_id," .. -- 2
" contract_id," .. -- 3
" product_id," .. -- 4
" call_type," .. -- 5
-- [1]
" total_fee," .. -- 6
" total_duration," .. -- 7
" total_times," .. -- 8
-- [2]
" np_id," .. -- 11
" np_duration," .. -- 12
" np_times," .. -- 13
" np_fee," .. -- 14
-- [3]
local select_sql = "select " ..
"product_id, " .. -- 4
"call_type, " .. -- 5
-- [1]
"IFNULL(sum(service_fee), 0) as un_total_fee, " .. -- 6
"IFNULL(sum(duration), 0) as total_duration, " .. -- 7
"count(*) as total_times, " .. -- 8
-- [2]
"source, " .. -- 11
"IFNULL(sum(np_fee), 0) as np_total_fee, " .. -- 14 integer NULL,
-- [3]
" from view_dat_sdr_" .. cycleid_str .. " where product_id = '" .. productid .. "' and call_type & " .. calltype .. " > 0 " ..
" and (reserve_00 = '0' OR reserve_00 = '2') group by call_type;"
local rtn, desc = executeSql(select_sql, cfg.is_master_bill)
if not rtn then
rtnstr[
rtnstr["msg"] = "failed to genBill select data cycleid : " .. cycleid_str .. " productid : " .. productid .. " error : " .. desc
ngx.log(ngx.ERR, rtnstr["msg"])
else
if type(desc) == "table" and next(desc) ~= nil then
local insert_sql = ""
for key, value in ipairs(desc) do
insert_sql = insert_sql .. insert_const_sql
insert_sql = insert_sql .. "('" .. cycleid_str .. "', '', '', '" .. productid .. "', " .. value["call_type"] .. ", " ..
value["un_total_fee"] .. ", " .. value["total_duration"] .. ", " .. value["total_times"] .. ", 0, 0, '" ..
value["source"] .. "', " .. value["total_duration"] .. ", " .. value["total_times"] .. ", " .. value["np_total_fee"] .. ", '00', '" ..
value["current"] .. "', '" .. value["current"] .. "')" ..
" ON DUPLICATE KEY UPDATE" ..
" total_fee = " .. value["un_total_fee"] .. ", total_duration = " .. value["total_duration"] .. "," ..
" total_times = " .. value["total_times"] .. ", np_duration = " .. value["total_duration"] .. "," ..
" np_times = " .. value["total_times"] .. ", np_fee = " .. value["np_total_fee"] .. "," ..
" insert_time = '" .. value["current"] .. "', update_time = '" .. value["current"] .. "'; "
end
--ngx.log(ngx.ERR, "genBill insert into dat_bill_" .. cycleid_str .. " insert_sql : " .. insert_sql)
local rtn, err = executeSql(insert_sql)
if not rtn then
rtnstr[
rtnstr["msg"] = "Error. failed to genBill insert into dat_bill_" .. cycleid_str .. " productid : " .. productid ..
" call_type : " .. value["call_type"] .. " error : " .. err
ngx.log(ngx.ERR, rtnstr["msg"])
end
else
rtnstr[
rtnstr["msg"] = "OK. genBill bill productid : " .. productid .. " cycleid : " .. cycleid_str .. " is empty"
ngx.log(ngx.ERR, rtnstr["msg"])
end
end（7）命令LOAD DATA INFILE
lcoal sql = "LOAD DATA INFILE '" .. csvdir .. string.gsub(offlinefile["filename"], ".zip", ".csv") .. "' " .. "INTO TABLE np_cdr_" .. cycleid_str .." FIELDS TERMINATED BY ',' IGNORE 1 LINES (product_id,calling_id,start_time,duration,call_type,@fee) " .. "set original_fee = @fee * 1000, insert_time = sysdate(), " .. "np_id = '" .. offlinefile["np_id"] .. "'," .. "cdr_state = '00', " .. "source = '" .. offlinefile["filename"] .. "';"
（8）IFNULL 和 FLOOR函数
local sql = "select call_type, ani_area, dnis_area, fee_rate, fee_unit, fee_type, transfer_area, " ..
" ifnull(np_id,'none') 'np_id', " ..
" ifnull(to_days(enable_date) - TO_DAYS('19700101') - 1, 0) 'startdate', " ..
" ifnull(to_days(disable_date) - TO_DAYS('19700101') + 1, 0) 'enddate'" ..
" From cfg_fee_rate where product_id = '" ..
productid .. "' and (fee_type = 'voice' or fee_type = 'sms') order by level DESC, enable_date DESC;"local update_sql = "update np_cdr_" .. cycleid_str .. " set np_unit = IF(call_type = '长话', " .. long_rate["fee_unit"] .. ", " .. short_rate["fee_unit"] .. "), " .. " np_rate = IF(call_type = '长话', " .. long_rate["fee_rate"] .. ", " .. short_rate["fee_rate"] .. "), ".. " np_qty = FLOOR((duration + np_unit - 1) / np_unit), " .. " np_fee = FLOOR((duration + np_unit - 1) / np_unit) * np_rate " .. " where np_id = '" .. offlinefile["np_id"] .. "' and source = '" .. offlinefile["filename"] .. "';"
（9）distinct关键字
local sql = "select distinct a.id, a.product_id, a.realbalance, a.balance " ..
" from cfg_subscriber_prepay a " ..
" where a.status = '01' and a.disable_time > '" .. lastyearmonth .. "01' and " ..
" a.enable_time < '" .. (getNextcyceid(lastyearmonth)) .. "01' order by a.product_id, a.id;"（10）常量值语句
local sqlstr = "replace into dat_bill_" .. cycleid_str .. "(cycle_id, custom_id, contract_id, product_id, call_type, total_fee," ..
" total_duration, total_times, discount_fee, cash_fee, np_id, np_duration, np_times, np_fee," ..
" bill_state, insert_time, update_time) select '" .. cycleid_str ..
"' , '+', '+', product_id, call_type, fee_rate" ..
", 1, 1, 0" ..
", 0, '0', 1, 1, 0, '0', sysdate(), sysdate() from cfg_fee_rate where call_type & ".. string.format("%d", cfg.globalcalltypecode['service']) .. " > 0 and disable_date >= '" ..
getNextcyceid(cycleid_str) .. "01' and enable_date < '" .. getNextcyceid(cycleid_str) .. "01' order by product_id;" （11）ON DUPLICATE KEY UPDATE 语句
local sqlstr = "update cfg_acct_free_res set " .. computebalance .. " = " ..
string.format("%d", fr_record[refbalance] - fr_record["cycle_low"]) ..
" where id = " .. fr_record["id"] .. ";"
sqlstr = sqlstr .. "insert into dat_bill_" .. cycleid_str .. "(cycle_id, custom_id, contract_id, product_id, call_type, total_fee," ..
"total_duration, total_times, discount_fee, cash_fee, np_id, np_duration, np_times, np_fee," ..
"bill_state, insert_time, update_time) values('" .. -- values
cycleid_str ..
"', '+', '+', '" .. freeresproduct .."', 131072," ..
string.format("%d", fr_record["cycle_low"] - fr_record["resumesum"]) ..
", 1, 1," ..
string.format("%d", fr_record["cycle_low"] - fr_record["resumesum"]) ..
", 0, '0', 1, 1, 0, '0', sysdate(), sysdate()) on duplicate key update " .. -- on duplicate key update
"total_fee = total_fee + " .. string.format("%d", fr_record["cycle_low"] - fr_record["resumesum"]) .."," ..
"discount_fee = discount_fee + " .. string.format("%d", fr_record["cycle_low"] - fr_record["resumesum"]) .."," ..
"total_times = total_times + 1;"Good Good Study, Day Day Up.
顺序 选择 循环 总结