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_first_part)

       assert(nil ~= t.__head_ord)
       local counter = 0
       for k, v in ipairs(t.__head_ord) do
           print(k, v)
           assert(nil ~= t.__fields[v])
           if counter > 0 then
               table.insert(tmp_sql, ", ")
           else
               counter = counter + 1
           end

           if type(t.__fields[v]) == "string" then
               table.insert(tmp_sql, string.format("'%s'",t.__fields[v] ))
           else
               table.insert(tmp_sql, string.format("%s", t.__fields[v]))
           end
       end

       table.insert(tmp_sql, ")")
       print("sql is :", table.concat(tmp_sql))
       db:query(table.concat(tmp_sql))

--db:disconnect()
--skynet.exit()
end) 　　　　　　　　　　　　　　

这时表，存储过程都创建好了，运行testmysql脚本: ./skynet ./examples/config.mysql

成功调用存储过程并插入一条记录.

由于主程要求每个表都要有一个“插入不成功就更新的存储过程”，虽说用了navicat，但是目前对它的了解，只能一个一个的创建，很麻烦；

要不就是执行一个创建所有过程的sql脚本，用navicat执行创建。于是选择了后者。

首先提取共性，需要以下：

（1）统一命名为"qy_insert_" + "表名"的格式

（2）获取需要创建存储过程的表名,表的每个字段名，字段类型（TABLE_TYPE）和字段的值类型（DATA_TYPE）

以下是实现：

　 local addr = io.open("./user_sqlscript.sql", "w")
assert(addr)
local sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = " .. '"' .. "数据库名称" .. '"' //到这里选出的是数据库中所有的表。加上后面是筛选需要的表 .. "and TABLE_NAME NOT like" .. "'" .. "g_%" .. "'";

　 print(sql)
local table_list = db:query(sql)
--[[

　　DELIMITER $$

　　DELIMITER；

　　以此存储过程为例

--]]
    for k, v in ipairs(table_list) do
        local script = {}
        local tmpsql1 = {} -- 存储 `qy_insert_randomval`(IN in_id int, IN in_val int, IN in_step int)中的 “IN in_id int, IN in_val int, IN in_step int”
        local tmpsql2 = {} --存储 insert into randomval (`id`, `val`, `step`) 中的 “`id`, `val`, `step`”
        local tmpsql3 = {} --存储 values (in_id, in_val, in_step) 中的 “in_id, in_val, in_step”
        local tmpsql4 = {} --存储 on duplicate key update `id` = in_id, `val` = in_val, `step` = in_step; 中的 “`id` = in_id, `val` = in_val, `step` = in_step”

        local sql = string.format("select COLUMN_NAME , DATA_TYPE , COLUMN_TYPE from information_schema.`COLUMNS` where table_name = " .. '"' .. "%s" .. '";', v.TABLE_NAME)

　　 --COLUMN_NAME 是列名, COLUMN_TYPE 是列的类型如（ｖａｒｃｈａｒ，ｉｎｔ（６）ｕｎｓｉｇｎｅｄ）等，ＤＡＴＡ＿ＴＹＰＥ是该列的值类型（ｉｎｔ，　ｖａｒｃｈａｒ）等
        print(sql)
        local col_val = db:query(sql)
        assert(col_val)
        for k, v in ipairs(col_val) do
            for sk, sv in pairs(v) do
                if sv == "int" then
                    print("string is ")
                end
            end
        end

        --format each percudure part
        local idx = 0
        for sk, sv in ipairs(col_val) do
            if sv.DATA_TYPE == "varchar" then
                table.insert(tmpsql1, string.format("IN %s %s", "in_" .. sv.COLUMN_NAME, sv.COLUMN_TYPE))
            else
                table.insert(tmpsql1, string.format("IN %s %s", "in_" .. sv.COLUMN_NAME, sv.DATA_TYPE))
            end

            table.insert(tmpsql2, string.format("`%s`", sv.COLUMN_NAME))
            table.insert(tmpsql3, string.format("in_%s", sv.COLUMN_NAME))
            table.insert(tmpsql4, string.format("`%s` = in_%s", sv.COLUMN_NAME, sv.COLUMN_NAME))
        end

        --chain all tmpsqlx up
        table.insert(script, "DELIMITER $$ ")
        table.insert(script, string.format("CREATE DEFINER=`root`@`%%` PROCEDURE `%s`(", "qy_insert_" .. v.TABLE_NAME))
        table.insert(script, table.concat(tmpsql1， “，”) .. ") ")
        table.insert(script, "BEGIN ")
        table.insert(script, string.format("insert into %s (%s values (%s)", v.TABLE_NAME, table.concat(tmpsql2, ","), table.concat(tmpsql3, ",") .. ") "))
        table.insert(script, string.format("on duplicate key update %s ; ", table.concat(tmpsql4, ",")))
        table.insert(script, "END$$ ")
        table.insert(script, "DELIMITER ;")
        local percudure = table.concat(script)
        print(percudure)

        addr:write(percudure)
        addr:write(" ")
    end
    addr:close()　－－打开文件别忘了关闭

以上代码实现了一个从数据库中查询出所有的表及其列名，列的类型，然后拼装成每个表需要的存储过程。

前面说到用到的工具是ｎａｖｉｃａｔｅ当用工具创建存储过程时：ｆｕｎｃｔｉｏｎ－＞ｐｒｏｃｅｄｕｒｅ－＞输入参数－＞　直接在ＢＥＧＩＮ，ＥＮＤ中间写过程。

而且用此方法创建时，得到的存储过程代码是没有下面的内容的

DELIMITER

．．．

END $$

DELIMITER；

因此，一开始没有意识到此问题，我直接将生成的ｓｑｌ文件用ｎａｖｉｃａｔｅ生成存储过程时是有问题的，总是说存储过程有问题，但是对比用ｎａｖｉｃａｔ直接生成的代码，又是一模一样的。因此很郁闷。

网上搜资料找到要加上上面的内容，然后生成成功．

原因在这篇文章中叙述的挺详细的：

http://blog.sina.com.cn/s/blog_4c197d420101d3oa.html

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－分割线－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

ｓｋｙｎｅｔ中庸存储过程查询数据库并接受返回值时可以这样使用：

ｄｂ：ｑｕｅｒｙ（“call　存储过程名（＠ａ，＠ｂ，　＠ｃ）”）

local　ｒ　＝　ｄｂ：ｑｕｅｒｙ（＂ＳＥＬＥＣＴ　＠ａ，　＠ｂ，　＠ｃ＂）

注：传出变量为＠ａ，＠ｂ，＠ｃ　，切＠ａ之间没有空格

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

由于数据库基础不是很好，还没找到好的方法实现调用存储过程返回数据集的操作，望各位赐教！

以后补充．