Re: blob data to infile

[Charles Kaminski]

Below is a script you can run to check for your self or to make suggestions:

# Create the schema and tables

DROP SCHEMA IF EXISTS sandbox_binary;

CREATE SCHEMA sandbox_binary
DEFAULT Character SET = binary;

CREATE TABLE `sandbox_binary`.`blob_table` (
`transaction_id` varchar(20) NOT NULL,
`binary_data` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

# Add some Binary Data
INSERT INTO `sandbox_binary`.`blob_table`
(`transaction_id`,
`binary_data`)
VALUES
('123456789A',
unhex('CF090000')
);

# Confirm that the binary data was written as requested
SELECT transaction_id,
hex(binary_data),
binary_data
FROM sandbox_binary.blob_table
WHERE transaction_id = '123456789A';


SET character_set_client = binary;
SET character_set_connection = binary;
SET character_set_database = binary;
SET character_set_filesystem = binary;
SET character_set_results = binary;
SELECT binary binary_data
FROM sandbox_binary.blob_table
WHERE transaction_id = '123456789A'
INTO outfile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/binary_out.txt"
CHARACTER SET binary
FIELDS TERMINATED BY '~~|~~' lines terminated by '~~^~~';

# From here, read the outfile with a hex editor
# CF090000 <> CF095C305C30