statement.executeQuery()返回异常：JSON中逗号被替换为«符号

在MySQL Shell中执行查询，amulet字段的JSON格式数据显示正常：

mysql> select amulet from nrotienkiem.player where id = 1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| amulet                                                                                                                                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id":213,"point":1686124182092},{"id":214,"point":0},{"id":215,"point":0},{"id":216,"point":0},{"id":217,"point":0},{"id":218,"point":0},{"id":219,"point":1686137387317},{"id":522,"point":0},{"id":761,"point":0},{"id":762,"point":0}] |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

但通过Java代码查询时，结果中的逗号被替换为«符号：

root@NXT:/home/nxtpro# java -jar Test.jar
[{"id":213«"point":1686124182092}«{"id":214«"point":0}«{"id":215«"point":0}«{"id":216«"point":0}«{"id":217«"point":0}«{"id":218«"point":0}«{"id":219«"point":1686137387317}«{"id":522«"point":0}«{"id":761«"point":0}«{"id":762«"point":0}]

使用的Java代码：

public class Test {

    public static void main(String[] args) throws IOException, SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nrotienkiem", "root", "root");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM `player` WHERE `account_id`=1;");
        if (rs != null && rs.next()) {
            System.out.println(rs.getString("amulet"));
        }
    }
}

系统环境：

• Ubuntu Server 20.04
• mysql Ver 14.14 Distrib 5.6.46, for linux-glibc2.12 (x86_64)
• Java 8
• mysql-connector-java 5.1.23

1. 强制指定JDBC连接字符集 #

在JDBC URL中添加字符集参数，确保与数据库字符集一致，修改连接代码：

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nrotienkiem?useUnicode=true&characterEncoding=utf8", "root", "root");

若数据库使用utf8mb4字符集，将characterEncoding=utf8替换为characterEncoding=utf8mb4即可。

2. 升级mysql-connector-java版本 #

当前使用的5.1.23版本较旧，存在字符集处理相关bug，建议升级至Java 8兼容的最新5.x版本（如5.1.49），或8.x版本（注意8.x版本需调整驱动类与URL参数）：

• 驱动类改为com.mysql.cj.jdbc.Driver
• URL需添加时区参数：jdbc:mysql://localhost:3306/nrotienkiem?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC

3. 检查数据库与表的字符集设置 #

执行以下SQL确认字符集配置是否统一：

-- 检查数据库字符集
SELECT schema_name, default_character_set_name FROM information_schema.schemata WHERE schema_name = 'nrotienkiem';
-- 检查player表字符集
SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'nrotienkiem' AND table_name = 'player';
-- 检查amulet字段字符集
SELECT column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema = 'nrotienkiem' AND table_name = 'player' AND column_name = 'amulet';

确保数据库、表、字段的字符集统一为utf8或utf8mb4，避免转换过程中出现乱码替换。

4. 直接获取字节数组再解码 #

若以上方法无效，可绕过JDBC驱动的字符处理逻辑，直接读取原始字节再解码：

import java.nio.charset.StandardCharsets;

// ... 省略其他代码
if (rs != null && rs.next()) {
    byte[] bytes = rs.getBytes("amulet");
    String amulet = new String(bytes, StandardCharsets.UTF_8);
    System.out.println(amulet);
}

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