四.OpenResty中使用mysql

-- 定义关闭mysql的连接
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:",error)
     return
 end
 -- 设置超时时间(毫秒)
 db:set_timeout(1000)
 -- 连接属性定义
 local props = {
     host = "192.168.8.106",
     port = "3306",
     database = "test",
     user = "test",
     password = "123456",
     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

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, 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

ngx.say("--插入数据user--","<br/>")

 local insert_sql = "insert into user(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,"<br/>")

 -- res的返回值 对于新增/修改/删除会返回如下格式的响应:
 --[[
 {
 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"}
 }
 --]]

ngx.say("--更新表user--","<br/>")
 -- 定义更新语句
 local update_table_sql = "update user set ch = 'hello2' where id ="..res.insert_id
 res,err,errno,sqlstate=db:query(update_table_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, ch from user"
 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,"<br/>")
     end
 end


 -- 参数查询

 ngx.say("--查询user-根据ch参数--","<br/>")
 --防止sql注入
 local ch_param = ngx.req.get_uri_args()["ch"] or ''
 --使用ngx.quote_sql _str防止sql注入
 local query_sql = "select id,ch from user 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,"salstate:",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,"<br/>")
     end
 end