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

[3 Mar 2006 19:30] Chris Calender
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
[5 Mar 2006 8:30] Valeriy Kravchuk
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.
[5 Apr 2006 23:00] Bugs System
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".