在 Nginx 配置文件里使用 Lua 操作 MySQL 需要使用 lua-resty-mysql 模块 : https://github.com/openresty/lua-resty-mysql
在 Nginx 配置文件中引入 lua-resty-mysql
# nginx.conf http { lua_package_path "/path/to/lua-resty-mysql/lib/?.lua;;"; ... }lua-resty-mysq API : https://github.com/openresty/lua-resty-mysql#methods
操作数据库 Demo
# 查询 location /mysql-query { default_type "text/plain"; content_by_lua \' local resty_mysql = require("resty.mysql") local mysql, str_err = resty_mysql:new() if not mysql then ngx.say("new -- failed to new a mysql. -- str_err: ", str_err) return end mysql:set_timeout(1000) -- 1 sec local conn, str_err = mysql:connect({ host = "10.253.1.251", port = "3306", database = "peanutcar", user = "caikr", password = "caikr" -- , -- compact_arrays = true -- when this option is set to true, -- then the query and read_result methods will return the array-of-arrays structure for the resultset, -- rather than the default array-of-hashes structure. }) if not conn then ngx.say("connect -- failed to connect to remote mysql server. -- str_err: ", str_err) return end ngx.say("connect success! connect: ", conn) -- set utf-8 mysql:query("SET NAMES utf8") local str_sql_for_query = "select * from car_store limit 20" local byte_data, str_err = mysql:send_query(str_sql_for_query) if not byte_data then ngx.say("send_query -- failed to send query. -- str_err: ", str_err) return end ngx.say("send_query success! byte_data: ", byte_data) local obj_res, str_err, n_errcode, n_sqlstate = mysql:read_result() -- local obj_res, str_err, n_errcode, n_sqlstate = mysql:read_result(6) -- The optional argument nrows can be used to specify an approximate number of rows for the result set. -- This value can be used to pre-allocate space in the resulting Lua table for the result set. -- By default, it takes the value 4. if not obj_res then ngx.say("read_result -- failed to read result. -- str_err: ", str_err, " -- n_errcode", n_errcode, " -- n_sqlstate", n_sqlstate) return end ngx.say("read_result success. type(obj_res): ", type(obj_res)) -- ngx.say("read result success. obj_res: ", obj_res) -- for i = 1, #obj_res do -- ngx.say("i = ", i, " -- obj_res[", i , "] = ", obj_res[i]) -- end -- for k, v in pairs(obj_res) do -- ngx.say("key = ", k) -- for x, y in pairs(v) do -- ngx.say("x : ", x, " -- y : ", y) -- end -- end -- 用 lua-cjson 处理结果集 local cjson = require("cjson") local cj_res = cjson.encode(obj_res) ngx.say("结果集 cj_res: ", cj_res) -- put it into the connection pool of size 100, with 10 seconds max idle timeout local ok, err = mysql:set_keepalive(10000, 100) if not ok then ngx.say("set_keepalive -- failed to set keepalive: ", err) return end -- or just close the connection right away: -- local ok, err = db:close() -- if not ok then -- ngx.say("failed to close", err) -- return -- end '; } # 插入数据 location /mysql-insert { default_type "text/plain"; content_by_lua ' local resty_mysql = require("resty.mysql") local db = resty_mysql:new() -- hello local conn = db:connect({ host = "10.253.1.251", port = 3306, database = "peanutcar", user = "caikr", password = "caikr" }) ngx.say("connect success! conn : ", conn) local str_sql_for_insert = "insert into cats (name) values (\'再\')" -- 设置字符集为 UTF-8 -- db:query("SET NAMES utf8") local obj_res, str_err, n_errcode, n_sqlstate = db:query(str_sql_for_insert) if not obj_res then ngx.say("insert ---- failed to insert. str_err: ", str_err, " -- n_errcode: ", n_errcode, " -- n_sqlstate: ", n_sqlstate) return end local cjson = require("cjson") local cj_res = cjson.encode(obj_res) ngx.say("插入操作结果为： cj_res: ", cj_res) -- put it into the connection pool of size 100, with 10 seconds max idle timeout local ok, err = db:set_keepalive(10000, 100) if not ok then ngx.say("set_keepalive -- failed to set keepalive: ", err) return end -- or just close the connection right away: -- local ok, err = db:close() -- if not ok then -- ngx.say("failed to close", err) -- return -- end '; } # 事务处理 location /mysql-transcation { default_type "text/plain"; content_by_lua ' local cjson = require("cjson") local mysql = require("resty.mysql") local db, err = mysql:new() db:set_timeout(1000) -- 1 sec local conn, err, errcode, sqlstate = db:connect({ host = "10.253.1.251", port = 3306, database = "peanutcar", user = "root", password = "caikr" }) ngx.say("success! connected to mysql.") -- set character utf8 local res, err, errcode, sqlstate = db:query("SET NAMES utf8") -- start transcation res, err, errcode, sqlstate = db:query("begin") ngx.say("transcation begin! Result #1 -- res:", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") local str_sql_insert = "insert into cats (name) values (\'测试事务\'), (\'test transcation ~\'), (\'hello transcation~~~\')" -- insert res, err, errcode, sqlstate = db:query(str_sql_insert) ngx.say("insert -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") -- select local str_sql_select = "select * from cats order by id desc limit 10" res, err, errcode, sqlstate = db:query(str_sql_select) ngx.say("select -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") -- rollback res, err, errcode, sqlstate = db:query("rollback") ngx.say("rollback -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") -- select after rollback local str_sql_select = "select * from cats order by id desc limit 10" res, err, errcode, sqlstate = db:query(str_sql_select) ngx.say("select [after rollback] -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") -- update after rollback local str_sql_update = "update cats set name = \'test update Bob > Tom\' where id = 100" res, err, errcode, sqlstate = db:query(str_sql_update) ngx.say("update -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") -- select after update str_sql_select = "select * from cats order by id desc limit 10" res, err, errcode, sqlstate = db:query(str_sql_select) ngx.say("select [after update] -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------\\n") -- commit res, err, errcode, sqlstate = db:query("commit") ngx.say("commit -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) -- select after commit str_sql_select = "select * from cats order by id desc limit 10" res, err, errcode, sqlstate = db:query(str_sql_select) ngx.say("select [after commit] -- res: ", cjson.encode(res), ", err:", err, ", errcode:", errcode, ", sqlstate:", sqlstate) ngx.say("------------------------------------------") -- put it into the connection pool of size 100, with 10 seconds max idle timeout local ok, err = db:set_keepalive(10000, 100) if not ok then ngx.say("set_keepalive -- failed to set keepalive: ", err) return end -- or just close the connection right away: -- local ok, err = db:close() -- if not ok then -- ngx.say("failed to close", err) -- return -- end '; }注意事项
需要手动设置编码格式， 手动控制事务。