MySQL Bugs: #17904: LOAD DATA INFILE stores HEX values as a string in VARBINARY columns

Bug #17904	LOAD DATA INFILE stores HEX values as a string in VARBINARY columns
Submitted:	3 Mar 2006 19:30	Modified:	5 Apr 2006 8:30
Reporter:	Chris Calender	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1, 5.0	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
When trying to load hex into a varbinary column using 'LOAD DATA INFILE', it gets stored as a string.  There has been a request to get this to not be stored as a string.

From the below, after you load this, you'll see that it gets stored as a string.

How to repeat:
create table t2 (id varbinary(30));

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t2
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

where data.txt is:
0xDA29D43DD827C023

Thank you for a feature request. The problem is that LOAD DATA INFILE treats each field value as string. It is documented even (http://dev.mysql.com/doc/refman/5.0/en/load-data.html):

"LOAD DATA INFILE regards all input as strings, so you cannot use numeric values for ENUM or SET columns the way you can with INSERT statements."

So, even this feature will be useful to simplify the load process, I am not sure it will be implemented in the foreseeable future. Moreover, there is a workaround that should solve the problem:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t2
  (@var1)
  SET column2 = unhex(substr(@var1,3));

Please, check. It works for INSERT statement, at least.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".