将二进制数据插入MySQL(没有PreparedStatement的) [英] Inserting Binary data into MySQL (without PreparedStatement's)
问题描述
我试图在不使用准备语句的情况下将一些Binary数据插入MySQL数据库.原因是我将成千上万条语句连接到一个插入中,一次运行一次. (MySQL转储和导入的确切工作方式)
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:
插入my_table值(1,'g = F| }X ',2);
INSERT INTO my_table VALUES (1,'g=���F|�}X���',2);
插入my_table值(1,CAST('g = F| }X 'as BINARY),2);
INSERT INTO my_table VALUES (1,CAST( 'g=���F|�}X���' AS BINARY),2);
插入my_table值(1,CONVERT('g = F| }X ',BINARY),2);
INSERT INTO my_table VALUES (1,CONVERT( 'g=���F|�}X���', BINARY),2);
插入my_table值(1,BINARY'g = F| }X ',2)
INSERT INTO my_table VALUES (1,BINARY 'g=���F|�}X���',2)
我得到的错误是:
com.mysql.jdbc.MysqlDataTruncation:数据截断:第1行的'binary_data'列数据太长
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);
conn.createStatement().executeUpdate(sql);
PreparedStatement可以正常工作(但在这种情况下太慢了)
PreparedStatements work fine (but are too slow in this case)
数据库中的实际String I显示出一点区别:
The actual String I in the database displays a little differet:
g =÷óF|¸} X£ì[
g=÷óF|¸}X£ì[
二进制视图:67 3d 81 f7 19 f3 46 7c b8 7d 58 8c 10 a3 ec 5b
Binary View: 67 3d 81 f7 19 f3 46 7c b8 7d 58 8c 10 a3 ec 5b
Java字节:103、61,-127,-9、25,-13、70、124,-72、125、88,-116、16,-93,-20、91
Java Bytes: 103, 61, -127, -9, 25, -13, 70, 124, -72, 125, 88, -116, 16, -93, -20, 91
这与编码有关吗?
任何暗示都引起了人们的关注, 罗
Any hints much apprecaited, Ro
推荐答案
找到了解决方案....尽管我在任何地方都看不到文件.....
Found the solution .... Although not something I saw documented anywhere .....
您可以通过写入转换为十六进制并以0x开头的字节直接插入二进制数据
You can insert Binary data directly by writing the bytes converted to HEX and preceeded by 0x
例如:
INSERT INTO my_table VALUES (1,0x19c0300dc90e7cedf64703ed8ae8683b,2);
这篇关于将二进制数据插入MySQL(没有PreparedStatement的)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!