-- 定义关闭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
[c-alert type=”warning”]最后使用close_db(db)
关闭数据库[/c-alert]
Nginx引入Lua
http{
# 定义lua文件的路径
lua_package_path "/usr/local/openresty/nginx/lua?.lua;;";
server {
listen 80;
server_name localhost;
charset utf-8;
location / {
default_type text/html;
charset utf-8;
# 引入lua文件
content_by_lua_file /usr/local/openresty/nginx/lua/mysql.lua;
}
}
}
© 版权声明
THE END