Skip to content
W3S101

Lua 数据库访问

Lua - 数据库访问

Section titled “Lua - 数据库访问”

虽然 Lua 可以使用普通文件进行数据存储，但对于更复杂、可伸缩和高效的数据操作，数据库是必不可少的。LuaSQL 是一个常用的库，它为 Lua 到各种数据库管理系统（Database Management Systems，简称 DBMS）提供了一致的接口。还有其他特定于数据库的驱动程序，通常可以通过 LuaRocks 安装。

LuaSQL 支持多种后端，包括：

  • SQLite
  • MySQL / MariaDB
  • PostgreSQL
  • ODBC（用于连接 SQL Server、Access 等）
  • Oracle

本教程将以 SQLite 和 MySQL 为例，演示通用的 LuaSQL 工作流程。这些原理可以应用于其他受支持的数据库。

通用 LuaSQL 工作流程

Section titled “通用 LuaSQL 工作流程”
  1. 加载驱动： 使用 require 加载特定的 LuaSQL 驱动（例如 luasql.sqlite3 或 luasql.mysql）。
  2. 创建环境对象： 调用驱动的主函数（例如 sqlite3.sqlite3()）以获取一个环境对象（environment object）。
  3. 连接数据库： 使用环境对象的 connect 方法建立连接。连接参数因数据库而异。
  4. 执行 SQL 语句： 使用连接对象的 execute 方法运行 SQL 查询（CREATE, INSERT, UPDATE, DELETE, SELECT）。
  5. 处理结果（针对 SELECT）： 如果 execute 返回一个游标对象（cursor object，用于 SELECT 查询），使用其 fetch 方法检索行。
  6. 关闭资源： 完成操作后关闭游标、连接和环境对象，以释放资源。

错误处理

Section titled “错误处理”

大多数 LuaSQL 函数在失败时返回 nil 和一个错误消息，成功时返回特定的对象（如连接、游标）。conn:execute() 对于非 SELECT 查询可能返回 0 或受影响的行数，对于 SELECT 查询则返回一个游标。始终检查返回值以处理错误。

SQLite 示例

Section titled “SQLite 示例”

SQLite 是一个轻量级、基于文件的数据库，非常适合嵌入式应用和简单项目。

1. 设置与连接：

Section titled “1. 设置与连接：”
local sqlite3 = require "luasql.sqlite3"


-- Create an environment object
local env, err_env = sqlite3.sqlite3()
if not env then
  error("Failed to create SQLite3 environment: " .. (err_env or "unknown error"))
end


-- Connect to the database (creates mydb.sqlite if it doesn't exist)
local conn, err_conn = env:connect('mydb.sqlite')
if not conn then
  env:close() -- Close environment if connection failed
  error("Failed to connect to SQLite database: " .. (err_conn or "unknown error"))
end


print("Successfully connected to SQLite database 'mydb.sqlite'")

2. 创建表：

Section titled “2. 创建表：”
local sql_create_table = [[
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  );
]]


local ok, err_create = conn:execute(sql_create_table)
if not ok then
  print("Error creating table: " .. (err_create or "unknown error"))
else
  print("Table 'users' ensured or created.")
end

3. 插入数据：

Section titled “3. 插入数据：”
-- WARNING: For production, always sanitize inputs or use parameterized queries if supported by the driver
-- to prevent SQL injection. LuaSQL itself is basic; consider ORMs for more safety.
local function insert_user(name, email)
  -- Basic quoting for this example, NOT secure for untrusted input
  local safe_name = string.gsub(name, "'", "''")
  local safe_email = string.gsub(email, "'", "''")
  local sql_insert = string.format("INSERT INTO users (name, email) VALUES ('%s', '%s')", safe_name, safe_email)


  local affected_rows, err_insert = conn:execute(sql_insert)
  if not affected_rows then -- For INSERT, execute returns number of affected rows or nil on error
    print(string.format("Error inserting user %s: %s", name, (err_insert or "unknown error")))
  else
    print(string.format("User %s inserted. Affected rows: %d", name, affected_rows))
  end
end


insert_user("Alice Wonderland", "alice@example.com")
insert_user("Bob The Builder", "bob@example.com")

4. 选择数据：

Section titled “4. 选择数据：”
local sql_select = "SELECT id, name, email FROM users WHERE name LIKE '%Alice%';"
local cursor, err_select = conn:execute(sql_select)


if not cursor then
  print("Error selecting users: " .. (err_select or "unknown error"))
else
  print("\nUsers found:")
  -- Fetch rows one by one. fetch({}, 'a') populates a new table with field names as keys.
  local row = cursor:fetch({}, "a")
  while row do
    print(string.format("  ID: %d, Name: %s, Email: %s", row.id, row.name, row.email))
    row = cursor:fetch(row, "a") -- Reuse the table for efficiency
  end
  cursor:close() -- Always close the cursor
end

5. 更新与删除数据：

Section titled “5. 更新与删除数据：”
local sql_update = "UPDATE users SET email = 'alice.wonder@example.com' WHERE name = 'Alice Wonderland';"
local affected_update, err_update = conn:execute(sql_update)
if affected_update then
  print(string.format("\nAlice's email updated. Affected rows: %d", affected_update))
else
  print("Error updating Alice's email: ", err_update or "unknown error")
end


local sql_delete = "DELETE FROM users WHERE name = 'Bob The Builder';"
local affected_delete, err_delete = conn:execute(sql_delete)
if affected_delete then
  print(string.format("Bob deleted. Affected rows: %d", affected_delete))
else
  print("Error deleting Bob: ", err_delete or "unknown error")
end

6. 关闭连接与环境：

Section titled “6. 关闭连接与环境：”
conn:close()
env:close()
print("\nSQLite connection closed.")


-- Ensure you add appropriate error checks around each conn:execute() call in real applications.

MySQL / MariaDB 示例

Section titled “MySQL / MariaDB 示例”

MySQL 是一个流行的客户端-服务器关系型数据库。MariaDB 是 MySQL 的社区开发分支，两者大致兼容。

1. 设置与连接：

Section titled “1. 设置与连接：”
local mysql = require "luasql.mysql"


local env, err_env = mysql.mysql()
if not env then
  error("Failed to create MySQL environment: " .. (err_env or "unknown error"))
end


-- Connection parameters: database_name, user, password, host (optional), port (optional)
-- Replace with your actual MySQL credentials and database details.
-- It's highly recommended to use environment variables or a config file for credentials.
local db_name = os.getenv("LUA_DB_NAME") or "test_lua_db"
local db_user = os.getenv("LUA_DB_USER") or "lua_user"
local db_pass = os.getenv("LUA_DB_PASS") or "password"
local db_host = os.getenv("LUA_DB_HOST") or "127.0.0.1"
local db_port = tonumber(os.getenv("LUA_DB_PORT")) -- or nil for default


local conn, err_conn = env:connect(db_name, db_user, db_pass, db_host, db_port)
if not conn then
  env:close()
  error(string.format("Failed to connect to MySQL (Host: %s, DB: %s, User: %s): %s",
    db_host, db_name, db_user, (err_conn or "unknown error")))
end


print("Successfully connected to MySQL database '" .. db_name .. "'")


-- The DDL, DML (CREATE, INSERT, SELECT etc.) operations are very similar to SQLite example above.
-- Key differences might be in SQL syntax details (e.g., AUTO_INCREMENT vs AUTOINCREMENT).


-- Example: Create table for MySQL
local sql_create_mysql_table = [[
  CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2)
  );
]]
local ok_mysql_create, err_mysql_create = conn:execute(sql_create_mysql_table)
if not ok_mysql_create then
  print("Error creating MySQL table: " .. (err_mysql_create or "unknown error"))
else
  print("Table 'products' in MySQL ensured or created.")
end


-- Remember to close connection and environment for MySQL as well
-- conn:close()
-- env:close()
-- print("\nMySQL connection closed.")


-- For brevity, further INSERT, SELECT operations follow the same pattern as SQLite
-- just with MySQL-specific SQL syntax if needed.

事务 (Transactions)

Section titled “事务 (Transactions)”

事务通过将多个 SQL 语句分组为一个单一的工作单元来确保数据一致性。它们遵循 ACID 特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

  • conn:execute('START TRANSACTION;') 或 conn:execute('BEGIN;')：开始一个事务。
  • conn:execute('COMMIT;')：保存事务期间所做的所有更改。
  • conn:execute('ROLLBACK;')：撤销事务期间所做的所有更改。

许多驱动程序还提供了 conn:commit() 和 conn:rollback() 方法，以及 conn:setautocommit(false) 来开启隐式事务模式。

-- Assuming 'conn' is an active LuaSQL connection (e.g., to SQLite or MySQL)


-- Example transaction (conceptual - ensure table 'accounts' exists)
-- CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
-- INSERT INTO accounts (id, name, balance) VALUES (1, 'Alice', 100.0);
-- INSERT INTO accounts (id, name, balance) VALUES (2, 'Bob', 50.0);


local ok_begin, err_begin = conn:execute('BEGIN TRANSACTION') -- Or START TRANSACTION
if not ok_begin then
  print("Error starting transaction: ", err_begin)
  -- Handle error, maybe close connection
else
  print("Transaction started.")
  local transfer_amount = 10.0
  local ok_debit, err_debit = conn:execute(string.format("UPDATE accounts SET balance = balance - %f WHERE id = 1", transfer_amount))
  local ok_credit, err_credit = conn:execute(string.format("UPDATE accounts SET balance = balance + %f WHERE id = 2", transfer_amount))


  if ok_debit and ok_credit then
    local ok_commit, err_commit = conn:execute('COMMIT')
    if ok_commit then
      print("Transaction committed successfully.")
    else
      print("Error committing transaction: ", err_commit)
      -- Attempt rollback on commit failure
      local ok_rb_on_commit_fail, err_rb_on_commit_fail = conn:execute('ROLLBACK')
      print("Rollback on commit failure status: ", ok_rb_on_commit_fail, err_rb_on_commit_fail or "")
    end
  else
    print("Error during transaction (debit/credit failed). Rolling back...")
    print("Debit error: ", err_debit or "N/A")
    print("Credit error: ", err_credit or "N/A")
    local ok_rollback, err_rollback = conn:execute('ROLLBACK')
    if ok_rollback then
      print("Transaction rolled back.")
    else
      print("Error rolling back transaction: ", err_rollback)
    end
  end
end


-- Don't forget to close your main connection and environment when fully done.
-- conn:close()
-- env:close()

关于 SQL 注入的安全性说明： 上面的示例使用了字符串格式化来构建 SQL 查询。如果输入值来自不受信任的源（如用户输入），这种做法极易受到 SQL 注入攻击。LuaSQL 本身没有在所有驱动中提供标准化的预处理语句（prepared statements）或参数绑定（parameter binding）功能。对于生产应用，你必须严格清理所有输入，或使用处理预处理语句或正确转义的 Lua ORM（对象关系映射器，Object-Relational Mapper）或查询构建器库。为了简化，本教程展示了直接执行的方式，但请务必注意其中的风险。

深入学习： 探索 LuaRocks 查找其他数据库驱动（例如用于 PostgreSQL 的 pgmoon，以及用于 Redis 或 MongoDB 等 NoSQL 数据库的特定驱动）。对于复杂应用，考虑使用 Lua ORM，例如 Lapis ORM（如果使用 Lapis 框架）或 lua-resty-orm（用于 OpenResty）。