java - 如何使用 Java 在 MySQL 中进行不区分大小写的选择

Question

我需要帮助在 MySQL 数据库中进行不区分大小写的选择。
我已经找到了在 MySQL 中执行此操作的解决方案，但无法将其转换为 Java。

这是 MySQL:

SELECT * FROM allclient where name="Zdi0" collate utf8_bin

这有效，但是在java中:

PreparedStatement pstm = mysql.Pstm("SELECT id,name FROM allclient WHERE   user =  ? collate utf8_bin  AND pass = ? ");
pstm.setString(1, this.name);
pstm.setString(2, this.pass);
ResultSet rs = pstm.executeQuery();

生成此异常:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
    at Luncher4.run(Luncher4.java:96)

为什么不起作用？

Answer 1

您可以使用MySQL中不区分大小写的查询作为示例:

SELECT * FROM `users` WHERE UPPER(username) = 'ZDI';
SELECT * FROM `users` WHERE LOWER(username) = 'zdi';