luasql连接数据库 lua数据库操作

[TOC]

Lua数据库访问(普通方式)

为lua安装数据库扩展主要是利用Lua的包管理工具LuaRocks

LuaRocks中有众多的lua包 包括但不限于 json, base64, md5, socke, 各种无数据,之类的

具体可以来这里看https://luarocks.org

注意事项

5.2 版本之后的require 不再定义全局变量，需要保存其返回值。
`require "luasql.mysql"`需要写成:`luasql = require "luasql.mysql"`

安装LuaRocks：

首先在安装之前是需要安装lua的,这里就不再累述了.

自动安装

sudo apt-get install lua5.1\
&& sudo apt-get install liblua5.1-dev\
&& sudo apt-get install luarocks

到时候 在编程的时候可以加入:require "luarocks.loader" -- 自动安装环境

手动安装

wget http://luarocks.org/releases/luarocks-2.2.1.tar.gz\
&& tar zxpf luarocks-2.2.1.tar.gz\
&& cd luarocks-2.2.1
./configure
sudo make bootstrap

安装 LuaRocks 报错解决方案

提示找不到lua.h,默认情况下会从/usr下寻找，因为我们需要指定lua.h的目录

Lua interpreter found: /usr/bin/lua...
Lua version detected: 5.1
lua found in $PATH: /usr/bin
Checking Lua includes... lua.h not found (looked in /usr/include, /usr/include/lua/5.1, /usr/include/lua5.1)
You may want to use the flag --with-lua or --with-lua-include. See --help.

configure failed.

查看lua存在的目录

find / -name lua.h

/usr/local/luajit/include/luajit-2.0/lua.h  
/usr/local/src/lua/lua-5.1.5/src/lua.h  
/usr/local/src/lua/LuaJIT-2.0.4/src/lua.h  
/usr/local/include/luajit-2.0/lua.h  
/usr/local/include/lua.h

再安装目录再重新编译：

./configure –with-lua=/usr/local –with-lua-include=/usr/local/include

也可以使用--prefix=/usr/local/luarocks指定安装目录,但是一般我都是默认的.

便已完毕后安装

make bootstrap
或者
make build\
&&make install

安装lua扩展

luarocks install luasql-mysql

安装期间可以能会报错

安装luasql-mysql 出错

Error: Could not find header file for MYSQL
No file mysql.h in /usr/local/mysql
You may have to install MYSQL in your system and/or pass MYSQL_DIR or MYSQL_INCDIR to the luarocks command.
Example: luarocks install luasql-mysql MYSQL_DIR=/usr/local

解决方案

在终端执行whereis mysql 找到mysql 的include路径,whereis mysql会返回MySQL的各种路径

luarocks install luasql-mysql MYSQL_INCDIR=/usr/include/mysql 

注释：配置自己查到的mysql.h 所在路径

Lua 连接MySql 数据库：

5.2 版本之后的require 不再定义全局变量，需要保存其返回值。

require "luasql.mysql"需要写成:luasql = require "luasql.mysql"

语法介绍,以MySQL为例

env = lusql.mysql() -- myql数据库的环境对象

env:connect("databasename", "username", "passwd", "ip", "port")--mysql的参数定义为

conn:close()--无参数，关闭connection,要求与之关联的cursor对象先关闭
            --否则关闭失败，重复关闭也会返回失败；关闭成功返回true

conn:commit(["DEFERRED" | "IMMEDIATE" | "EXCLUSIVE"])
--委托当前事务，仅在支持事务管理的数据库中具有此功能，如sqlite。

conn:rollback(["DEFERRED" | "IMMEDIATE" | "EXCLUSIVE"])
--回滚操作，回滚当前事务，同样属于特定函数，只有支持事务操作的数据库有效

conn:setautocommit(*)
--参数比较多，另起一行，*的参数如下
[boolean] [,"DEFERRED" | "IMMEDIATE" | "EXCLUSIVE"] [,timeout]
--特有“事务操作”函数，关闭或者打开autocommit功能。
--一般我们要设置事务,只需要第一个参数即可
--打开或者关闭“自动提交事务处理”功能。
--这个方法在不支持事务处理的数据库上不会正常工作。
--在支持事务处理单不支持自动提交事务处理的数据库上，会根据数据库驱动的不同而有不同的结果。
--返回值：设置成功返回 true，设置失败或者数据库不支持事务处理则返回 false

cur = conn:execute(statement [,timeout])
--执行给定的sql语句statement，timeout应该是某些数据库的特有参数，
--参考手册中注明了sqlite，通过测试发现mysql是不支持加timeout的
--timeout 单位毫秒级milliseconds，可选参数，当数据库忙是等待超时


cur:close()--关闭cur,重复关闭报false,成功返回true
--cursor对象包含一些函数，这些函数可以获取执行sql语句得到的数据结果；
--cursor对象是执行conn:execute和conn:prepare返回值

cur:fetch([table[,modestring]])
--获取下一行数据。
--[[如果fetch无参数调用，直接返回的数据表；如果是带table的参数，
则数据会先拷贝到table中，然后返回此table数据表,modestring有两种模式，
“n”返回结果中是数字索引,默认模式，“a”返回结果中是数字和字符的混杂模式。
n模式是select语句获取fields的index位置，a模式是这些field的名字
"重点强调一下, table参数一定是保存一下一row的数据，自身完成迭代"。
--]]

cur:getcolnames()
--无参数，返回column 名字

cur:getcoltypes()
--无参数，返回column 类型

连接MySQL的案例

luasql = require "luasql.mysql

--创建环境对象
env = luasql.mysql()

--连接数据库
conn = env:connect("数据库名","用户名","密码","IP地址",端口号)

--设置数据库的编码格式
conn:execute"SET NAMES UTF8"

--执行数据库操作
cur = conn:execute("select * from user")

row = cur:fetch({},"a")

--文件对象的创建
file = io.open("user.txt","w+");

--遍历得到的数据,按照字段名写入到文件中,且打印出来
while row do
    var = string.format("%d %s\n", row.id, row.uid)

    print(var)

    file:write(var)

    row = cur:fetch(row,"a")
end


file:close()  --关闭文件对象
conn:close()  --关闭数据库连接
env:close()   --关闭数据库环境

lua操作Redis

一时之间竟然没有找到好用的方式

Lua数据库访问(NGINX中的lua脚本必须使用的openresty)

安装lua-resty-redis和lua-resty-mysql

官方代码地址https://github.com/openresty/lua-resty-mysql

官方地址https://openresty.org/cn/linux-packages.html

注意这种形式访问的Redis,必须NGINX访问才能拿得到,直接运行lua文件是不行的.

操作Redis

-- 基本逻辑很简单，要注意此处判断是否为 nil，需要跟 ngx.null 比较。
local function close_redis(red)
    if not red then
        return
    end
    local ok, err = red:close()
    if not ok then
        ngx.say("close redis error : ", err)
    end
end

local redis = assert(require("resty.redis"))
--创建实例
local red = assert(redis:new()) --设置超时(毫秒) red:set_timeout(1000)
--建立连接
local ip = "127.0.0.1"
local port = 6379
local ok, err = red:connect(ip, port)
if not ok then
    ngx.say("connect to redis error : ", err)
    return close_redis(red)
end

--调用API进行处理
ok, err = red:set("msg", "hello world")
if not ok then
    ngx.say("set msg error : ", err)
    return close_redis(red)
end
--调用API获取数据
local resp, err = red:get("msg")
if not resp then
    ngx.say("get msg error : ", err)
    return close_reedis(red)
end
--得到的数据为空处理
if resp == ngx.null then
    resp = '' --比如默认值
end
ngx.say("msg : ", resp)
close_redis(red)

Redis连接池

建立 TCP 连接需要三次握手而释放 TCP 连接需要四次握手，而这些往返时延仅需要一次，以后应该复用 TCP 连接，此时就可以考虑使用连接池，即连接池可以复用连接。

我们只需要将之前的 close_redis 函数改造为如下即可:

local function close_redis(red)
    if not red then
        return
    end
    --释放连接(连接池实现)
    local pool_max_idle_time = 10000 --毫秒
    local pool_size = 100 --连接池大小
    local ok, err = red:set_keepalive(pool_max_idle_time, pool_size)
    if not ok then
        ngx.say("set keepalive error : ", err)
    end
end

即设置空闲连接超时时间防止连接一直占用不释放;设置连接池大小来复用连接。

此处假设调用 red:set_keepalive()，连接池大小通过 nginx.conf 中 http 部分的如下指令定义:

默认连接池大小，默认 30 lua_socket_pool_size 30; #默认超时时间,默认 60s lua_socket_keepalive_tim eout 60s;

注意:

1. 连接池是每 Worker 进程的，而不是每 Server 的;
2. 当连接超过最大连接池大小时，会按照 LRU 算法回收空闲连接为新连接使用;
3. 连接池中的空闲连接出现异常时会自动被移除;
4. 连接池是通过 ip 和 port 标识的，即相同的 ip 和 port 会使用同一个连接池(即使是不同类型的客户端如 Re dis、Memcached);
5. 连接池第一次 set_keepalive 时连接池大小就确定下了，不会再变更;
6. cosocket 的连接池 http://wiki.nginx.org/HttpLuaModule#tcpsock:setkeepalive。

Redis的pipeline

pipeline 即管道，可以理解为把多个命令打包然后一起发送;

MTU(Maxitum Transmission Unit 最大传输单 元)为二层包大小，一般为 1500 字节;

而 MSS(Maximum Segment Size 最大报文分段大小)为四层包大 小，其一般是 1500-20(IP 报头)-20(TCP 报头)=1460 字节;

因此假设我们执行的多个 Redis 命令能在 一个报文中传输的话，可以减少网络往返来提高速度。

因此可以根据实际情况来选择走 pipeline 模式将多个命令 打包到一个报文发送然后接受响应，而 Redis 协议也能很简单的识别和解决粘包。

修改之前的代码片段

red:init_pipeline()
red:set("msg1", "hello1")
red:set("msg2", "hello2")
red:get("msg1")
red:get("msg2")
local respTable, err = red:commit_pipeline()

--得到的数据为空处理
if respTable == ngx.null then
    respTable = {}
    --比如默认值
end
--结果是按照执行顺序返回的一个table
for i, v in ipairs(respTable) do
    ngx.say("msg : ", v, "<br/>")
end

通过 init_pipeline() 初始化，然后通过 commit_pipieline() 打包提交 init_pipeline() 之后的Redis命令;

返回结 果是一个 lua table，可以通过 ipairs 循环获取结果;

配置相应 location，测试得到的结果

msg : OK
msg : OK
msg : hello1
msg : hello2

Redis Lua 脚本

利用 Redis 单线程特性，可以通过在 Redis 中执行 Lua 脚本实现一些原子操作。如之前的 red:get("msg") 可 以通过如下两种方式实现:

1、直接 eval:

local resp, err = red:eval（"return redis.call('get', KEYS[1])", 1, "msg");

2、script load 然后 evalsha SHA1 校验和，这样可以节省脚本本身的服务器带宽: Java 代码

local sha1, err = red:script("load", "return redis.call('get', KEYS[1])");
if not sha1 then
    ngx.say("load script error : ", err)
    return close_redis(red)
end
ngx.say("sha1 : ", sha1, "<br/>")
local resp, err = red:evalsha(sha1, 1, "msg");

首先通过 script load 导入脚本并得到一个 sha1 校验和(仅需第一次导入即可)，然后通过evalsha 执行 sha1 校验和即可，这样如果脚本很长通过这种方式可以减少带宽的消耗。

此处仅介绍了最简单的 redis lua 脚本，更复杂的请参考官方文档学习使用。 另外 Redis 集群分片算法该客户端没有提供需要自己实现，当然可以考虑直接使用类似于Twemproxy 这种中间 件实现。

lua操作MySQL

lua-resty-mysql 是为基于 cosocket API 的 ngx_lua 提供的 Lua Mysql 客户端，通过它可以完成 Mysql 的 操作。默认安装 OpenResty 时已经自带了该模块，使用文档可参考https://github.com/openresty/lua-rest y-mysql。

local function close_db(db)
    if not db then
        return
    end
    db:close()
end
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(1000)
local props = {
    host = "127.0.0.1", port = 3306, database = "mysql", user = "root", password = "123456"
}

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





--删除表
local drop_table_sql = "drop table if exists test"
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

--创建表
local create_table_sql = "create table test(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

--插入
local insert_sql = "insert into test (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/>")

--更新
local update_sql = "update test set ch = 'hello2' where id =" .. res.insert_id
res, err, errno, sqlstate = db:query(update_sql)
if not res then
    ngx.say("update error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate)
    return close_db(db)
end
ngx.say("update rows : ", res.affected_rows, "<br/>")

--查询
local select_sql = "select id, ch from test"
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("<br/>")

--防止sql注入
local ch_param = ngx.req.get_uri_args()["ch"] or ''

--使用ngx.quote_sql_str防止sql注入
local query_sql = "select id, ch from test 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, " , 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

--删除

local delete_sql = "delete from test"
res, err, errno, sqlstate = db:query(delete_sql)
if not res then
    ngx.say("delete error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate)
    return close_db(db)
end

ngx.say("delete rows : ", res.affected_rows, "<br/>")

close_db(db)

对于新增/修改/删除会返回如下格式的响应:

{
    insert_id = 0, server_status = 2, warning_count = 1, affected_rows = 32, message = nil
}

affected_rows 表示操作影响的行数，insert_id 是在使用自增序列时产生的 id。

对于查询会返回如下格式的响应:

{
    { id= 1, ch= "hello"}, { id= 2, ch= "hello2"}
}

null 将返回 ngx.null。

返回结果

insert rows : 1 , id : 2
update rows : 1
select row 1 : ch = hello
select row 1 : id = 1
select row 2 : ch = hello2
select row 2 : id = 2
select row 1 : ch = hello
select row 1 : id = 1
delete rows : 2

客户端目前还没有提供预编译 SQL 支持(即占位符替换位置变量)，这样在入参时记得使用 ngx.quote_sql_st r 进行字符串转义，防止 sql 注入;连接池和之前 Redis 客户端完全一样就不介绍了。