openresty lua-resty-mysql使用
蓝飞
openresty lua-resty-mysql使用
官网：https://github.com/openresty/lua-resty-mysql
lua-resty-mysql 说明
mysql:new：创建mysql连接对象
语法格式：db, err = mysql:new()
Creates a MySQL connection object. In case of failures,
returns nil and a string describing the error
* 创建mysql连接对象
* 如果创建失败，返回nil、错误信息err
connect(options)：连接数据库
语法格式：ok, err, errcode, sqlstate = db:connect(options)
# options可选值
* host：mysql地址
* port：mysql端口
* path：he path of the unix socket file listened by the MySQL server
* database：数据库名称
* user：用户名
* password：密码
* charset：编码集
* max_packet_size：mysql最大返回的数据大小，默认1M
* ssl：是否使用ssl加密，默认false
* pool：数据库连接池，如果不设置，默认为：user:database:host:port、user:database:path
* pool_size：数据库连接池大小（针对每个worker，不是整个nginx服务器）
如果不设置backlog，总的数据库连接不会有限制
如果不设置，并且backlog没有设置，则不会创建连接池
如果不设置，但是设置了backlog，poolsize默认为lua_socket_pool_size
当连接不够时，超过存活时间的连接会断开，断开后可以建立新的连接
* backlog：设置后可以限制总的数据库连接
If specified, this module will limit the total number of opened
connections for this pool. No more connections than pool_size can
be opened for this pool at any time. If the connection pool is full,
subsequent connect operations will be queued into a queue equal to
this option's value (the "backlog" queue). If the number of queued
connect operations is equal to backlog, subsequent connect operations
will fail and return nil plus the error string "too many waiting
connect operations"
* 如果设置了，可以限制连接池总的连接数
* 连接数满了后，不能创建新的连接，会进入队列排队
* 队列数满了，会直接报错，返回nil，错误信息：too many waiting connect operations
* compact_arrays：true、false（默认）
when this option is set to true, then the query and read_result
methods will return the array-of-arrays structure for the resultset,
rather than the default array-of-hashes structure
* 如果设置为true，返回的数据结构：数组嵌套数组
* 默认返回hash数组结构
set_timeout：设置超时时间
语法格式：db:set_timeout(time)
Sets the timeout (in ms) protection for subsequent
operations, including the connect method
* 超时时间，单位毫秒
set_keepalive：设置空闲连接存活时间
语法格式：ok, err = db:set_keepalive(max_idle_timeout, pool_size)
Puts the current MySQL connection immediately into the ngx_lua
cosocket connection pool.
* 将当前连接放到连接池
You can specify the max idle timeout (in ms) when the connection
is in the pool and the maximal size of the pool every nginx worker process.
* 设置最大空闲时间、连接池大小（针对每个nginx worker）
In case of success, returns 1. In case of errors, returns nil with a string describing the error
* 设置成功，返回1
* 设置出错，返回nil，错误信息err
get_reused_times：判断当前连接重复使用次数
语法格式：times, err = db:get_reused_times()
This method returns the (successfully) reused times for the current connection.
In case of error, it returns nil and a string describing the error.
* 获取当前连接的重复使用次数
* 如果出错，返回nil，错误信息err
If the current connection does not come from the built-in connection pool, then
this method always returns 0, that is, the connection has never been reused (yet).
If the connection comes from the connection pool, then the return value is always
non-zero. So this method can also be used to determine if the current connection
comes from the pool
* 如果当前连接不来自于连接池，返回0
* 如果当前连接来自连接池，返回一个非0值
* 这方法可用来判断当前连接是不是来自于连接池
close：关闭连接
语法格式：ok, err = db:close()
Closes the current mysql connection and returns the status.
* 关闭数据库连接，返回状态
In case of success, returns 1. In case of errors, returns
nil with a string describing the error
* 关闭成功，返回1
* 关闭失败，返回nil、错误信息err
send_query：发送查询语句，不需要等待响应结果
语法格式：bytes, err = db:send_query(query)
Sends the query to the remote MySQL server without waiting for its replies.
* 向服务端发送查询语句，不需要等待响应结果
Returns the bytes successfully sent out in success and otherwise returns
nil and a string describing the error.
* 如果发送成功，返回发送成功的响应数据
* 如果发送失败，返回nil、错误信息err
You should use the read_result method to read the MySQL replies afterwards
* 使用read_result读取响应结果
read_result：读取响应结果
# 语法格式
res, err, errcode, sqlstate = db:read_result()
res, err, errcode, sqlstate = db:read_result(nrows)
* 读取服务端第一个返回的结果（多语句查询会返回多个结果）
# 结果示例
* 查询语句
{
{ name = "Bob", age = 32, phone = ngx.null },
{ name = "Marry", age = 18, phone = "10666372"}
}
* 插入语句
{
insert_id = 0,
server_status = 2,
warning_count = 1,
affected_rows = 32,
message = nil
}
if more results are following the current result, a second err return
value will be given the string again. One should always check this
(second) return value and if it is again, then she should call this
method again to retrieve more results. This usually happens when the
original query contains multiple statements (separated by semicolon
in the same query string) or calling a MySQL procedure. See also
Multi-Resultset Support.
* 如果返回多个结果，err字符串为again，可调用read_result继续读取结果
* 多结果查询：Multi-Resultset
In case of errors, this method returns at most 4 values: nil, err,
errcode, and sqlstate. The err return value contains a string describing
the error, the errcode return value holds the MySQL error code (a
numerical value), and finally, the sqlstate return value contains the
standard SQL error code that consists of 5 characters. Note that, the
errcode and sqlstate might be nil if MySQL does not return them.
* 如果出错了，方法最多返回4个结果：nil, err, errcode, and sqlstate
* err：错误信息
* errcode：错误信息状态码
* sqlstate：sql错误码
* 如果mysql不返回errcode、sqlstate，errcode、sqlstate为nil
The optional argument nrows can be used to specify an approximate
number of rows for the result set. This value can be used to
pre-allocate space in the resulting Lua table for the result set.
By default, it takes the value 4
* nrows：限制结果集的数量，默认为4
query：send_query、read_result的简化
# 语法格式
res, err, errcode, sqlstate = db:query(query)
res, err, errcode, sqlstate = db:query(query, nrows)
This is a shortcut for combining the send_query call and the
first read_result call.
* send_query、read_result的简化
You should always check if the err return value is again in case
of success because this method will only call read_result only
once for you. See also Multi-Resultset Support.
* 检查err的值是否是again
* read_result只会读取服务端第一次返回的结果
server_ver：服务端版本
语法格式：str = db:server_ver()
Returns the MySQL server version string, like "5.1.64".
* 返回服务端的版本
You should only call this method after successfully
connecting to a MySQL server, otherwise nil will be returned
* 需要在连接上服务端后调用，否则会返回nil
set_compact_arrays：压缩数据集
语法格式：db:set_compact_arrays(boolean)
Sets whether to use the "compact-arrays" structure for
the resultsets returned by subsequent queries. See the
compact_arrays option for the connect method for more details
* 是否压缩返回的数据结构
* 可参照connect的参数：compact_arrays
防止sql注入
local name = ngx.unescape_uri(ngx.var.arg_name)
local quoted_name = ngx.quote_sql_str(name)
local sql = "select * from users where name = " .. quoted_name
多结果操作
local cjson = require "cjson"
local mysql = require "resty.mysql"
local db = mysql:new()
local ok, err, errcode, sqlstate = db:connect({
host = "127.0.0.1",
port = 3306,
database = "world",
user = "monty",
password = "pass"})
if not ok then
ngx.log(ngx.ERR, "failed to connect: ", err, ": ", errcode, " ", sqlstate)
return ngx.exit(500)
end
res, err, errcode, sqlstate = db:query("select 1; select 2; select 3;")
if not res then
ngx.log(ngx.ERR, "bad result #1: ", err, ": ", errcode, ": ", sqlstate, ".")
return ngx.exit(500)
end
ngx.say("result #1: ", cjson.encode(res))
local i = 2
while err == "again" do
res, err, errcode, sqlstate = db:read_result()
if not res then
ngx.log(ngx.ERR, "bad result #", i, ": ", err, ": ", errcode, ": ", sqlstate, ".")
return ngx.exit(500)
end
ngx.say("result #", i, ": ", cjson.encode(res))
i = i + 1
end
local ok, err = db:set_keepalive(10000, 50)
if not ok then
ngx.log(ngx.ERR, "failed to set keepalive: ", err)
ngx.exit(500)
end
# 返回结果
result #1: [{"1":"1"}]
result #2: [{"2":"2"}]
result #3: [{"3":"3"}]
结果调试：将返回结果编码为字符串后输出
local cjson = require "cjson"
...
local res, err, errcode, sqlstate = db:query("select * from cats")
if res then
print("res: ", cjson.encode(res))
end
错误日志
By default the underlying ngx_lua module does error logging when
socket errors happen. If you are already doing proper error handling
in your own Lua code, then you are recommended to disable this automatic
error logging by turning off ngx_lua's lua_socket_log_errors directive
* 默认情况下，ngx_lua会记录错误日志
* 如果代码中已经有了错误处理，可关闭ngx_lua错误日志记录
# 关闭ngx_lua错误日志记录
lua_socket_log_errors off;
lua-resty-mysql使用限制
This library cannot be used in code contexts like
init_by_lua*, set_by_lua*, log_by_lua*, and header_filter_by_lua*
where the ngx_lua cosocket API is not available
* 不能在init_by_lua*, set_by_lua*, log_by_lua*, and header_filter_by_lua*使用mysql
* 在这些模块中，cosocket API不能使用
The resty.mysql object instance cannot be stored in a Lua variable
at the Lua module level, because it will then be shared by all the
concurrent requests handled by the same nginx worker process
* resty.mysql对象不能在lua模块级别的变量中存储
* 会被nginx worker中的所有请求共享
使用示例
创建mysql
docker run -it -d --net fixed --ip 172.18.0.61 -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 --name mysql4 mysql
数据库操作
# 修改认证方式
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 创建数据库
mysql> create database lihu;
Query OK, 1 row affected (0.01 sec)
default.conf
server {
listen 80;
server_name localhost;
location / {
root /usr/local/openresty/nginx/html;
index index.html index.htm;
}
location /test {
content_by_lua_block {
local mysql = require 'resty.mysql';
local db, err = mysql:new();
if not db then
ngx.say("mysql创建失败", err);
end
db:set_timeout(1000);
local res, err, errcode, sqlstate = db:connect({
host = "172.18.0.61", port = 3306, database = "lihu",
user = "root", password = "123456"
});
if not res then
ngx.say("连接出错", err, errcode, sqlstate);
end
ngx.say("创建表: test")
res, err, errcode, sqlstate = db:query("create table test("
.."id int primary key auto_increment, "
.."name varchar(20)"
..")");
if not res then
ngx.say("表创建失败", err);
end
ngx.say("\n数据插入: ", "insert into test(name) values('gtlx'),('gzw')");
res, err, errcode, sqlstate = db:query(
"insert into test(name) values('gtlx'),('hzw')"
);
if not res then
ngx.say("数据插入失败", err);
end
ngx.say("\n数据查询: ", "select * from test");
res, err, errcode, sqlstate = db:query(
"select * from test"
);
if not res then
ngx.say("数据查询失败", err);
end
local cjson = require 'cjson';
ngx.say("返回的数据类型：", type(res));
ngx.say("查询的数据为：", cjson.encode(res));
ngx.say("\n数据更新: ","update test set name = 'gtls' where id = 2");
res, err, errcode, sqlstate = db:query(
"update test set name = 'gtls' where id = 2"
);
if not res then
ngx.say("数据更新失败", err);
end
res, err, errcode, sqlstate = db:query(
"select * from test"
);
if not res then
ngx.say("数据查询失败", err);
end
ngx.say("更新后查询的数据为：", cjson.encode(res));
ngx.say("\n数据删除: ", "delete from test where id = 2");
res, err, errcode, sqlstate = db:query(
"delete from test where id = 2"
);
if not res then
ngx.say("数据删除失败", err);
end
res, err, errcode, sqlstate = db:query(
"select * from test"
);
if not res then
ngx.say("数据查询失败", err);
end
ngx.say("删除后查询的数据为：", cjson.encode(res));
}
}
location /insert {
content_by_lua_block {
local mysql = require 'resty.mysql';
local db, err = mysql:new();
if not db then
ngx.say("mysql创建失败", err);
end
db:set_timeout(1000);
local res, err, errcode, sqlstate = db:connect({
host = "172.18.0.61", port = 3306, database = "lihu",
user = "root", password = "123456"
});
if not res then
ngx.say("连接出错", err, errcode, sqlstate);
end
for i=1,9 do
db:query("insert into test(name) values('gtlx')");
end
}
}
location /test2 {
content_by_lua_block {
local mysql = require 'resty.mysql';
local db, err = mysql:new();
if not db then
ngx.say("mysql创建失败", err);
end
db:set_timeout(1000);
local res, err, errcode, sqlstate = db:connect({
host = "172.18.0.61", port = 3306, database = "lihu",
user = "root", password = "123456"
});
if not res then
ngx.say("连接出错", err, errcode, sqlstate);
end
ngx.say("数据查询: ","select * from test limit 1");
res, err, errcode, sqlstate = db:query(
"select * from test limit 1"
);
if not res then
ngx.say("数据查询失败", err);
end
local cjson = require 'cjson';
ngx.say("查询的数据为：", cjson.encode(res));
ngx.say("\n数据查询: ", "select * from test", " nrows = 1" );
res, err, errcode, sqlstate = db:query(
"select * from test", 1
);
if not res then
ngx.say("数据查询失败", err);
end
ngx.say("查询的数据为：", cjson.encode(res));
--for i=1,9 do
-- db:query("insert into test(name) values('gtlx')")
--end
ngx.say("\n多结果数据查询: ", "select * from test where id = 1; select * from test where id = 3");
res, err, errcode, sqlstate = db:query(
"select * from test where id = 1; select * from test where id = 3"
);
if not res then
ngx.say("数据查询失败", err);
end
ngx.say("查询的数据为: result[1] ==> ", cjson.encode(res));
local index = 2;
while err == 'again' do
res, err, errcode, sqlstate = db:read_result();
if not res then
ngx.say("数据查询失败", err);
end
ngx.say("查询的数据为: result[",index,"] ==> ", cjson.encode(res));
end
}
}
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /usr/local/openresty/nginx/html;
}
}
创建容器
docker run -it -d --net fixed --ip 172.18.0.62 -p 5002:80 \
-v /Users/huli/lua/openresty/mysql/default.conf:/etc/nginx/conf.d/default.conf \
--name resty-mysql lihu12344/openresty使用测试
huli@hudeMacBook-Pro mysql % curl localhost:5002/test
创建表: test
数据插入: insert into test(name) values('gtlx'),('gzw')
数据查询: select * from test
返回的数据类型：table
查询的数据为：[{"id":1,"name":"gtlx"},{"id":2,"name":"hzw"}]
数据更新: update test set name = 'gtls' where id = 2
更新后查询的数据为：[{"id":1,"name":"gtlx"},{"id":2,"name":"gtls"}]
数据删除: delete from test where id = 2
删除后查询的数据为：[{"id":1,"name":"gtlx"}]
# 插入数据
huli@hudeMacBook-Pro mysql % curl localhost:5002/insert
# 数据查询测试
huli@hudeMacBook-Pro mysql % curl localhost:5002/test2
数据查询: select * from test limit 1
查询的数据为：[{"id":1,"name":"gtlx"}]
# nrows不能限制查询的条数
数据查询: select * from test nrows = 1
查询的数据为：[{"id":1,"name":"gtlx"},{"id":3,"name":"gtlx"},{"id":4,"name":"gtlx"},{"id":5,"name":"gtlx"},{"id":6,"name":"gtlx"},{"id":7,"name":"gtlx"},{"id":8,"name":"gtlx"},{"id":9,"name":"gtlx"},{"id":10,"name":"gtlx"},{"id":11,"name":"gtlx"}]
多结果数据查询: select * from test where id = 1; select * from test where id = 3
查询的数据为: result[1] ==> [{"id":1,"name":"gtlx"}]
查询的数据为: result[2] ==> [{"id":3,"name":"gtlx"}]
类似资料：