CODEWITHSUNDEEP
mysqlsqlbashunix
ktm5124
ktm5124

Reputation: 12123

Loading data into MySQL: How to deal with backslashes?

I downloaded a tab-delimited file from a well-known source and now want to upload it into a MySQL table. I am doing this using load data local infile.

This data file, which has over 10 million records, also has the misfortune of many backslashes.

$ grep '\\' tabd_file.txt | wc -l
223212

These backslashes aren't a problem, except when they come at the end of fields. MySQL interprets backslashes as an escape character, and when it comes at the end of the field, it messes up the next field, or possibly the next row.

In spite of these backslashes, I only received 6 warnings from MySQL when loading it into a table. In each of these warnings, a row doesn't have the proper number of columns precisely because the backslash concatenated two adjacent fields in the same row.

My question is, how to deal with these backslashes? Should I specify load data local infile [...] escaped by '' to remove any special meaning from them? Or would this have unintended consequences? I can't think of a single important use of an escape sequence in this data file. The actual tabs that terminate fields are "physical tabs", not "\t" sequences.

Or, is removing the escape character from my load command bad practice? Should I just replace every instance of '\' in the file with '\\'?

Thanks for any advice :-)

Upvotes: 6

Views: 8543

Answers (1)

koriander
koriander

Reputation: 3258

If you don't need the escaping, then definitely use ESCAPED BY ''.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

"If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur. "

Upvotes: 19

Related Questions