嗨
Mysql编码设置如下:
MySQL []> show global variables like '%char%';
+--------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/oscar-5.6.10a.200187.0/share/charsets/ |
+--------------------------+--------------------------------------------------+
数据库里有两条记录，分别是中文和印地语
+------------+----------------+-----------------------------------------------------------------------------------------------+
| command_id | property_key | property_value |
+------------+----------------+-----------------------------------------------------------------------------------------------+
| lk_test_1 | msgbox_content | इनका पागलपन देख आपको यकीन नहीं होगा |
| lk_test_1 | msgbox_title | 我的 |
+------------+----------------+-----------------------------------------------------------------------------------------------+
通过mysql客户端链接，设置编码格式为utf8，可以正常正常显示结果
mysql -u root -p -h ***** --default-character-set=utf8
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4837
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL []> show variables like '%char%';
+--------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/oscar-5.6.10a.200187.0/share/charsets/ |
+--------------------------+--------------------------------------------------+
8 rows in set (0.29 sec)
MySQL []> show global variables like '%char%';
+--------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/oscar-5.6.10a.200187.0/share/charsets/ |
+--------------------------+--------------------------------------------------+
说明我通过设置mysql session级别的编码可以正常读取数据。但是我通过resty-mysql 读取却是乱码，以下是代码：
local cjson = require("cjson.safe")
local mysql = require "resty.mysql"
local db, err = mysql:new()
db:set_timeout(100000)
local ok, err, errcode, sqlstate = db:connect{
host = "********",
port = 3306,
database = "******",
user = "root",
password = "12345678",
charset = "utf8",
max_packet_size = 1024 * 1024,
}
if not ok then
ngx.say("failed to connect: ", err, ": ", errcode, " ", sqlstate)
return
end
local res, err, errcode, sqlstate = db:query("SELECT * FROM * WHERE property_value IS NOT NULL and command_id = 'lk_test_1' and (property_key='msgbox_content' or property_key='msgbox_title')")
if not res then
ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".")
return
end
ngx.say(cjson.encode(res))
--输出结果：
[{"command_id":"lk_test_1","property_value":"???? ?????? ??? ???? ???? ???? ????","property_key":"msgbox_content"},{"command_id":"lk_test_1","property_value":"??","property_key":"msgbox_title"}]
请问如何解决这个问题？
