java - 通过 java.sql.PreparedStatement.setBytes 进行 BINARY 比较的空结果集
问题描述
我有带有自定义 ORM 的 Percona Mysql 服务器和 Java 客户端。在数据库中,我有表:
CREATE TABLE `PlayerSecret` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`secret` binary(16) NOT NULL,
`player_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `PlayerSecret_secret_unique` (`secret`),
KEY `PlayerSecret_player_id` (`player_id`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
我发现SELECT PlayerSecret.player_id FROM PlayerSecret WHERE PlayerSecret.secret = ?
当方法提供参数时,查询返回一个空的结果集java.sql.PreparedStatement#setBytes
,并且按预期工作 java.sql.PreparedStatement#setBinaryStream
。我启用了 mysql 常规日志,发现在这个日志中两个查询是相同的,我在十六进制模式下检查了这个。
在一般日志它看起来像:
SELECT PlayerSecret.player_id FROM PlayerSecret WHERE PlayerSecret.secret = '<96>R\Ø8üõA\í¤Z´^E\Ô\ÊÁ\Ö'
以十六进制模式从通用日志中查询参数:
2796 525c d838 fcf5 415c eda4 5ab4 055c d45c cac1 5cd6 27
数据库中的值:
mysql> select hex(secret) from PlayerSecret where id=109;
+----------------------------------+
| hex(secret) |
+----------------------------------+
| 9652D838FCF541EDA45AB405D4CAC1D6 |
+----------------------------------+
1 row in set (0.00 sec)
问题是我的 ORM 通过setBytes
方法执行此查询,我认为这是BINARY
数据类型的正确方法,但它不起作用。
编码设置的一部分my.cnf
(也许很重要):
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
general_log = on
general_log_file=/var/log/mysql/mysqld_general.log
require_secure_transport = ON
init-connect = SET collation_connection = utf8mb4_unicode_ci
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Java代码:
var uuid = UUID.fromString("9652d838-fcf5-41ed-a45a-b405d4cac1d6");
var array = ByteBuffer.allocate(16).putLong(uuid.getMostSignificantBits()).putLong(uuid.getLeastSignificantBits()).array();
// works
stmt.setBinaryStream(index, new ByteArrayInputStream(array));
// don't works
stmt.setBytes(index, array);
我不明白这两种情况有什么区别,以及如何解决这个问题setBytes
。也许有人可以澄清这一点或指出我的重要部分/地方?
我的环境:
- 开放jdk 11
- HicariCP 3.1.0
- MySQL 连接器/J 8.0.13
- Percona 5.7.24-26-log Percona 服务器 (GPL),版本 '26',修订版 'c8fe767'
解决方案
最后我想通了。问题出在character_set_client=utf8
而不是utf8mb4
.
此查询显示了预期值和实际线程值之间的差异(认为这是非常方便的查询):
SELECT VARIABLE_NAME, gv.VARIABLE_VALUE 'Global', tv.VARIABLE_VALUE 'Thread value', THREAD_ID, PROCESSLIST_ID
FROM performance_schema.global_variables gv
JOIN performance_schema.variables_by_thread tv USING (VARIABLE_NAME)
JOIN performance_schema.threads USING(THREAD_ID)
WHERE gv.VARIABLE_VALUE <> tv.VARIABLE_VALUE ;
+-----------------------+--------------------+--------------------+-----------+----------------+
| VARIABLE_NAME | Global | Thread value | THREAD_ID | PROCESSLIST_ID |
+-----------------------+--------------------+--------------------+-----------+----------------+
| autocommit | ON | OFF | 82 | 56 |
| character_set_client | utf8mb4 | utf8 | 82 | 56 |
| character_set_results | utf8mb4 | | 82 | 56 |
当我替换init-connect = SET collation_connection = utf8mb4_unicode_ci
为init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
问题my.cnf
消失并且查询setBytes
开始按预期工作时。
为什么它适用于setBinaryStream
并且不适用setBytes
- 因为在第一种情况下适用此代码com.mysql.cj.ServerPreparedQueryBindings#setBinaryStream(int, java.io.InputStream, int)
:
@Override
public void setBinaryStream(int parameterIndex, InputStream x, int length) {
if (x == null) {
setNull(parameterIndex);
} else {
ServerPreparedQueryBindValue binding = getBinding(parameterIndex, true);
this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_BLOB, this.numberOfExecutions));
binding.value = x;
binding.isLongData = true;
binding.bindLength = this.useStreamLengthsInPrepStmts.getValue() ? length : -1;
}
}
这里的重要部分是binding.resetToType(MysqlType.FIELD_TYPE_BLOB
- 驱动程序注释 mysql 该数据是BLOB
在第二种情况下com.mysql.cj.ServerPreparedQueryBindings#setBytes(int, byte[])
包含:
@Override
public void setBytes(int parameterIndex, byte[] x) {
if (x == null) {
setNull(parameterIndex);
} else {
ServerPreparedQueryBindValue binding = getBinding(parameterIndex, false);
this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_VAR_STRING, this.numberOfExecutions));
binding.value = x;
}
}
MysqlType.FIELD_TYPE_VAR_STRING
意味着这不是简单的字节,而是某种编码的字符串(带有某种排序规则)。我真的不知道为什么驱动程序会为字节设置这种类型的数据——这个问题对我来说仍然是开放的。