Re: blob data to infile
Posted by: Charles Kaminski
Date: April 28, 2017 02:53PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1057
April 27, 2017 12:07PM
524
April 27, 2017 12:13PM
Re: blob data to infile
635
April 28, 2017 02:53PM
521
May 04, 2017 10:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.