Inserting Binary data into MySQL (without PreparedStatement's)

Question

I'm trying to insert some Binary data into a MySQL database without using prepared statements. The reason for this is that I concatenate thousands of statements into a single insert an run that once. (Exactly how the MySQL dump & import works)

I have tried the following statements, but the are all failing:

INSERT INTO my_table VALUES (1,'g=��F|�}X��',2);

INSERT INTO my_table VALUES (1,CAST( 'g=��F|�}X��' AS BINARY),2);

INSERT INTO my_table VALUES (1,CONVERT( 'g=��F|�}X��', BINARY),2);

INSERT INTO my_table VALUES (1,BINARY 'g=��F|�}X��',2)

The error I get is:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'binary_data' at row 1

The code I use to execute the statement is simply:

conn.createStatement().executeUpdate(sql);

PreparedStatements work fine (but are too slow in this case)

The actual String I in the database displays a little differet:

g=÷óF|¸}X£ì[

Binary View: 67 3d 81 f7 19 f3 46 7c b8 7d 58 8c 10 a3 ec 5b

Java Bytes: 103, 61, -127, -9, 25, -13, 70, 124, -72, 125, 88, -116, 16, -93, -20, 91

Could this be something to do with Encoding ?

Any hints much apprecaited, Ro

Binary data can be inserted only through `PreparedStatement` OR `CallableStatement` and not possible using simple `Statement`. — Ravindra Gullapalli, Apr 13 '12 at 10:58
You do know that the "optimization" you did is probably worthless? The point of prepared statements is that they're "precompiled" by MySQL and you just feed MySQL the parameters. Whether you concatenate statements or not, you're sending thousands of values anyway. And naturally, this turned out to be a problem since you cannot easily insert binary data. — N.B., Apr 13 '12 at 10:59
How are you generating your `INSERT` statements? Encoding likely matters, but you also have to worry about correct escaping (e.g. if the binary data has a `'` in it). Also do you check your JDBC [connection parameters](http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html), such as `useUnicode` and `characterEncoding`? — ɲeuroburɳ, Apr 16 '12 at 14:17

score 16 · Accepted Answer · answered Apr 23 '12 at 15:10

16

Found the solution .... Although not something I saw documented anywhere .....

You can insert Binary data directly by writing the bytes converted to HEX and preceeded by 0x

For example:

INSERT INTO my_table VALUES (1,0x19c0300dc90e7cedf64703ed8ae8683b,2);

answered Apr 23 '12 at 15:10

Ro.

1,357
4
13
25

2

I didn't realise mysql automatically decodes hex like that, thanks for this helpful post. – fabspro Sep 16 '12 at 09:49
1

The notation x'19c0300dc90e7cedf64703ed8ae8683b' works, too. This is all documented in §9.1.4 of the MySQL Reference Manual. – olefevre Oct 12 '12 at 03:54

score 3 · Answer 2 · edited May 23 '17 at 12:17

3

Have you tried to use PreparedStatement in Batch mode?

    PreparedStatement pStmt = ...;
    while(...) { // use for or whatever loop
        pStmt.clearParameters();
        pStmt.setBinaryStream(2, ...);
        pStmt.addBatch();
    }
    pStmt.executeBatch();

For more detailed information on how you can make Batches efficient with JDBC and MySQL have a look here: MySQL and JDBC with rewriteBatchedStatements=true

edited May 23 '17 at 12:17

Community

1
1

answered Apr 13 '12 at 11:20

Angelo Fuchs

9,825
1
35
72

Yep - Already Batching 2000 Statements at a time. Trying to write something faster than standard mysql importing (with extra custom features) Current performance is 40% slower than standard import, even though I'm processing in parallel - The tables with binary columns (which I'm processing using PreparedStatements are the slowest) – Ro. Apr 13 '12 at 11:42
@Ro. So, then PreparedStatement is not slower than Stamement. If it is, you should ask a question on why it is, because it shouldn't be. – Angelo Fuchs Apr 13 '12 at 11:46
Mysql connector j has `client-side` prepared statement. It has a rewrite batch param which allow rewrite batch insert statement rewritten into on statement. – jilen Oct 28 '16 at 08:57
@jilen I did not understand your comment. Care to give an example (feel free to edit the answer if you have additional insight or post an own answer with it) – Angelo Fuchs Oct 28 '16 at 10:19
@AngeloFuchs http://stackoverflow.com/questions/26307760/mysql-and-jdbc-with-rewritebatchedstatements-true I am refering to this. – jilen Oct 28 '16 at 10:29
@jilen Ohh! Nice! Thank you! I've amended my answer. – Angelo Fuchs Oct 28 '16 at 10:33

score 2 · Answer 3 · answered Apr 13 '12 at 11:12

2

A prepared statement is undoubtedly the fastest approach. The reason that you find it too slow might be because you are not using it inside a transaction. You might be able to do something cute with base 64, but it would be very slow.

answered Apr 13 '12 at 11:12

Philip Sheard

5,789
5
27
42

Inserting Binary data into MySQL (without PreparedStatement's)

3 Answers3

Linked