如何通过Lua借助HTTP请求与XAMPP环境下的MySQL数据库交互？

Hey there! Since you already have a working XAMPP setup with a website that interacts with MySQL, the easiest way to get Lua talking to your database is to leverage your existing web server as a middle layer. Lua will send HTTP requests to custom endpoints you build (like PHP scripts), and those scripts will handle the actual MySQL queries. Let's walk through this with concrete examples.

1. Create Database Endpoints (PHP Examples) #

First, let's build two simple PHP scripts that your Lua code can call. These will act as the bridge between HTTP requests and your MySQL database.

1.1 GET Endpoint: Fetch Data from MySQL #

Create a file named get_data.php in your XAMPP htdocs folder:

<?php
// Enable CORS if your Lua script runs on a different machine
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json");

// Database connection details (match your XAMPP MySQL setup)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'root'; // Default XAMPP MySQL username
$password = ''; // Default XAMPP MySQL password (empty unless you changed it)

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Example query: fetch all records from a "users" table
    $stmt = $pdo->query("SELECT id, name, email FROM users");
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($data);
} catch(PDOException $e) {
    echo json_encode(["error" => $e->getMessage()]);
}
?>

1.2 POST Endpoint: Insert Data into MySQL #

Create another file named add_data.php in htdocs:

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Allow-Headers: Content-Type");

$host = 'localhost';
$dbname = 'your_database_name';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Get POST data (make sure to validate/sanitize!)
    $input = json_decode(file_get_contents('php://input'), true);
    if(!isset($input['name']) || !isset($input['email'])) {
        echo json_encode(["error" => "Missing required fields (name/email)"]);
        exit;
    }

    // Use prepared statements to prevent SQL injection
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
    $stmt->bindParam(':name', $input['name']);
    $stmt->bindParam(':email', $input['email']);
    $stmt->execute();

    echo json_encode(["success" => true, "inserted_id" => $pdo->lastInsertId()]);
} catch(PDOException $e) {
    echo json_encode(["error" => $e->getMessage()]);
}
?>

Important Notes:

• Replace your_database_name with your actual database name, and adjust the table/column names to match your schema.
• Always use prepared statements (like in the POST example) to prevent SQL injection—never concatenate user input directly into queries!
• The CORS headers (Access-Control-Allow-Origin: *) are useful if your Lua script runs outside the same server as XAMPP. For production, restrict this to specific domains instead of using *.

2. Lua Code to Send HTTP Requests #

Next, we'll use Lua to call these endpoints. We'll use the lua-http library for HTTP requests and dkjson to handle JSON parsing.

2.1 Install Required Libraries #

First, install the libraries using LuaRocks (if you don't have LuaRocks installed, grab it from the official Lua website):

luarocks install lua-http
luarocks install dkjson

2.2 Lua Example: Fetch Data (GET Request) #

local http = require("http.client")
local dkjson = require("dkjson")

-- URL to your GET endpoint (adjust port if your XAMPP uses a non-default one)
local url = "http://localhost/get_data.php"

-- Send GET request
local response, err = http.get(url)
if not response then
    print("Error fetching data:", err)
    return
end

-- Read response body
local body, err = response:read_body()
if not body then
    print("Error reading response:", err)
    return
end

-- Parse JSON response
local data, pos, decode_err = dkjson.decode(body, 1, nil)
if decode_err then
    print("Error decoding JSON:", decode_err)
    return
end

-- Print the fetched data
print("Fetched data:")
for _, user in ipairs(data) do
    print(string.format("ID: %d, Name: %s, Email: %s", user.id, user.name, user.email))
end

2.3 Lua Example: Insert Data (POST Request) #

local http = require("http.client")
local dkjson = require("dkjson")

local url = "http://localhost/add_data.php"

-- Data to send (match the fields expected by your PHP endpoint)
local post_data = {
    name = "John Doe",
    email = "john@example.com"
}

-- Encode data to JSON
local json_data = dkjson.encode(post_data)

-- Send POST request with JSON content
local response, err = http.post(url, {
    headers = {
        ["Content-Type"] = "application/json",
        ["Content-Length"] = tostring(#json_data)
    },
    body = json_data
})

if not response then
    print("Error sending POST request:", err)
    return
end

local body, err = response:read_body()
if not body then
    print("Error reading response:", err)
    return
end

local result, pos, decode_err = dkjson.decode(body, 1, nil)
if decode_err then
    print("Error decoding JSON:", decode_err)
    return
end

if result.success then
    print(string.format("Data inserted successfully! ID: %d", result.inserted_id))
else
    print("Error inserting data:", result.error)
end

3. Testing the Setup #

1. Make sure your XAMPP Apache and MySQL services are running.
2. Create the users table in your database (if you don't have it already):

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       email VARCHAR(100) NOT NULL UNIQUE
   );
   

3. Run the Lua scripts—you should see data fetched or inserted successfully.

That's it! This approach keeps things simple by reusing your existing web server setup, and it's secure as long as you follow best practices like prepared statements and input validation.

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