What is the best way to deal with white space in a mysql database?
What I'm talking about is trailing spaces in larger fields. We receive data
in fixed width format, so when you import it, you get a bunch of trailing
white spaces that take up space. Multiply these white spaces by 120million
records and it adds up.
I wrote a C program to parse out the file and eliminate white space and put
the data into a comma-delimited format. Now I have a new problem. I think
that there are some commas in the data and that is resulting in an
innacurate load (the commas in the data through off the LOAD INFILE command
So..if I stick with the fixed field, my load gets all records but I get a
bunch of extra data in the form of spaces.
if I parse the file and put it into comma-delimited, I miss some data
because of commas in the data.
Any tips are appreciated.