openresty開發(fā)系列28--openresty中操作mysql

Mysql客戶端
?? 應(yīng)用中最常使用的就是數(shù)據(jù)庫了，尤其mysql數(shù)據(jù)庫，那openresty lua如何操作mysql呢？
?? 默認(rèn)安裝OpenResty時(shí)已經(jīng)自帶了該模塊。


案例，mysql數(shù)據(jù)庫的常用操作，編輯testmysql.lua

# centos7中安裝mariadb
# yum install -y mariadb-server mariadb
# systemctl start mariadb-server
# 設(shè)置root密碼為'root'
# update mysql.user set password=PASSWORD('root') where user='root' and host='localhost';

# 創(chuàng)建測(cè)試的數(shù)據(jù)庫openresty
create database openresty charset='utf8mb4';

# 測(cè)試nginx配置
# nginx.conf
location /testmysql {
?? ??? ?content_by_lua_file /usr/local/lua/testmysql.lua;
?? ?}

# vim /usr/local/lua/testmysql.lua

local function close_db(db)
?? ?if not db then
?? ??? ?return
?? ?end
?? ?db:close()
end

-- 引入mysql實(shí)例
local mysql = require("resty.mysql")
--創(chuàng)建實(shí)例
local db, err = mysql:new()
if not db then
?? ?ngx.say("new mysql error : ", err)
?? ?return
end

--設(shè)置超時(shí)時(shí)間(毫秒)
db:set_timeout(10000)

--定義連接屬性
local props = {
?? ?host = "127.0.0.1",
?? ?port = 3306,
?? ?database = 'openresty',
?? ?user = 'root',
?? ?password = 'root',
?? ?charset = 'utf8'
}


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

--我們對(duì)數(shù)據(jù)庫進(jìn)行crud，統(tǒng)一的操作方法 query
--不同于其他語言 insert update delete select

ngx.say("----------刪除表user----------------","<br/>")

--刪除表
local drop_table_sql = "drop table if exists user"
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

ngx.say("----------創(chuàng)建表user----------------","<br/>")

local create_table_sql = "create table user(id int primary key auto_increment,username 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

ngx.say("----------插入數(shù)據(jù)user----------------","<br/>")
local insert_table_sql = "insert into user(username) values('jack'),('lucy'),('tom'),('lily')"
res, err, errno, sqlstate = db:query(insert_table_sql)
if not res then
?? ?ngx.say("insert table error :", err,", errno", errno, ", sqlstate : ",sqlstate)
?? ?return close_db(db)
end

res, err, errno, sqlstate = db:query(insert_table_sql)

ngx.say("insert rows :", res.affected_rows,", id", res.insert_id, "<br/>")


ngx.say("----------更新表user----------------","<br/>")

local update_sql = "update user set username = 'lucy' where id = " .. res.insert_id
res, err, errno, sqlstate = db:query(update_sql)
if not res then
?? ?ngx.say("update table error :", err,", errno", errno, ", sqlstate : ",sqlstate)
?? ?return close_db(db)
end

ngx.say("update rows :",res.affected_rows, "<br/>")

ngx.say("----------查詢表user----------------","<br/>")

--查詢
local select_sql = "select id,username from 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
?? ?for username,value in pairs(row) do
?? ??? ?ngx.say("select row", i," : ", username, " = ", value, "<br/>")
?? ?end
end

ngx.say("----------刪除user----------------","<br/>")

local ch_param = ngx.req.get_uri_args()["ch"] or ''
--local query_sql = "select id,username from user where username = " .. ngx.quote_sql_str(ch_param)
local query_sql = "select id,username from user where username = " .. ch_param
ngx.say(query_sql,"<br/>")
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 username,value in pairs(row) do
??????? ngx.say("select row ",i," : ",username, " = ",value,"<br/>")
??? end
end



ngx.say("=========關(guān)閉db===========", "<br/>")

close_db(db)

======================================

對(duì)于新增/修改/刪除會(huì)返回如下格式的響應(yīng)：
{
??? insert_id = 0,???? ----insert_id是在使用自增序列時(shí)產(chǎn)生的id。
??? server_status = 2,
??? warning_count = 1,
??? affected_rows = 32,?? ----affected_rows表示操作影響的行數(shù)
??? message = nil
}

對(duì)于查詢會(huì)返回如下格式的響應(yīng)：
{
??? { id= 1, ch= "hello"},
??? { id= 2, ch= "hello2"}
}
null將返回ngx.null。

訪問請(qǐng)求http://10.11.0.215/testmysql
輸出結(jié)果
start connect
end connect mysql
----------刪除表user----------------
----------創(chuàng)建表user----------------
----------插入數(shù)據(jù)user----------------
insert rows :4, id5
----------更新表user----------------
update rows :1
----------查詢表user----------------
select row1 : username = jack
select row1 : id = 1
select row2 : username = lucy
select row2 : id = 2
select row3 : username = tom
select row3 : id = 3
select row4 : username = lily
select row4 : id = 4
select row5 : username = lucy
select row5 : id = 5
select row6 : username = lucy
select row6 : id = 6
select row7 : username = tom
select row7 : id = 7
select row8 : username = lily
select row8 : id = 8
----------刪除user----------------
=========關(guān)閉db===========



===========查詢user=根據(jù)ch參數(shù)=======
select row 1 : ch = hello
select row 1 : id = 1
===========刪除user========
delete rows : 2
===========關(guān)閉db========

注意：

客戶端目前還沒有提供預(yù)編譯SQL支持（即占位符替換位置變量），
這樣在入?yún)r(shí)記得使用ngx.quote_sql_str進(jìn)行字符串轉(zhuǎn)義，防止sql注入；


--防止sql注入

local ch_param = ngx.req.get_uri_args()["ch"] or ''
--local query_sql = "select id,username from user where username = " .. ngx.quote_sql_str(ch_param)
-- 不使用quote_sql_str防注入
local query_sql = "select id,username from user where username = " .. ch_param
ngx.say(query_sql,"<br/>")
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 username,value in pairs(row) do
??????? ngx.say("select row ",i," : ",username, " = ",value,"<br/>")
??? end
end

當(dāng)我們沒有使用quote_sql_str時(shí)，使用如下查詢語句，會(huì)把數(shù)據(jù)表中的所有數(shù)據(jù)查詢出來

http://10.11.0.215/testmysql?ch='jack' or 1=1

select id,username from user where username = 'jack' or 1=1
select row 1 : username = jack
select row 1 : id = 1
select row 2 : username = lucy
select row 2 : id = 2
select row 3 : username = tom
select row 3 : id = 3
select row 4 : username = lily
select row 4 : id = 4
select row 5 : username = lucy
select row 5 : id = 5
select row 6 : username = lucy
select row 6 : id = 6
select row 7 : username = tom
select row 7 : id = 7
select row 8 : username = lily
select row 8 : id = 8
=========關(guān)閉db===========

==========================================

連接池和之前Redis客戶端完全一樣。
local function close_db(db) ?
??? if not db then ?
??????? return ?
??? end ?
??? --釋放連接(連接池實(shí)現(xiàn)) ?
??? 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) ?
??? end ?
end ?


更多資料 https://github.com/openresty/lua-resty-mysql
sqlstate https://blog.csdn.net/tercel99/article/details/1520094

?

testmysql.lua

[root@node5 conf]# cat /usr/local/lua/testredis.lua -- --定義redis關(guān)閉連接的方法 local function close_redis(red)if not red thenreturnend--釋放連接(連接池實(shí)現(xiàn))local pool_max_idle_time = 1000 --毫秒local pool_size = 100 --連接池大小local ok,err = red:set_keepalive(pool_max_idle_time, pool_size)if not ok thenngx.say("set keepalive error: ",err)end end -- 引入redis模塊 local redis = require "resty.redis" -- 創(chuàng)建一個(gè)對(duì)象，注意是用冒號(hào)調(diào)用的 local red = redis:new() -- 設(shè)置超時(shí)(毫秒) red:set_timeout(1000) --建立連接 local ip = "10.11.0.215" local port = 6379 local ok, err = red:connect(ip, port) if not ok thenngx.say("connect to redis error : ", err)return close_redis(red) end-- 如果使用連接池，不需要每次都使用密碼 local count, err = red:get_reused_times() if 0 == count thenok, err = red:auth("redis123")if not ok thenngx.say("faied to auth:", err)returnend elseif err thenngx.say("failed to get reused times:",err)return end--調(diào)用API設(shè)置key ok, err = red:set("msg", "hello world")if not ok thenngx.say("set msg error :", err)return close_redis(red) end-- 調(diào)用API獲取key值 local resp, err = red:get("msg1") if not resp thenngx.say("get msg error:",err)return close_redis(red) endif resp == ngx.null thenresp = 'hello lua' endngx.say("msg:",resp) close_redis(red) [root@node5 conf]# cat /usr/local/lua/testmy cat: /usr/local/lua/testmy: No such file or directory [root@node5 conf]# cat /usr/local/lua/testmy testmyredis.lua testmysql.lua [root@node5 conf]# cat /usr/local/lua/testmysql.lua local function close_db(db) if not db then return end --釋放連接(連接池實(shí)現(xiàn)) 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) end end-- 引入mysql實(shí)例 local mysql = require("resty.mysql") --創(chuàng)建實(shí)例 local db, err = mysql:new() if not db thenngx.say("new mysql error : ", err)return end--設(shè)置超時(shí)時(shí)間 db:set_timeout(10000)--定義連接屬性 local props = {host = "127.0.0.1",port = 3306,database = 'openresty',user = 'root',password = 'root',charset = 'utf8' }ngx.say("start connect","<br/>") local res,err,errno,sqlstate = db:connect(props) if not res thenngx.say("connect to mysql error:", err,", errno:", errno,", sqlstate:", sqlstate)return close_db(db) endngx.say("end connect mysql","<br/>")ngx.say("----------刪除表user----------------","<br/>")--刪除表 local drop_table_sql = "drop table if exists user" res, err, errno, sqlstate = db:query(drop_table_sql) if not res thenngx.say("drop table error :", err,", errno", errno, ", sqlstate : ",sqlstate)return close_db(db) endngx.say("----------創(chuàng)建表user----------------","<br/>") local create_table_sql = "create table user(id int primary key auto_increment,username varchar(100))" res, err, errno, sqlstate = db:query(create_table_sql) if not res thenngx.say("create table error :", err,", errno", errno, ", sqlstate : ",sqlstate)return close_db(db) endngx.say("----------插入數(shù)據(jù)user----------------","<br/>") local insert_table_sql = "insert into user(username) values('jack'),('lucy'),('tom'),('lily')" res, err, errno, sqlstate = db:query(insert_table_sql) if not res thenngx.say("insert table error :", err,", errno", errno, ", sqlstate : ",sqlstate)return close_db(db) endres, err, errno, sqlstate = db:query(insert_table_sql)ngx.say("insert rows :", res.affected_rows,", id", res.insert_id, "<br/>")ngx.say("----------更新表user----------------","<br/>")local update_sql = "update user set username = 'lucy' where id = " .. res.insert_id res, err, errno, sqlstate = db:query(update_sql) if not res thenngx.say("update table error :", err,", errno", errno, ", sqlstate : ",sqlstate)return close_db(db) endngx.say("update rows :",res.affected_rows, "<br/>")ngx.say("----------查詢表user----------------","<br/>")--查詢 local select_sql = "select id,username from user" res, err, errno, sqlstate = db:query(select_sql) if not res thenngx.say("select table error :", err,", errno", errno, ", sqlstate : ",sqlstate)return close_db(db) endfor i, row in ipairs(res) dofor username,value in pairs(row) dongx.say("select row", i," : ", username, " = ", value, "<br/>")end endngx.say("----------刪除user----------------","<br/>")local ch_param = ngx.req.get_uri_args()["ch"] or '' --local query_sql = "select id,username from user where username = " .. ngx.quote_sql_str(ch_param) local query_sql = "select id,username from user where username = " .. ch_param ngx.say(query_sql,"<br/>") res, err, errno, sqlstate = db:query(query_sql) if not res thenngx.say("select error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate)return close_db(db) endfor i,row in ipairs(res) dofor username,value in pairs(row) dongx.say("select row ",i," : ",username, " = ",value,"<br/>")end endngx.say("=========關(guān)閉db===========", "<br/>")close_db(db)

?

轉(zhuǎn)載于:https://www.cnblogs.com/reblue520/p/11434666.html

總結(jié)

以上是生活随笔為你收集整理的openresty开发系列28--openresty中操作mysql的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。