OpenResty-Lua之连接MySQL数据库

连接MySQL

    local mysql = require "resty.mysql"
    local db, err = mysql:new()
    if not db then
        ngx.say("failed to instantiate mysql: ", err)
    return
    end
    db:set_timeout(1000)    --1 sec
    local ok, err, errno, sqlstate = db:connect{
        host = "127.0.0.1",
        port = 3306,
        database = "test",
        user = "root",
        password = "vagrant",
        max_packet_size = 1024*1024
    }
    if not ok then
        ngx.say("failed to connect: ",err, ":", errno, "  ", sqlstate)
        return
    end

    res, err, errno, sqlstate = db:query("create table cats"
    .. "(id serial primary key, "
    .."name varchar(5))")
   
    if not res then
        ngx.say("bad result: ", err, ": ", errno, ":", sqlstate, ".")
        return
    end

    ngx.say("table cats created.")
     
    res, err, errno, sqlstate = 
        db:query("insert into cats (name) "
        .. "values (\'Bob\'),(\'\'),(null)")
    if not res then
        ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
        return
    end

样例2：

local db, err = env:new()
    if not db then
        --错误处理
        ngx.say(err)
        return
    end
    --设置超时
    db:set_timeout(1000)
    --设置链接信息
    local ok, err ,errcode ,sqlstate = db:connect{
        database ="root",user = "root",password = "root",host = "127.0.0.1",port = 3306,charset = "utf8",max_packaget_size = 2048 * 2048
    }
    --数据库命令主体
    query = "select";
    --数据库操作返回
    local res, err ,errcode ,sqlstate = db:query(query)
    if not res then
        ngx.say(err)
        return
    end
    local ret = {}
    --数据库操作返回数据重构 
    for i,row in ipairs(res) do
    	for key,raw in pairs(row) do
    		ret[key] = raw
    	end
    end
    --json编码进入
    db:close()--关闭数据库连接

需要安装Resty.mysql的库，非Openresty用户需要额外安装，resty.mysql库同时依赖resty.string库

nginx配置文件部分：

location /mysql_hello {            
content_by_lua_file XX.lua;        
}