Lua新手求教：如何连接返回JSON的Web服务（附MySQL Proxy代码）

Hey there! Let's walk through how to pull JSON data from a web service in your MySQL Proxy Lua script. I'll break this down simply since you're new to Lua and MySQL Proxy, and fix a few small issues in your original code along the way.

First, a quick fix for your existing code #

Lua uses -- for comments (not // like some other languages), and it's better to load modules outside your read_query function so they only load once, not every time a query runs.

What you'll need #

You'll need two Lua modules:

• luasocket: To send HTTP requests to your web service
• A JSON parser (like dkjson—it's lightweight and easy to use with MySQL Proxy)

Make sure these modules are placed in a directory that MySQL Proxy can access (or install them via LuaRocks if your environment supports it).

Full working example code #

Here's how to modify your script to connect to the web service, fetch JSON, parse it, and return it as a MySQL result set:

-- Load modules once at the top, not inside the query handler
local socket = require('socket')
local dkjson = require('dkjson')

function read_query(packet)
    if string.byte(packet) == proxy.COM_QUERY then
        local command = string.lower(packet)
        -- Check if this is a SELECT query
        if string.find(command, "select") ~= nil and string.find(command, "from") ~= nil then
            -- Step 1: Connect to your web service
            local conn, err = socket.connect('localhost', 5050)
            if not conn then
                print("Connection failed: " .. err)
                -- Send an error back to the MySQL client
                proxy.response = {
                    type = proxy.MYSQLD_PACKET_ERR,
                    errmsg = "Could not connect to web service: " .. err
                }
                return proxy.PROXY_SEND_RESULT
            end

            -- Step 2: Send an HTTP GET request to your web service endpoint
            -- Replace "/data" with your actual endpoint path
            conn:send("GET /data HTTP/1.1\r\nHost: localhost:5050\r\nConnection: close\r\n\r\n")

            -- Step 3: Read the full response from the web service
            local full_response = ""
            local chunk
            while true do
                chunk, err = conn:receive(1024)
                if not chunk then break end
                full_response = full_response .. chunk
            end
            conn:close()

            -- Step 4: Separate the HTTP header from the JSON body
            local body_start = string.find(full_response, "\r\n\r\n") + 4
            local json_body = string.sub(full_response, body_start)

            -- Step 5: Parse the JSON into a Lua table
            local json_data, parse_err = dkjson.decode(json_body)
            if not json_data then
                print("JSON parse failed: " .. parse_err)
                proxy.response = {
                    type = proxy.MYSQLD_PACKET_ERR,
                    errmsg = "Invalid JSON from web service: " .. parse_err
                }
                return proxy.PROXY_SEND_RESULT
            end

            -- Step 6: Convert the parsed JSON into a MySQL result set
            -- Assume your JSON is an array of objects (e.g., [{"id":1,"name":"Alice"}, ...])
            local fields = {}
            -- Auto-detect field names from the first JSON object
            for key, _ in pairs(json_data[1]) do
                table.insert(fields, {
                    type = proxy.MYSQL_TYPE_STRING, -- Use appropriate type if needed (e.g., MYSQL_TYPE_LONG for integers)
                    name = key
                })
            end

            -- Build the rows of data
            local rows = {}
            for _, item in ipairs(json_data) do
                local row = {}
                for _, field in ipairs(fields) do
                    -- Convert values to strings (adjust for numeric types if needed)
                    table.insert(row, tostring(item[field.name]))
                end
                table.insert(rows, row)
            end

            -- Step 7: Send the result set back to the MySQL client
            proxy.response = {
                type = proxy.MYSQLD_PACKET_OK,
                resultset = {
                    fields = fields,
                    rows = rows
                }
            }
            return proxy.PROXY_SEND_RESULT
        end
    end
end

Key things to note #

• Error handling: We added checks for connection failures and JSON parsing errors, so the MySQL client gets a clear message instead of hanging.
• HTTP request format: The send call includes a full HTTP 1.1 request—this is required for the web service to recognize and respond correctly.
• Result set construction: MySQL Proxy expects a specific structure for resultset (fields + rows). We auto-detect fields from your JSON data to keep things flexible.
• Data types: The example uses MYSQL_TYPE_STRING for all fields. If your JSON has numbers, dates, etc., adjust the type value (e.g., proxy.MYSQL_TYPE_LONG for integers).

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