openresty使用lua操作mysql

--1) 定义数据库连接属性
local props = {
    host = "127.0.0.1",
    port = 3306,
    database = 'xukang-dev',
    user = 'root',
    password = 'root',
    charset = 'utf8mb4',
    max_packet_size = 1024 * 1024
}

-- 2) 自定义close函数
local function close_db(db)  
    if not db then  
        return  
    end
      
    --  连接池机制：不调用close,选择keepalive 确保性能
    local pool_max_idle_time = 10000 --毫秒  
    local pool_size = 100 --连接池大小  
    local ok, err = db:set_keepalive(pool_max_idle_time, pool_size)  
    if not ok then  
        -- ngx.say("set keepalive error : ", err)  
        ngx.log(ngx.ERR, "set keepalive error : ", err)
    end 
end
 
-- 3) 引入mysql实例
local mysql = require("resty.mysql")
 
-- 4) 创建实例
local db, err = mysql:new()
if not db then
    -- ngx.say("new mysql error : ", err)
    ngx.log(ngx.ERR, "new mysql error : ", err)
    return
end
 
--4) 设置超时时间
db:set_timeout(10000)
 
-- 6) 建立连接
local res,err,errno,sqlstate = db:connect(props)
-- 备注：异常判断,异常消息统一处理
if not res then
    -- ngx.say("connect to mysql error:", err,", errno:", errno,", sqlstate:", sqlstate)
    ngx.log(ngx.ERR, "connect to mysql error:", err,", errno:", errno,", sqlstate:", sqlstate)
    return close_db(db)
end
 
-- 7) sql 语句操作 -->过程省略

local select_sql = "select id, username, salt, phone from sys_user"
res, err, errno, sqlstate = db:query(select_sql)
if not res then
    ngx.say("select table error :", err,", errno", errno, ", sqlstate : ",sqlstate)
    return close_db(db)
end

-- 多条'结果集'的处理
for i, row in ipairs(res) do
    -- ngx.say(row.username, row.phone)
    for key,value in pairs(row) do
        ngx.say("select row", i," : ", key, " = ", value)
    end
    ngx.say("<br/>===================")
end

-- 8) 关闭连接 -->由于使用连接池,不需要每次都使用密码
-- close_db(db)