将十六进制/二进制数据导入mysql [英] Import hex/binary data into mysql

问题描述

我需要将一组具有一个字段作为blob的大数据导入到mysql服务器(5.5)中.遵循SELECT INTO OUTFILE和LOAD DATA INFILE的示例，它几乎可以正常工作.但是，我有一个问题:BLOB文件中生成的数据实际上具有二进制表示形式.生成的文件如下所示(第三列表示BLOB之一):

I need to import into a mysql server (5.5) a set of large data having one field as a blob. Following the samples of SELECT INTO OUTFILE and LOAD DATA INFILE, it works [almost] fine. However, I have a problem: the generated data in the file for the BLOB has its actually binary representation. The generated file looks like this (3rd column represents the BLOB one):

1,1,"4Vÿ"
2,1,"ª»Ì"
3,1,"Ýîÿ"
4,1,"\"3"

，并且使用以下sql语句在这种情况下导入可以正常工作:

and the import works fine for this case with the following sql statement:

LOAD DATA INFILE 'C:/temp/mysql/mysqldump.1.txt'
INTO TABLE `test_table`
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
;

我的问题是这样的文本是偶然生成的.如果引号，换行符或其他内容出现，我将需要手动进行转义(即用反斜杠字符替换)，而我想跳过此步骤.

My problem is that the text is generated by chance like this. If quotes occur, or new-line, or whatever, I will need to do the escaping manually (i.e. replace them with backslash-char) and I would like to skip this step.

所以，我的问题是:有什么方法可以导入以下格式的文本文件(BLOB存储为六进制):

So, my question is: is there any way I can import a text file with the following format (BLOB stored as hexa):

1, 1, 0x123456FF
2, 1, 0xaabbcc
3, 1, 0xddeeff
4, 1, 0x112233

只有一条mysql语句?

with one single mysql statement?

更新:忘了提及-尝试直接使用LOAD DATA INFILE语句导入具有所需格式的文本文件，结果实际上是将文本'0x123456FF'存储在文件的BLOB文件中桌子.

Update: Forgot to mention - tried to import the text file with desired format directly with the LOAD DATA INFILE statement, and the result was actually storing the text '0x123456FF' in the BLOB file of the table.

推荐答案

您可以使用LOAD DATA INFILE的SET部分，而无需使用0x转义的东西:

You can use the SET part of LOAD DATA INFILE, and you don't need the 0x escaping stuff:

1, 1, 123456FF
2, 1, aabbcc
3, 1, ddeeff

然后将列分配给变量，然后将列设置为该变量的UNHEXed版本:

And then you assign the column to a variable, and then set the column to the UNHEXed version of that variable:

LOAD DATA INFILE 'file' INTO TABLE `table` (column1, column2, @var1)
SET column3 = UNHEX(@var1)

这篇关于将十六进制/二进制数据导入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！