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

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

--删除表

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

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

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