简单的Lua 连接操作mysql数据库的方法

admin Lua

2023-12-05 0 699

win 需要先安装luaforwindows
linux 需要安装 luarocks 并 luarocks install luasql-mysql

require\”luasql.mysql\”

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

–连接数据库
conn=env:connect(\”数据库名\”,\”用户名\”,\”密码\”,\”IP地址\”,端口)

–设置数据库的编码格式
conn:execute\”SET NAMES GB2312\”

–执行数据库操作
cur=conn:execute(\”select * from role\”)

row=cur:fetch({},\”a\”)

while row do
var=string.format(\”%d%s\\n\”,row.id,row.name)

print(var)

row=cur:fetch(row,\”a\”)
end

conn:close()–关闭数据库连接
env:close()–关闭数据库环境

上面是个简单的，我们再来看个稍微复杂些的例子

— load driver
require \”luasql.mysql\”
— create environment object
env = assert (luasql.mysql())
— connect to data source
con = assert (env:connect(\”database\”, \”usr\”, \”password\”, \”192.168.xx.xxx\”, 3306))
— reset our table
res = con:execute\”DROP TABLE people\” –建立新表people
res = assert (con:execute[[
CREATE TABLE people(
name varchar(50),
email varchar(50)
)
]])
— add a few elements
list = {
{ name=\”Jose das Couves\”, email=\”jose@couves.com\”, },
{ name=\”Manoel Joaquim\”, email=\”manoel.joaquim@cafundo.com\”, },
{ name=\”Maria das Dores\”, email=\”maria@dores.com\”, },
}
for i, p in pairs (list) do –加入数据到people表
res = assert (con:execute(string.format([[
INSERT INTO people
VALUES (\’%s\’, \’%s\’)]], p.name, p.email)
))
end
— retrieve a cursor
cur = assert (con:execute\”SELECT name, email from people\”) –获取数据
— print all rows
row = cur:fetch ({}, \”a\”) — the rows will be indexed by field names –显示出来
while row do
print(string.format(\”Name: %s, E-mail: %s\”, row.name, row.email))
row = cur:fetch (row, \”a\”) — reusing the table of results
end
— close everything
cur:close()
con:close()
env:close()