MySQL Forums
Forum List  »  Newbie

LOAD DATA INFILE and binary data
Posted by: Nick B
Date: November 17, 2009 11:33PM

I'm trying to populate a large database (~1 billion rows). Each row contains a couple of dozen integer values, and a handful of small (10-20 byte) binary fields.

Of course, I'd like to get this done as quickly as possible, and from what I've heard, LOAD DATA INFILE is the fastest way to populate a table. But I'm having a couple of problems with this.

1) Is there any way to read in binary integer values, rather than string representations? e.g. Represent a TINYINT as the byte 0x7B in the file, instead of the string "123"? These numbers exist in the program as binary values, and are stored in the database in the same format, so converting 20 billion of them to strings and back again seems a little absurd. I can do it by calling the column a BINARY(1) rather than a TINYINT, but that's less than ideal...

2) Is there any way to load a VARBINARY field? Because they contain raw binary data, they might be packed full of delimiters already. And obviously, the boundaries can't be deduced from the field width as they are with a fixed BINARY.

Any help with this, or other ideas on how to get the data in quickly, would be greatly appreciated. 1,000,000,000 inserts is just going to take way too long...

Options: ReplyQuote


Subject
Written By
Posted
LOAD DATA INFILE and binary data
November 17, 2009 11:33PM
November 19, 2009 10:29AM
November 23, 2009 08:20AM


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.