如何在Lua脚本的SELECT查询中获取两个及以上值并以数组返回

Hey there! Let's figure out how to fetch multiple values from a SELECT query in Lua and return them as an array. The exact approach will vary a bit based on your database library (like LuaSQL for SQL databases, or built-in SQLite libraries), but I'll share a flexible, reusable solution that works with most common setups.

Step 1: Set Up Your Database Connection (Example with LuaSQL) #

First, make sure you have your database library installed. For LuaSQL (MySQL in this case), install it via LuaRocks if you haven't:

luarocks install luasql-mysql

Then establish a connection to your database:

local luasql = require "luasql.mysql"

-- Initialize the MySQL environment and connect to your database
local env = assert(luasql.mysql())
local db_conn = assert(env:connect(
    "your_database_name",
    "your_username",
    "your_password",
    "localhost",
    3306
))

Step 2: Create a Reusable Function to Fetch Results as an Array #

This function will run your SELECT query, iterate over the results, and convert each row into a numeric array (or associative, if you prefer). We'll focus on numeric arrays since that's what you asked for:

function fetch_query_as_array(db_conn, select_query)
    -- Execute the query and get a cursor
    local cursor = assert(db_conn:execute(select_query))
    
    -- Get column names to map values in order
    local column_names = cursor:getcolnames()
    local results_array = {}

    -- Loop through each row of results
    local row = cursor:fetch({}, "a") -- "a" gives associative table (column name → value)
    while row do
        -- Convert associative row to a numeric array
        local numeric_row = {}
        for i = 1, #column_names do
            table.insert(numeric_row, row[column_names[i]])
        end
        table.insert(results_array, numeric_row)

        -- Reuse the row table for better performance
        row = cursor:fetch(row, "a")
    end

    -- Clean up the cursor to free resources
    cursor:close()

    return results_array
end

Step 3: Use the Function and Process the Results #

Now call the function with your SELECT query and work with the returned array:

-- Example: Fetch user IDs and names from a users table
local user_query = "SELECT id, name FROM users WHERE age >= 18"
local user_results = fetch_query_as_array(db_conn, user_query)

-- Print the results to test
for index, row in ipairs(user_results) do
    print(string.format("Row %d: ID = %s, Name = %s", index, row[1], row[2]))
end

-- Don't forget to close the connection when you're done!
db_conn:close()
env:close()

Bonus: Fetch a Single Row as an Array #

If you're querying for a single row (e.g., SELECT col1, col2 FROM table WHERE id = 123), you can simplify the function to return a single array instead of an array of arrays:

function fetch_single_row_as_array(db_conn, select_query)
    local cursor = assert(db_conn:execute(select_query))
    local column_names = cursor:getcolnames()
    local row = cursor:fetch({}, "a")
    cursor:close()

    if not row then return {} end

    local numeric_row = {}
    for i = 1, #column_names do
        table.insert(numeric_row, row[column_names[i]])
    end
    return numeric_row
end

-- Usage example
local single_user = fetch_single_row_as_array(db_conn, "SELECT id, name FROM users WHERE id = 5")
print("User ID:", single_user[1], "Name:", single_user[2])

Key Notes #

• If you're using a different library (like lua-sqlite3), the core logic stays the same: execute the query, iterate over rows, and collect values into an array.
• Using the associative row first ("a" flag) ensures you get values in the exact order of your SELECT columns. If you use the numeric flag ("n"), the order will match the database's default column order, which might not be what you want.

内容的提问来源于stack exchange，提问作者Sriram S