nginx访问连接mysql数据库

最新推荐文章于 2024-10-20 18:12:02 发布

原创最新推荐文章于 2024-10-20 18:12:02 发布 · 6.9k 阅读

1 ·

CC 4.0 BY-SA版权

本文展示了如何在Nginx配置中利用Lua模块直接连接到MySQL数据库进行查询操作。通过设置数据库连接参数，如主机、端口、用户名、密码和数据库名，成功建立连接后执行SQL查询，并将结果编码为JSON返回。

location /test {
?????? content_by_lua '
?????????? 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(10000) -- 1 sec

?????????? local ok, err, errno, sqlstate = db:connect{
?????????????? host = "192.168.106.66",
?????????????? port = 3306,
?????????????? database = "greenskin",
?????????????? user = "cms",
?????????????? password = "greenskin",
?????????????? max_packet_size = 1024 * 1024 }

?????????? if not ok then
?????????????? ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
?????????????? return
?????????? end

?????????? ngx.say("connected to mysql.")

?????????? res, err, errno, sqlstate =
?????????????? db:query("select * from dingStat where id = " .. ngx.var.arg_id .. " order by id asc")
?????????? if not res then
?????????????? ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
?????????????? return
?????????? end

?????????? local cjson = require "cjson"
?????????? ngx.say("result: ", cjson.encode(res))

?????????? -- put it into the connection pool of size 100,
?????????? -- with 0 idle timeout
?????????? local ok, err = db:set_keepalive(0, 100)
?????????? if not ok then
?????????????? ngx.say("failed to set keepalive: ", err)
?????????????? return
?????????? end
?????? ';
?? }