Dealing with Quotes and Special Characters
Problem
Your datafile contains quoted values or escaped characters.
Solution
Tell LOAD
DATA
to be aware of quote and escape
characters so that it doesn’t load data values into the database
uninterpreted.
Discussion
The FIELDS
clause can specify other format options besides TERMINATED
BY
. By default, LOAD
DATA
assumes that values are unquoted, and it interprets the backslash
(\
) as an escape character for
special characters. To indicate the value-quoting character
explicitly, use ENCLOSED
BY
; MySQL will strip that character from the
ends of data values during input processing. To change the default
escape character, use ESCAPED
BY
.
The three subclauses of the FIELDS
clause
(ENCLOSED
BY
, ESCAPED
BY
, and TERMINATED
BY
) may be present in any order if you
specify more than one of them. For example, these FIELDS
clauses are equivalent:
FIELDS TERMINATED BY ',' ENCLOSED BY '"' FIELDS ENCLOSED BY '"' TERMINATED BY ','
The TERMINATED
BY
sequence can consist of multiple
characters. If data values are separated within input lines by
something like *@*
, you indicate
that like this:
FIELDS TERMINATED BY '*@*'
To disable escape processing entirely, specify an empty escape sequence:
FIELDS ESCAPED BY ''
When you specify ENCLOSED
BY
to indicate which quote character should be stripped from data values, it’s possible to include the quote character literally within data values by doubling it or by preceding it with the escape character. For example, if the quote ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.