local mysql_pool = {}
function mysql_pool:get_connect()
ngx.say(ngx.ctx[mysql_pool])
if ngx.ctx[mysql_pool] then
return true, ngx.ctx[mysql_pool]
end
local mysql = require "resty.mysql"
local client, errmsg = mysql.new()
if not client then
return false, "mysql.socket_failed: " .. (errmsg or "nil")
end
--设置超时时间
client:set_timeout(1000)
local props = {
host = "192.168.1.111",
port = 3306,
database = "visitor",
user = "username",
password = "password",
max_packet_size = 1024 * 1024
}
local res, err, errno, sqlstate = client:connect(props)
if not res then
return false, "mysql.query_failed: "..(errmsg or "nil")..", errno:"..(errno or "nil")..", sql_state:"..(sqlstate or "nil")
end
local query = "SET NAMES UTF8"
local result, errmsg, errno, sqlstate = client:query(query)
if not result then
return false, "mysql.query_failed: "..(errmsg or "nil")..", errno:"..(errno or "nil")..", sql_state:"..(sqlstate or "nil")
end
ngx.ctx[mysql_pool] = client
return true, ngx.ctx[mysql_pool]
end
--把连接返回到连接池
--用set_keepalive代替close() 将开启连接池特性 (1分钟timout，连接池100个)
function mysql_pool:close()
if ngx.ctx[mysql_pool] then
local ok, err = ngx.ctx[mysql_pool]:set_keepalive(60000, 100)
if not ok then
ngx.say("failed to set keepalive:", err)
end
ngx.ctx[mysql_pool] = nil
end
end
function mysql_pool:query(sql, flag)
local ret, client = self:get_connect(flag)
if not ret then
return false, client, nil
end
local result, errmsg, errno, sqlstate = client:query(sql)
self:close()
if not result then
errmsg = concat_db_errmsg("mysql.query_failed:", errno, errmsg, sqlstate)
return false, errmsg, sqlstate
end
return true, result, sqlstate
end
return mysql_pool
