openresty mysql lua库连接提示Unknown collation: '0'，如何手动设置字符集-Go服务器开发

这个错误通常是因为 MySQL 版本不兼容或字符集配置问题导致的。以下是几种解决方案：

1. 在 OpenResty 连接时显式设置字符集

local mysql = require "resty.mysql"

-- 创建数据库连接
local db, err = mysql:new()
if not db then
    ngx.say("Failed to create mysql object: ", err)
    return
end

-- 设置超时时间
db:set_timeout(1000) -- 1秒

-- 连接数据库，显式指定字符集
local ok, err, errcode, sqlstate = db:connect{
    host = "127.0.0.1",
    port = 3306,
    database = "your_database",
    user = "your_username",
    password = "your_password",
    charset = "utf8mb4",  -- 明确指定字符集
    max_packet_size = 1024 * 1024
}

if not ok then
    ngx.say("Failed to connect: ", err, " : ", errcode, " ", sqlstate)
    return
end

2. 检查并修改 MySQL 服务器配置

在 MySQL 配置文件 my.cnf 中添加：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

3. 在连接后立即执行 SET NAMES

local ok, err = db:query("SET NAMES utf8mb4")
if not ok then
    ngx.say("Failed to set names: ", err)
    return
end

-- 或者更详细的设置
local queries = {
    "SET NAMES utf8mb4",
    "SET CHARACTER SET utf8mb4", 
    "SET character_set_connection = utf8mb4"
}

for _, query in ipairs(queries) do
    local res, err, errcode, sqlstate = db:query(query)
    if not res then
        ngx.say("Query failed: ", err)
        break
    end
end

4. 检查数据库和表的字符集

-- 检查数据库字符集
SHOW CREATE DATABASE your_database;

-- 检查表字符集  
SHOW CREATE TABLE your_table;

-- 修改数据库字符集（如果需要）
ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表字符集
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. lua-resty-mysql 库的完整示例

location /test-mysql {
    content_by_lua_block {
        local mysql = require "resty.mysql"
        
        local db = mysql:new()
        db:set_timeout(1000)
        
        local ok, err, errcode, sqlstate = db:connect{
            host = "127.0.0.1",
            port = 3306,
            database = "test_db",
            user = "test_user", 
            password = "test_pass",
            charset = "utf8mb4"
        }
        
        if not ok then
            ngx.say("Connect error: ", err)
            return
        end
        
        -- 确保字符集设置生效
        local res, err = db:query("SET NAMES utf8mb4")
        if not res then
            ngx.say("Set names error: ", err)
            return
        end
        
        -- 执行查询...
        
        -- 关闭连接
        local ok, err = db:close()
        if not ok then
            ngx.say("Failed to close connection: ", err)
        end
        
        ngx.say("MySQL operation completed successfully")
    }
}