I'm trying to insert a table from file using 'LOAD DATA LOCAL INFILE'. I'm able to insert my other tables, but I'm having an issue with a table containing a blob column.
The blob value in file is stored as hex value and each column are separated by , character.
I'm following answer from here
Mysql server version is 5.6.44
This is the table definition
CREATE TABLE `tx_bin` (
`hash_id` bigint(20) unsigned NOT NULL,
`block_height` bigint(20) NOT NULL,
`binary` blob NOT NULL,
PRIMARY KEY (`hash_id`, `block_height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And this is the sample value in the file I'm using to insert into table
4,1,01000000010000000000000000000000000000000000000000000000000000000000000000ffffffff0e0420e7494d017f062f503253482fffffffff0100f2052a010000002321021aeaf2f8638a129a3156fbe7e5ef635226b0bafd495ff03afe2c843d7e3a4b51ac00000000
7,2,01000000010000000000000000000000000000000000000000000000000000000000000000ffffffff0e0432e7494d010e062f503253482fffffffff0100f2052a010000002321038a7f6ef1c8ca0c588aa53fa860128077c9e6c11e6830f4d7ee4e763a56b7718fac00000000
10,3,01000000010000000000000000000000000000000000000000000000000000000000000000ffffffff0e0486e7494d0151062f503253482fffffffff0100f2052a01000000232103f6d9ff4c12959445ca5549c811683bf9c88e637b222dd2e0311154c4c85cf423ac00000000
And this is the syntax that I used
LOAD DATA LOCAL INFILE '/work/bootstrap/10000/tx_bin'
INTO TABLE tx_bin FIELDS TERMINATED BY ','
(hash_id,block_height,@bin)
SET binary=UNHEX(@bin);
The error I'm getting
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary=UNHEX(@bin)' at line 1
If I remove the SET binary then the insert is successful (but without the binary data).
LOAD DATA LOCAL INFILE '/work/bootstrap/10000/tx_bin'
INTO TABLE tx_bin FIELDS TERMINATED BY ','
(hash_id,block_height,@bin);
Then I tried using SET but on other column, it's also successful.
LOAD DATA LOCAL INFILE '/work/bootstrap/10000/tx_bin'
INTO TABLE tx_bin FIELDS TERMINATED BY ','
(hash_id,@height,@bin)
SET block_height=@height;
If I add the blob column again after other column, it also give me the same error
LOAD DATA LOCAL INFILE '/work/bootstrap/10000/tx_bin'
INTO TABLE tx_bin FIELDS TERMINATED BY ','
(hash_id,@height,@bin)
SET block_height=@height, binary=UNHEX(@bin);
Any idea what I'm doing wrong?
BINARY is a reserved word in MySQL and needs to be quoted when used as a field name.
LOAD DATA LOCAL INFILE '/work/bootstrap/10000/tx_bin'
INTO TABLE tx_bin FIELDS TERMINATED BY ','
(hash_id,block_height,@bin)
SET `binary`=UNHEX(@bin);