如何通过 JDBC 接口将 UTF-8 字符串正确写入 MySQL

java mysql utf-8 jdbc

2022-09-03 04:17:13

连接到数据库：

public DBSource(ConnectionInfo ci) throws
        ClassNotFoundException, InstantiationException,
        IllegalAccessException, SQLException
{
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String dbPath = String.format(
        "jdbc:mysql://%s:%d/%s?user=%s&password=%s&characterEncoding=utf-8&" + 
        "useUnicode=true", ci.host, ci.port, ci.dbName, ci.user, ci.password);
    conn = java.sql.DriverManager.getConnection(dbPath);
    prepareTables();
}

表创建代码：

private void prepareTables() throws SQLException
{
    java.sql.Statement stat = conn.createStatement();

    String query = "set names utf8";
    stat.execute(query);

    query = "set character set utf8";
    stat.execute(query);

    query = "show variables like '%char%'";
    stat.execute(query);
    java.sql.ResultSet rs = stat.getResultSet();
    while (rs.next())
    {
        String k = rs.getString(1);
        String v = rs.getString(2);
        System.out.println(k + " - " + v);
    }
    
    query = "drop table if exists clt";
    stat.execute(query);
    query = "create table clt"
            + "("
            + "  id bigint not null"
            + ", text varchar(50) not null"
            + ") default character set utf8";
    stat.execute(query);
}

行插入：

public void visit(Insert i) throws SQLException
{
    String query = "insert into clt"
            + " (id, text) values (?, ?)";
    java.sql.PreparedStatement stmt = conn.prepareStatement(query);
    if (i.rowData.id == 12656697)
    {
        String toOut = "<<< " + Long.toString(i.rowData.id) + " - " + i.rowData.text;
        System.out.println(toOut);
    }
    int it = 0;
    stmt.setLong(++it, i.rowData.id);
    stmt.setString(++it, i.rowData.text);
    stmt.execute();
    stmt.close();
}

检查数据：

 public void checkText() throws SQLException
{
    java.sql.Statement stmt = conn.createStatement();
    String query = "select id, text from clt where id = '12656697'";
    stmt.execute(query);
    java.sql.ResultSet rs = stmt.getResultSet();
    while (rs.next())
    {
        String k = rs.getString(1);
        String v = rs.getString(2);
        String toOut = ">>> " + k + " - " + v;
        System.out.println(toOut);
    }
}

输出：

character_set_client - utf8
character_set_connection - latin1
character_set_database - latin1
character_set_filesystem - binary
character_set_results - utf8
character_set_server - utf8
character_set_system - utf8
character_sets_dir - /usr/share/mysql/charsets/

<<< 12656697 - Апарати
>>> 12656697 - ???????

问题：在表中，我在文本字段中有“???????????”符号。

预期字符串是： Апарати
结果： ???????

这是某种魔法？

我解决了一个问题...但是如果有人能向我解释，仍然会很感激。

所以。

我添加到我的/etc/mysql/my.cnf行中，由Costis Aivalis
建议的结果相同
我从代码中删除了行：
query = “set character set utf8”;
stat.execute（query）;

它正在工作!!!:)

答案 1

确保您的MySQL配置编码定义正确。使用以下命令检查您的设置和修改的正确性：

show variables like 'character%';

和show variables like 'collation%';

将这些行添加到 my.cnf 或 my.ini：

对于MySQL 5.1.nn及更高版本5.5.29，您只需要以下两行：

[mysqld]
character-set-server = utf8
character-set-filesystem = utf8

对于 MySQL 5.0.nn 及更早版本，请使用以下设置：

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set=utf8
character-set-server=utf8

使用MySQL-Workbench进行设置可能更方便。版本5 +非常出色。

enter image description here

在您的Java程序中，连接如下：

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8","user","passwd");

答案 2

如果得到，请添加参数而不是。请参阅以下示例。?????????characterEncoding=utf-8useUnicode=true

对于英文文本：

String url =  "jdbc:mysql://localhost:" + port + "/DBName?useUnicode=true&...";

对于俄语语言文本：

String url = "jdbc:mysql://localhost:" + port + "/DBName&characterEncoding=utf-8&...";