ResultSet.updateRow() 产生 "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'"。

Question

我有一张表格，其中name是LATIN1编码，其余部分是UTF8编码。

CREATE TABLE `test_names` (
  `name` varchar(500) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `other_stuff_1` int DEFAULT NULL,
  `other_stuff_2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我在Java中遇到了以下问题：

我使用了"SELECT ... FOR UPDATE"，然后在其ResultSet上调用"updateInt(2, 1)"和"updateRow()"，结果得到了"Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'"的错误提示。

如何在不更改表/连接字符集的情况下解决这个问题？

非常感谢。

--- 更新 ---

我使用了"SELECT name, other_stuff_1 FROM test_names LIMIT 1 FOR UPDATE;"，连接字符串为"DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + db + "?allowMultiQueries=true", user, password);"。

确切的堆栈跟踪如下：

java.sql.SQLException: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2441)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
    at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2405)

- dotwin

---

请提供完整的SELECT语句，以及事务中的其他语句。请提供连接到MySQL时使用的连接参数。 - Rick James

更新完毕。感谢您的帮助，@RickJames。 - dotwin

将SELECT语句更改为SELECT name, other_stuff_1 FROM test_names LIMIT 1 ORDER BY name COLLATE latin1_bin FOR UPDATE。我没有测试这个语句，它需要MySQL 5.7或更高版本。 - Beck Yang

非常感谢您的回答，@beckyang。我尝试了 ORDER BY name COLLATE latin1_bin LIMIT 1 FOR UPDATE（ORDER BY 在 LIMIT 前面），但不幸的是仍然收到 java.sql.SQLException: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>' 的错误提示。 - dotwin

可能是Connector/J中的一个bug——您似乎正在使用v5.1.29，而当前的GA版本是v5.1.38。您能否尝试使用最新版本？ - eggyal

您可以在这里查看：https://dev59.com/PnA75IYBdhLWcg3w7tx6#21061305 - Adi Prasetyo

Answer 1

从我的角度来看，我可以给你一些建议。

1. 首先更新您的Connector/J到最新版本。
2. 运行此查询。

SET NAMES='utf8'

3. 在您的JDBC连接字符串中添加&characterEncoding=UTF-8。希望您已经完成了这一步。

4. 使用convert()进行插入或更新，并使用cast()进行选择查询。有关更多详细信息，请参见http://dev.mysql.com/doc/refman/5.7/en/charset-convert.html

5. 对于与"Illegal mix of collations"相关的问题，您可以查看Troubleshooting "Illegal mix of collations" error in mysql进行跟进。

- SkyWalker

Answer 2

问题在于您正在尝试从一个表中选择具有不同字符集的不同列。

您必须使用CONVERT()将查询中具有不同字符集的列转换为正确的字符集。

http://dev.mysql.com/doc/refman/5.7/en/charset-convert.html

在您的情况下，您应该修改您的查询为：

SELECT CONVERT(name USING latin1), other_stuff_1 FROM test_names LIMIT 1 FOR UPDATE;

- Andras Szell

---

非常感谢您的回答。使用CONVERT无法奏效，因为它会使ResultSet不可更新；我不能再更新other_stuff_1了。错误信息显示为：com.mysql.jdbc.NotUpdatable: Result Set not updatable (references computed values or doesn't reference any columns or tables).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details. - dotwin

ResultSet 是使用 ResultSet.TYPE_FORWARD_ONLY 和 ResultSet.CONCUR_UPDATABLE 创建的。错误出现是由于转换引起的。但这绝对是正确的方法。我需要以某种方式告诉 Java，这个数据库中的 PK 是 LATIN1。 - dotwin

Answer 3

在这种情况下，如果表中的编码不统一（例如主键是latin1但其他列是utf8），我认为您无法使用可更新的结果集。

正如Andras所指出的那样，您可以在SQL端转换编码，但然后您将无法更新结果集。

为什么不直接使用executeUpdate（...）更新表格呢？ 您可以使用简单的select缩小目标行，然后迭代生成的主键列表并调用executeUpdate。

对于我来说，这对于混合编码列是有效的。 只是一个例子：

    conn.setAutoCommit(false);
    ResultSet rs = st.executeQuery("SELECT name other_stuff_1 FROM test_names");
    List<String> keys = new ArrayList<String>();
    while(rs.next()) {
        keys.add(rs.getString(1));
    }
    rs.close();
    for(String key : keys) {
        st.executeUpdate("update test_names set other_stuff_1='"+key.length()+"', other_stuff_2='" + key.toUpperCase() + "' where name='" + key + "'");
    }
    conn.commit();

- riskop