How to use BLOB objects-PolarDB(PolarDB)-阿里云帮助中心

MySQL drivers versions 5.1.x and 8.0.x have a defect in how they implement PreparedStatement.setBlob: unlike most other PreparedStatement parameters, setBlob() does not convert data through the connection's character encoding. When your application stores binary data such as images or videos using PreparedStatement.setBlob, the raw bytes may mismatch the character set negotiated for the connection, resulting in a syntax error.

This defect is in the MySQL driver itself — it occurs whether you connect to a MySQL server or a PolarDB-X instance. The available workarounds depend on your PolarDB-X version.

Workarounds by PolarDB-X version

Each version range below unlocks additional workarounds. Start with the section that matches your PolarDB-X version.

PolarDB-X version	Available workarounds
Earlier than V5.4.9	Hex conversion
V5.4.9 to earlier than V5.4.13	Hex conversion + `_binary` prefix
V5.4.13 and later	Hex conversion + `_binary` prefix + UTF-8 encoding

Fix blob syntax errors

PolarDB-X earlier than V5.4.9

In versions earlier than V5.4.9, binary data cannot be passed directly as a SQL argument because it does not match the connection's character set. Convert the byte array to a hexadecimal string before inserting it:

insert into t1 values (0xaabbccdd, x'aabbccdd');

Java — use `setBytes` instead of `setBlob` (recommended)

Replace PreparedStatement.setBlob with PreparedStatement.setBytes. The MySQL JDBC driver automatically converts the byte[] array to a hexadecimal string before sending the query.

Java — using a framework that controls the set method (for example, Hibernate)

If your framework does not allow you to choose which set method to use, submit a ticket to Alibaba Cloud technical support. You will receive a customized MySQL driver package that automatically performs the conversion.

Other languages

Convert the byte array to a hexadecimal string in your application before constructing the SQL statement.

PolarDB-X V5.4.9 to earlier than V5.4.13

All workarounds from the previous section apply. In addition, you can use the _binary prefix to mark binary literals explicitly.

Java — add the `_binary` prefix to the SQL statement

Modify the INSERT statement to include the _binary prefix before the parameter placeholder.

Before:

insert into t1 values (?)

After:

insert into t1 values (_binary?)

Java — upgrade to MySQL Connector/J V8.0.26

MySQL Connector/J V8.0.26 automatically adds the _binary prefix when you call PreparedStatement.setBlob, so no code change is required.

PolarDB-X V5.4.13 and later

PolarDB-X V5.4.13 and later are fully compatible with MySQL for binary data processing. All workarounds from the previous sections apply. In addition, you can set the character encoding to utf8 or utf8mb4 for the connection.

If you use GBK or other encoding schemes, syntax errors may still occur — even when connecting to an official MySQL server. To resolve these issues, connect your application to an official MySQL server and use the utf8 or utf8mb4 character set.

Java — add encoding parameters to the JDBC URL

useUnicode=true&characterEncoding=utf8

Other languages — execute a SET statement after connecting

set names utf8mb4;