lua调用mysql存储过程_skynet 创建存储过程脚本

最近主程更改了数据库的操作方案，由之前的拼写sql脚本转为在mysql端创建好存储过程后，直接调用存储过程。

首先对一个表测试上述过程:

数据库端存储过程：(测试表)

CREATE TABLE `randomval` (

`id` int(10) unsigned NOT NULL，

`val` int(10) unsigned NOT NULL,

`step` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

创建存储过程：

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `qy_insert_randomval`(IN in_id int, IN in_val int, IN in_step int)

BEGIN

insert into randomval (`id`, `val`, `step`) values (in_id, in_val, in_step)

on duplicate key update `id` = in_id, `val` = in_val, `step` = in_step;

END $$

DELIMITER；

skynet的连接：更改的test下的testmysql.lua

skynet.start(function()

local function on_connect(db)

db:query("set charset utf8");

end

local db=mysql.connect({

host="127.0.0.1"或"局域网ip",

port=3306,

database="XXXX",

user="XXXX",

password="XXXX",

max_packet_size = 1024 * 1024,

on_connect = on_connect

})

if not db then

print("failed to connect")

end

--拼接调用存储过程的sql语句

print("testmysql success to connect to mysql server")

--表t的格式忽略，和项目有关，可自定义

local t = {__fields = {id = 34, val = 30, step = 50}, __tname = "randomval", __head_ord = {"id", "val", "step"} }

local tmp_sql = {}

local sql_first_part = string.format("call " .. "qy_insert_" .. t.__tname .. "(" )

table.insert(tmp_sql, sql_fir