java 如何解决“非法混合排序规则”SQLException？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11207027/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Howto resolve "Illegal mix of collations" SQLException?
提问by Adamsan
I looked in the mysql reference, and here, in stackoverflow, and looks like a lot of people are having difficulties with character encoding, but I couldn't find a clear answer to this problem:
我查看了 mysql 参考资料，在这里，在 stackoverflow 中，看起来很多人在字符编码方面遇到困难，但我找不到这个问题的明确答案：
A java program is using a mysql database, and when there are special characters in the query like ?,? (but á,é,í,ó,ú works), it throws an SQLException:
一个java程序正在使用mysql数据库，当查询中有特殊字符时，如？，？（但 á,é,í,ó,ú 有效），它会抛出 SQLException：
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection(
"jdbc:odbc:<database>", "<user>", "<pass>"
);
PreparedStatement stmt = conn.prepareStatement(
" select username, priority " +
" from users " +
" where username like ?");
//" where username like ? collate latin2_general_ci");
stmt.setString(1, "Ern?");
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getString("username") + " " + rs.getInt("priority"));
}
Exception:
例外：
Exception in thread "main" java.sql.SQLException:
[MySQL][ODBC 5.1 Driver][mysqld-5.1.63-0ubuntu0.10.04.1]
Illegal mix of collations (latin2_hungarian_ci,IMPLICIT)
and (latin1_swedish_ci,COERCIBLE) for operation 'like'
The table structure is:
表结构为：
CREATE TABLE `users` (
`username` varchar(45) COLLATE latin2_hungarian_ci NOT NULL,
`password` varchar(45) COLLATE latin2_hungarian_ci NOT NULL,
`priority` tinyint(4) NOT NULL,
`idCimlistaFK` int(10) unsigned DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`username`),
KEY `fk_users_tbl_Cimlista1` (`idCimlistaFK`),
CONSTRAINT `fk_users_tbl_Cimlista1` FOREIGN KEY (`idCimlistaFK`)
REFERENCES `tbl_cimlista` (`id_Cimlista`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci
When I tried to force a collation like in the commented line:
当我尝试像注释行那样强制排序时：
" where username like ? collate latin2_general_ci");
It also throws an exception:
它也会抛出异常：
COLLATION 'latin2_general_ci' is not valid for CHARACTER SET 'latin1'
Does anyone know, how to solve this (possibly within java)? (I would prefer not to change the database, because it's a legacy system.)
有谁知道，如何解决这个问题（可能在 java 中）？（我不想更改数据库，因为它是一个遗留系统。）
UPDATE:
更新：
I used the following url, as suggested by Rahul Agrawal:
根据 Rahul Agrawal 的建议，我使用了以下网址：
"jdbc:mysql://localhost:3306/database?characterEncoding=latin2"
It did work, however, I noticed a sideeffect. When I updated and inserted data with the testserver, wich runs mysql on a win XP machine, it went fine. But when I updated and inserted the data to the production server running Ubuntu, I did get rows, wich had ?-signs instead ?,?-letters. I resolved this with a workaround since, but it would be nice to see and understand, what went wrong, what are the issues here.
它确实有效，但是，我注意到了一个副作用。当我使用 testserver 更新并插入数据时，它在 Win XP 机器上运行 mysql，一切顺利。但是当我更新并将数据插入到运行 Ubuntu 的生产服务器时，我确实得到了行，其中有 ?-signs 而不是 ?,?-letters。从那以后，我通过一种解决方法解决了这个问题，但很高兴看到并理解出了什么问题，这里有什么问题。
回答by Rahul Agrawal
You need to use UTF-8 charset
您需要使用 UTF-8 字符集
In Connection URL try this
在连接 URL 中试试这个
jdbc:mysql://localhost:3306/testdb?characterEncoding=utf8
Database CHARSET=utf8
数据库字符集=utf8
Database Collation = utf8_general_ci
数据库整理 = utf8_general_ci
回答by Pritam Banerjee
Not always you can change the database columns for obvious reasons. At those point of times regex can come to your rescue.
出于显而易见的原因，您并不总是可以更改数据库列。在那个时候，正则表达式可以帮助您。
You can use the following:
您可以使用以下内容：
String output = input.replaceAll("[^\u0020-\u007e\u00a0-\u00ff]",
replaceWithWhateverYouWant);
