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
– Ravindra Gullapalli, Commented Apr 13, 2012 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.
– N.B., Commented Apr 13, 2012 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, such as useUnicode and characterEncoding? — ɲeuroburɳ
– ɲeuroburɳ, Commented Apr 16, 2012 at 14:17

Ro. · Accepted Answer · 2012-04-23 15:10:19Z

18

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, 2012 at 15:10

Ro.

1,3774 gold badges14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fabspro Over a year ago

I didn't realise mysql automatically decodes hex like that, thanks for this helpful post.

olefevre Over a year ago

The notation x'19c0300dc90e7cedf64703ed8ae8683b' works, too. This is all documented in §9.1.4 of the MySQL Reference Manual.

Community · Accepted Answer · 2017-05-23 12:17:11Z

4

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, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 13, 2012 at 11:20

Angelo Fuchs

9,9911 gold badge39 silver badges73 bronze badges

6 Comments

Ro. Over a year ago

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)

Angelo Fuchs Over a year ago

@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.

jilen Over a year ago

Mysql connector j has client-side prepared statement. It has a rewrite batch param which allow rewrite batch insert statement rewritten into on statement.

Angelo Fuchs Over a year ago

@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)

jilen Over a year ago

@AngeloFuchs stackoverflow.com/questions/26307760/… I am refering to this.

|

Philip Sheard · Accepted Answer · 2012-04-13 11:12:00Z

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, 2012 at 11:12

Philip Sheard

5,8335 gold badges30 silver badges42 bronze badges

Collectives™ on Stack Overflow

Inserting Binary data into MySQL (without PreparedStatement's)

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related