openresty开发系列28--openresty中操作mysql

Mysql客户端
   应用中最常使用的就是数据库了，尤其mysql数据库，那openresty lua如何操作mysql呢？
   默认安装OpenResty时已经自带了该模块。

案例，mysql数据库的常用操作，编辑testmysql.lua

# centos7中安装mariadb
# yum install -y mariadb-server mariadb
# systemctl start mariadb-server
# 设置root密码为'root'
# update mysql.user set password=PASSWORD('root') where user='root' and host='localhost';

# 创建测试的数据库openresty
create database openresty charset='utf8mb4';

# 测试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实例
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(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

--我们对数据库进行crud，统一的操作方法 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("----------创建表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("----------插入数据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("=========关闭db===========", "<br/>")

close_db(db)

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

对于新增/修改/删除会返回如下格式的响应：
{
    insert_id = 0,     ----insert_id是在使用自增序列时产生的id。
    server_status = 2,
    warning_count = 1,
    affected_rows = 32,   ----affected_rows表示操作影响的行数
    message = nil
}

对于查询会返回如下格式的响应：
{
    { id= 1, ch= "hello"},
    { id= 2, ch= "hello2"}
}
null将返回ngx.null。

访问请求http://10.11.0.215/testmysql
输出结果
start connect
end connect mysql
----------删除表user----------------
----------创建表user----------------
----------插入数据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----------------
=========关闭db===========

===========查询user=根据ch参数=======
select row 1 : ch = hello
select row 1 : id = 1
===========删除user========
delete rows : 2
===========关闭db========

注意：

客户端目前还没有提供预编译SQL支持（即占位符替换位置变量），
这样在入参时记得使用ngx.quote_sql_str进行字符串转义，防止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

当我们没有使用quote_sql_str时，使用如下查询语句，会把数据表中的所有数据查询出来

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
=========关闭db===========

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

连接池和之前Redis客户端完全一样。
local function close_db(db)  
    if not db then  
        return  
    end  
    --释放连接(连接池实现)  
    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关闭连接的方法

local function close_redis(red)

    if not red then

        return

    end

    --释放连接(连接池实现)

    local pool_max_idle_time =  --毫秒

    local pool_size =  --连接池大小

    local ok,err = red:set_keepalive(pool_max_idle_time, pool_size)

    if not ok then

        ngx.say("set keepalive error: ",err)

    end

end

-- 引入redis模块

local redis = require "resty.redis"

-- 创建一个对象，注意是用冒号调用的

local red = redis:new()

-- 设置超时(毫秒)

red:set_timeout()

--建立连接

local ip = "10.11.0.215"

local port =

local ok, err = red:connect(ip, port)

if not ok then

    ngx.say("connect to redis error : ", err)

    return close_redis(red)

end

-- 如果使用连接池，不需要每次都使用密码

local count, err = red:get_reused_times()

if  == count then

    ok, err = red:auth("redis123")

    if not ok then

        ngx.say("faied to auth:", err)

        return

    end

elseif err then

    ngx.say("failed to get reused times:",err)

    return

end

--调用API设置key

ok, err = red:set("msg", "hello world")

if not ok then

    ngx.say("set msg error :", err)

    return close_redis(red)

end

-- 调用API获取key值

local resp, err = red:get("msg1")

if not resp then

    ngx.say("get msg error:",err)

    return close_redis(red)

end

if resp == ngx.null then

    resp = 'hello lua'

end

ngx.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

    --释放连接(连接池实现)

    local pool_max_idle_time =  --毫秒

    local pool_size =  --连接池大小

    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实例

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()

--定义连接属性

local props = {

    host = "127.0.0.1",

    port = ,

    database = 'openresty',

    user = 'root',

    password = 'root',

    charset = 'utf8'

}

ngx.say("start connect","<br/>")

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

ngx.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 then

    ngx.say("drop table error :", err,", errno", errno, ", sqlstate : ",sqlstate)

    return close_db(db)

end

ngx.say("----------创建表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("----------插入数据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("=========关闭db===========", "<br/>")

close_db(db)