[大数据]skynet lua操作 mysql 带有返回参数的存储过程

skynet/test/testmysql.lua源码测试用例里面没有带操作mysql存储过程返回参数的例子，不知道如何处理更好，这个问题我今天研究了大半天😂，下载了mysql-server源代码https://github.com/mysql/mysql-server，看了libmysql实现，以及存储过程相关的操作，结合skynet 本身的sql的支持实现，做了测试，需要再次读取数据才能获取存储过程返回参数的值，因为之前我做游戏 全部都是采用存储过程来操作数据库，这样方便修改db操作相关的逻辑，比如封号 我不需要再程序里面写上相应的代码逻辑，用户登录直接数据返回值通知即可，大大简化逻辑，如果存储过程有返回参数获取 那么就可以很好的做个db引擎了，db操作全部脚本配置即可

不多说先上代码 修改 代码测试使用skynet/test/testmysql.lua 对应的文件为

https://github.com/cloudwu/skynet/blob/master/test/testmysql.lua

请自己去查阅

带有返回参数的 sql execute需要执行两次才行

带有out返回参数的存储过程代码：

CREATE PROCEDURE `get_test`(IN p_id int, out s int)
		BEGIN  
            set s = 10;
			select str,dt,flt,blb,num from test where id=p_id;
		END

lua代码实现：

local skynet = require "skynet"
local mysql = require "skynet.db.mysql"

local function dump(obj)
    local getIndent, quoteStr, wrapKey, wrapVal, dumpObj
    getIndent = function(level)
        return string.rep("\t", level)
    end
    quoteStr = function(str)
        return '"' .. string.gsub(str, '"', '\\"') .. '"'
    end
    wrapKey = function(val)
        if type(val) == "number" then
            return "[" .. val .. "]"
        elseif type(val) == "string" then
            return "[" .. quoteStr(val) .. "]"
        else
            return "[" .. tostring(val) .. "]"
        end
    end
    wrapVal = function(val, level)
        if type(val) == "table" then
            return dumpObj(val, level)
        elseif type(val) == "number" then
            return val
        elseif type(val) == "string" then
            return quoteStr(val)
        else
            return tostring(val)
        end
    end
    dumpObj = function(obj, level)
        if type(obj) ~= "table" then
            return wrapVal(obj)
        end
        level = level + 1
        local tokens = {}
        tokens[#tokens + 1] = "{"
        for k, v in pairs(obj) do
            tokens[#tokens + 1] = getIndent(level) .. wrapKey(k) .. " = " .. wrapVal(v, level) .. ","
        end
        tokens[#tokens + 1] = getIndent(level - 1) .. "}"
        return table.concat(tokens, "\n")
    end
    return dumpObj(obj, 0)
end
 
-- 测试存储过程和blob读写
local function test_sp_blob(db)
	print("test stored procedure")
	-- 创建测试表
	db:query "DROP TABLE IF EXISTS `test`"
	db:query [[
		CREATE TABLE `test` (
			`id` int(11) NOT NULL AUTO_INCREMENT,
			`str` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL,
			`dt` timestamp NULL DEFAULT NULL,
			`flt` double DEFAULT NULL,
			`blb` mediumblob,
			`num` int(11) DEFAULT NULL,
			PRIMARY KEY (`id`),
			UNIQUE KEY `id_UNIQUE` (`id`)
			) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
	]]
	-- 创建测试存储过程
    -- 输出参数为s，返回10 Mysql不支持类似mssql存储过程那种直接有返回值的 return 1 这种
	db:query "DROP PROCEDURE IF EXISTS `get_test`"
	db:query [[
		CREATE PROCEDURE `get_test`(IN p_id int, out s int)
		BEGIN  
            set s = 10;
			select str,dt,flt,blb,num from test where id=p_id;
		END
	]]

    -- 如果直接用sql语句操作数据库 建议这种 没有sql注入
    local stmt_insert = db:prepare("INSERT test (str,dt,flt,num,blb) VALUES (?,?,?,?,?)")
 
	local test_blob = string.char(0xFF,0x8F,0x03,0x04,0x0a,0x0b,0x0d,0x0e,0x10,0x20,0x30,0x40)

	local r = db:execute(stmt_insert,'test_str','2020-3-20 15:30:40',3.1415,89,test_blob)
	print("insert result : insert_id",r.insert_id,"affected_rows",r.affected_rows
		,"server_status",r.server_status,"warning_count",r.warning_count)
  
    --注意这里！！ 两个参数 第2个 不用？ 我是使用的@a作为输出
    local stmt_csp = db:prepare("call get_test(?,@a)")
    local stmt_csp1= db:prepare("select @a")
    r = db:execute(stmt_csp,1)
    print('------------------------')
    print(dump(r))
    print('------------------------')
    local r1 = db:execute(stmt_csp1)
    print(dump(r1))
    -- local stmt_csp = db:prepare("call get_test(?,?)")
    -- r = db:execute(stmt_csp,1,s)
    print('------------------------')
	local rs = r[1][1]
	print("call get_test() result : str",rs.str,"dt",rs.dt,"flt",rs.flt,"num",rs.num
		,"blb len",#rs.blb,"equal",test_blob==rs.blb)

	print("test stored procedure ok")
end
 
skynet.start(function()

	local function on_connect(db)
		db:query("set charset utf8mb4");
	end
	local db=mysql.connect({
		host="192.168.1.10",
		port=3306,
		database="skynet",
		user="root",
		password="love5259",
                charset="utf8",
		max_packet_size = 1024 * 1024,
		on_connect = on_connect
	})
	if not db then
		print("failed to connect")
	end
	print("testmysql success to connect to mysql server")

 
	-- 测试存储过程和二进制blob
	test_sp_blob(db)
	
	 
end)

我把返回结果通过?dump(obj)函数进行输出， 输出结果如下：

testmysql success to connect to mysql server
test stored procedure
insert result : insert_id       1       affected_rows   1       server_status   2       warning_count   0
------------------------
{
        [1] = {
                [1] = {
                        ["num"] = 89,
                        ["flt"] = 3.1415,
                        ["str"] = "test_str",
                        ["blb"] = "��

 0@",
                        ["dt"] = "2020-03-20 15:30:40",
                },
        },
        [2] = {
                ["warning_count"] = 0,
                ["affected_rows"] = 0,
                ["server_status"] = 2,
                ["insert_id"] = 0,
        },
        ["mulitresultset"] = true,
}
------------------------
{
        [1] = {
                ["@a"] = 10,
        },
}
------------------------
call get_test() result : str    test_str        dt      2020-03-20 15:30:40     flt     3.1415  num     89      blb len 12      equal   true
test stored procedure ok