SQL ESCAPED

What is SQL ESCAPED BY in MySQL?

Defines the escape character used in MySQL bulk I/O operations such as LOAD DATA INFILE and SELECT ... INTO OUTFILE.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ESCAPED: MySQL, MariaDB

SQL ESCAPED Full Explanation

In MySQL, the keyword ESCAPED (used as ESCAPED BY) lets you specify a single-byte character that precedes special sequence markers (\n, \t, \0, etc.) when data is imported with LOAD DATA INFILE or exported with SELECT ... INTO OUTFILE. By default, MySQL uses the backslash (\\) as the escape character. Setting ESCAPED BY overrides this default, allowing you to avoid conflicts when the backslash already appears frequently in your data or when you need to turn escaping off entirely (ESCAPED BY '' disables escaping). The chosen character tells MySQL how to interpret subsequent bytes when reading a file and which prefix to write when producing an output file. Only one character is permitted, and it must not be the same as TERMINATED BY or ENCLOSED BY characters. ESCAPED BY applies only to text (not BLOB/BINARY) fields and has no effect on the LIKE ... ESCAPE clause, which is a different SQL feature.

SQL ESCAPED Syntax

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE tbl_name
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\';

SELECT col1, col2
INTO OUTFILE 'file_path'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
FROM tbl_name;

SQL ESCAPED Parameters

Example Queries Using SQL ESCAPED

-- Import a CSV where backslash is used literally, so set a different escape character
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '`';

-- Export data with no escaping at all
SELECT id, name
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY ''
FROM users;

Expected Output Using SQL ESCAPED

First query loads rows into users, interpreting sequences like `n` as newline and leaving literal backslashes untouched
Second query writes users
csv without adding escape prefixes

Use Cases with SQL ESCAPED

Importing data files that already contain many backslashes
Exporting files for downstream systems that do not recognize MySQL backslash escapes
Disabling escaping to produce fully literal output
Choosing a rare character as the escape prefix to minimize collisions

Common Mistakes with SQL ESCAPED

Forgetting to double-escape the backslash in SQL strings (use '\\')
Using the same character for ESCAPED BY and TERMINATED BY, which causes syntax errors
Confusing ESCAPED BY with the LIKE ... ESCAPE clause
Expecting ESCAPED BY to affect binary columns or NULL representation

First Introduced In

MySQL 3.23

Frequently Asked Questions

What does ESCAPED BY actually change?

It sets the single character that precedes special sequences like \n, \t, or \0 when MySQL reads from or writes to a text file with LOAD DATA or INTO OUTFILE.

How can I turn escaping off?

Use ESCAPED BY '' (empty string). MySQL will then treat every byte literally and will not add escape prefixes to the output file.

Is ESCAPED BY related to the ESCAPE clause in LIKE patterns?

No. ESCAPED BY is limited to bulk file I/O, while the LIKE ... ESCAPE clause customizes wildcard escaping in pattern matching.

Does ESCAPED BY affect binary or BLOB columns?

No. It applies only to text fields. Binary columns are copied byte-for-byte regardless of the escape character.