SQL ENCLOSED

What does the ENCLOSED BY clause do in MySQL?

Defines the quotation character that wraps each field when using FIELDS ... ENCLOSED BY in MySQL file import or export commands.

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 ENCLOSED: Supported: MySQL, MariaDB Not supported: PostgreSQL, SQL Server, Oracle, SQLite (use COPY or other loader syntax instead).

SQL ENCLOSED Full Explanation

ENCLOSED (usually written as ENCLOSED BY) is part of the FIELDS clause used with LOAD DATA INFILE and SELECT ... INTO OUTFILE in MySQL and MariaDB. It lets you specify a single-character string that surrounds every field (or, with the OPTIONALLY modifier, only character, VARCHAR, or text fields) in the input or output file. This is vital when working with CSV files where values may contain the field delimiter itself; enclosing each value in a quote character preserves data integrity.Behavior- If OPTIONALLY is omitted, every field (numeric or text) is wrapped by the given character on export, and the same character is expected on import.- With OPTIONALLY ENCLOSED BY, only string fields are quoted; numeric fields are written without quotes. The loader still accepts quotes around numeric fields but does not require them.- The enclosure character must be exactly one byte long. Multi-byte strings cause an error.- ENCLOSED BY can be combined with TERMINATED BY, ESCAPED BY, and LINES TERMINATED BY to fully describe the file format.Caveats- MySQL treats NULL as an unquoted \N token, even when ENCLOSED BY is in use.- If the enclosure character appears inside a value, it must be escaped using the ESCAPED BY character (default backslash) or doubled if ESCAPED BY ''.- Other RDBMSs (PostgreSQL COPY, SQL Server BULK INSERT, Oracle SQL*Loader) use different keywords; ENCLOSED is MySQL-specific.

SQL ENCLOSED Syntax

-- Export
SELECT col1, col2
INTO OUTFILE 'file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM my_table;

-- Import
LOAD DATA INFILE 'file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(col1, col2);

SQL ENCLOSED Parameters

- ENCLOSED BY STRING (length 1) - Character used to wrap each field.
- OPTIONALLY (KEYWORD) - Quotes only string columns when present.
- TERMINATED BY (STRING) - Delimiter between fields (works with ENCLOSED BY).
- ESCAPED BY STRING (length 1) - Escape character for enclosure or delimiters.

Example Queries Using SQL ENCLOSED

-- 1. Export a CSV with quoted fields
SELECT id, name, created_at
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM users;

-- 2. Import the same file, quoting only strings
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users_tmp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
(id, name, created_at);

Expected Output Using SQL ENCLOSED

Query 1 writes /tmp/users
csv where every field value is surrounded by double quotes and separated by commas
Query 2 reads that file and inserts its rows into users_tmp, correctly parsing each quoted value into the target columns

Use Cases with SQL ENCLOSED

Creating RFC-4180 compliant CSVs for downstream tools.
Loading third-party CSV files that quote text but not numbers.
Avoiding delimiter collision when field values contain commas or tabs.
Maintaining consistent import/export formats in automated ETL pipelines.

Common Mistakes with SQL ENCLOSED

Forgetting the OPTIONALLY keyword, causing numeric fields to be quoted unexpectedly.
Using a multi-character string (e.g., '""') as the enclosure character.
Mixing different ENCLOSED BY characters on import vs export, resulting in parsing errors.
Assuming ENCLOSED BY is available in PostgreSQL COPY or SQL Server BULK INSERT.

First Introduced In

MySQL 3.23

Frequently Asked Questions

What characters can be used with ENCLOSED BY?

Any single-byte character is allowed, but double quotes ('"') and single quotes ('\'') are the most common.

How do I escape an enclosure character inside a field?

Use the ESCAPED BY character (default backslash) before the quote, or double the quote if ESCAPED BY '' is set.

Can ENCLOSED BY be combined with fixed-width files?

No. ENCLOSED BY is meaningful only when you also use FIELDS TERMINATED BY to create delimited, variable-width files.

Is ENCLOSED BY required for CSV compliance?

While not mandatory, quoting fields is recommended for maximum compatibility, especially when data may contain delimiter characters.