Lua函数异步SQL查询导致canOpen始终返回false的问题排查

问题描述 #

我给权限验证函数IsAuthorized添加了一段逻辑：当doorID包含authorizedgang字段时，通过MySQL.Async.fetchSingle异步查询数据库，验证玩家是否属于指定帮派。查询能成功找到玩家，打印"found"并将canOpen设为true，但函数最终返回时canOpen仍为false。相关代码如下：

function IsAuthorized(xPlayer, doorID, locked, usedLockpick)
    local jobName, grade = {}, {}
    jobName[1] = xPlayer.job.name
    grade[1] = xPlayer.job.grade
    if xPlayer.job2 then
        jobName[2] = xPlayer.job2.name
        grade[2] = xPlayer.job2.grade
    end
    local canOpen = false


    if doorID.lockpick and usedLockpick then
        count = xPlayer.getInventoryItem('lockpick').count
        if count and count >= 1 then canOpen = true end
    end

    if not canOpen and doorID.authorizedJobs then
        for job,rank in pairs(doorID.authorizedJobs) do
            if (job == jobName[1] and rank <= grade[1]) or (jobName[2] and job == jobName[2] and rank <= grade[2]) then
                canOpen = true
                if canOpen then break end
            end
        end
    end


    if not canOpen and doorID.items then
        local count
        for k,v in pairs(doorID.items) do
            count = xPlayer.getInventoryItem(v).count
            if count and count >= 1 then
                canOpen = true
                local consumables = { ['ticket']=1 }
                if locked and consumables[v] then
                    xPlayer.removeInventoryItem(v, 1)
                end
                break
            end
        end
        if not count or count < 1 then canOpen = false end
    end

    if not canOpen then
        local group = xPlayer.getGroup()
        if group == 'management' or group == 'owner' then
            print(group..' '..xPlayer.getName()..' was authorised to use a door')
            canOpen = true
        end
        if doorID.authorizedgang then
            print(xPlayer.identifier .. " | " .. doorID.authorizedgang)
            MySQL.Async.fetchSingle('SELECT * FROM users WHERE identifier = ? AND gang = ?', {xPlayer.identifier, doorID.authorizedgang}, function(foundplayer)
                if foundplayer then
                    print("found")
                    canOpen = true
                    print(canOpen)
                end
            end)
        end
    end


    print(canOpen)
    return canOpen
end

问题原因 #

核心问题是异步操作的执行顺序：

• MySQL.Async.fetchSingle是异步函数，它不会暂停当前函数的执行，而是直接发起数据库查询后，让函数继续向下执行print(canOpen)和return canOpen。
• 此时数据库查询还未完成，canOpen仍为初始值false，所以函数返回的是false。
• 等数据库查询完成后，回调函数里才会把canOpen改为true，但这时函数已经返回完毕，修改的值无法被调用者获取。

解决方案 #

有两种可行方案，可根据场景选择：

方案一：改用同步数据库查询（简单直接） #

如果你的框架支持同步数据库操作（如MySQL.Sync.fetchSingle），可以直接将异步查询替换为同步查询，这样代码会等待查询完成后再继续执行，canOpen的修改能正确反映在返回值中。

修改后的帮派验证代码片段：

if doorID.authorizedgang then
    print(xPlayer.identifier .. " | " .. doorID.authorizedgang)
    -- 替换为同步查询
    local foundplayer = MySQL.Sync.fetchSingle('SELECT * FROM users WHERE identifier = ? AND gang = ?', {xPlayer.identifier, doorID.authorizedgang})
    if foundplayer then
        print("found")
        canOpen = true
        print(canOpen)
    end
end

方案二：将函数改为异步回调模式（适合高并发） #

如果不想阻塞主线程，可将整个函数改为异步模式，添加回调参数，等所有权限判断（包括异步数据库查询）完成后，通过回调返回结果。

修改后的完整函数：

function IsAuthorized(xPlayer, doorID, locked, usedLockpick, callback)
    local jobName, grade = {}, {}
    jobName[1] = xPlayer.job.name
    grade[1] = xPlayer.job.grade
    if xPlayer.job2 then
        jobName[2] = xPlayer.job2.name
        grade[2] = xPlayer.job2.grade
    end
    local canOpen = false

    if doorID.lockpick and usedLockpick then
        local count = xPlayer.getInventoryItem('lockpick').count
        if count and count >= 1 then canOpen = true end
    end

    if not canOpen and doorID.authorizedJobs then
        for job,rank in pairs(doorID.authorizedJobs) do
            if (job == jobName[1] and rank <= grade[1]) or (jobName[2] and job == jobName[2] and rank <= grade[2]) then
                canOpen = true
                break
            end
        end
    end

    if not canOpen and doorID.items then
        local count
        for k,v in pairs(doorID.items) do
            count = xPlayer.getInventoryItem(v).count
            if count and count >= 1 then
                canOpen = true
                local consumables = { ['ticket']=1 }
                if locked and consumables[v] then
                    xPlayer.removeInventoryItem(v, 1)
                end
                break
            end
        end
        if not count or count < 1 then canOpen = false end
    end

    if not canOpen then
        local group = xPlayer.getGroup()
        if group == 'management' or group == 'owner' then
            print(group..' '..xPlayer.getName()..' was authorised to use a door')
            canOpen = true
        end
        if doorID.authorizedgang then
            print(xPlayer.identifier .. " | " .. doorID.authorizedgang)
            MySQL.Async.fetchSingle('SELECT * FROM users WHERE identifier = ? AND gang = ?', {xPlayer.identifier, doorID.authorizedgang}, function(foundplayer)
                if foundplayer then
                    print("found")
                    canOpen = true
                    print(canOpen)
                end
                -- 查询完成后通过回调返回结果
                callback(canOpen)
            end)
            -- 提前返回，避免执行下面的回调
            return
        end
    end

    -- 未触发异步查询时，直接通过回调返回结果
    callback(canOpen)
end

调用该函数时需传入回调处理结果：

IsAuthorized(xPlayer, doorID, locked, usedLockpick, function(canOpen)
    if canOpen then
        -- 执行开门逻辑
    else
        -- 无权限处理
    end
end)

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