openresty使用lua操作mysql

Ruthless發表於2013-06-21
RESTMySql
--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)

 

相關文章