Lua開發庫Mysql · Nginx+Lua OpenResty 開發

# Mysql客戶端 lua-resty-mysql是為基于cosocket API的ngx\_lua提供的Lua Mysql客戶端，通過它可以完成Mysql的操作。默認安裝OpenResty時已經自帶了該模塊，使用文檔可參考[https://github.com/openresty/lua-resty-mysql](https://github.com/openresty/lua-resty-mysql)。 ## 1. 編輯mysql/test_mysql.lua ``` local function close_db( db ) if not db then return end db:close() end local mysql = require("resty.mysql") -- 創建實例 local db, err = mysql:new() if not db then ngx.say("new mysql error:", err) return end -- 設置超時時間(毫秒) db:set_timeout(5000) local props = { host = "172.19.237.44", port = 3306, database = "fuhuitest", user = "root", password = "wsy@123456" } local res, err, errno, sqlstate = db:connect(props) if not res then ngx.say("connect to mysql error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end -- 刪除表 local drop_table_sql = "drop table if exists test" res, err, errno, sqlstate = db:query(drop_table_sql) if not res then ngx.say("drop table error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end -- 創建表 local create_table_sql = "create table test(id int primary key auto_increment, ch varchar(100))" res, err, errno, sqlstate = db:query(create_table_sql) if not res then ngx.say("create table error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end -- 插入 local insert_sql = "insert into test (ch) values('hello')" res, err, errno, sqlstate = db:query(insert_sql) if not res then ngx.say("insert error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end res, err, errno, sqlstate = db:query(insert_sql) ngx.say("insert rows : ", res.affected_rows, " , id : ", res.insert_id, " ") -- 更新 local update_sql = "update test set ch = 'hello2' where id =" .. res.insert_id res, err, errno, sqlstate = db:query(update_sql) if not res then ngx.say("update error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end ngx.say("update rows : ", res.affected_rows, " ") -- 查詢 local select_sql = "select id, ch from test" res, err, errno, sqlstate = db:query(select_sql) if not res then ngx.say("select error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end for i, row in ipairs(res) do for name, value in pairs(row) do ngx.say("select row ", i, " : ", name, " = ", value, " ") end end ngx.say(" ") --防止sql注入 local ch_param = ngx.req.get_uri_args()["ch"] or '' --使用ngx.quote_sql_str防止sql注入 local query_sql = "select id, ch from test where ch = " .. ngx.quote_sql_str(ch_param) res, err, errno, sqlstate = db:query(query_sql) if not res then ngx.say("select error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end for i, row in ipairs(res) do for name, value in pairs(row) do ngx.say("select row ", i, " : ", name, " = ", value, " ") end end -- 刪除 local delete_sql = "delete from test" res, err, errno, sqlstate = db:query(delete_sql) if not res then ngx.say("delete error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end ngx.say("delete rows : ", res.affected_rows, " ") close_db(db) ``` 對于新增/修改/刪除會返回如下格式的響應： ``` { insert_id = 0, server_status = 2, warning_count = 1, affected_rows = 32, message = nil } ``` affected\_rows表示操作影響的行數，insert\_id是在使用自增序列時產生的id。對于查詢會返回如下格式的響應： ``` { { id= 1, ch= "hello"}, { id= 2, ch= "hello2"} } ``` null將返回ngx.null。 ## 2. openResty.conf配置文件 ``` location /lua_mysql { default_type 'text/html'; lua_code_cache on; content_by_lua_file /usr/openResty/lua/mysql/test_mysql.lua; } ``` ## 3. 訪問如http://127.0.0.1/lua_mysql?ch=hello進行測試，得到如下結果 ``` insert rows : 1 , id : 2 update rows : 1 select row 1 : ch = hello select row 1 : id = 1 select row 2 : ch = hello2 select row 2 : id = 2 select row 1 : ch = hello select row 1 : id = 1 delete rows : 2 ``` 客戶端目前還沒有提供預編譯SQL支持（即占位符替換位置變量），這樣在入參時記得使用ngx.quote\_sql\_str進行字符串轉義，防止sql注入；連接池和之前Redis客戶端完全一樣就不介紹了。 對于Mysql客戶端的介紹基本夠用了，更多請參考[https://github.com/openresty/lua-resty-mysql](https://github.com/openresty/lua-resty-mysql)。