SQL OPTIONALLY

What is the SQL OPTIONALLY keyword?

In MySQL, OPTIONALLY specifies that only character-type columns may be enclosed by a given quote character when importing or exporting delimited files.

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 OPTIONALLY: Supported: MySQL, MariaDB Not Supported: PostgreSQL, SQL Server, Oracle, SQLite

SQL OPTIONALLY Full Explanation

OPTIONALLY is a MySQL keyword that appears in the FIELDS clause of LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. When you write FIELDS OPTIONALLY ENCLOSED BY '"', the server treats the enclosing character as optional: - During import (LOAD DATA INFILE) only values for character columns (CHAR, VARCHAR, TEXT, etc.) may be quoted; numeric and other non-string columns do not need the quotes.- During export (SELECT ... INTO OUTFILE) MySQL surrounds only character columns with the specified quote character. Non-string columns are written without quotes, producing smaller, cleaner files.Without OPTIONALLY, ENCLOSED BY forces every column value—regardless of data type—to be wrapped in the quote character. OPTIONALLY therefore offers more flexible CSV handling, especially when you need compatibility with tools like Excel, pandas, or other databases that expect numbers to appear unquoted.Caveats:- OPTIONALLY works only in MySQL and MariaDB. Other systems either ignore it or raise an error.- The keyword must precede ENCLOSED BY. Placing it after ENCLOSED BY is a syntax error.- It has no effect if ENCLOSED BY is omitted.

SQL OPTIONALLY Syntax

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

SQL OPTIONALLY Parameters

Example Queries Using SQL OPTIONALLY

-- Import a CSV where only strings are quoted
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

-- Export data to a CSV with optional quoting
SELECT id, name, created_at
INTO OUTFILE '/tmp/users_export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users;

Expected Output Using SQL OPTIONALLY

In the first query MySQL reads /tmp/users
csv and loads rows into users, accepting quotes around string fields but not requiring them for numeric fields
In the second query MySQL writes /tmp/users_export
csv where only character columns are wrapped in double quotes

Use Cases with SQL OPTIONALLY

Importing third-party CSV files that quote only text fields
Exporting data for tools that prefer numbers unquoted
Reducing file size by omitting unnecessary quotes
Preventing downstream type-parsing errors in applications that treat quoted numbers as strings

Common Mistakes with SQL OPTIONALLY

Using OPTIONALLY in databases other than MySQL or MariaDB
Placing OPTIONALLY after ENCLOSED BY (must precede it)
Expecting numeric columns to be quoted when OPTIONALLY is present
Omitting ENCLOSED BY and assuming OPTIONALLY will still work

First Introduced In

MySQL 3.23

Frequently Asked Questions

What does OPTIONALLY ENCLOSED BY actually do?

It signals that only text columns may be quoted with the specified character during import or export. Numeric columns remain unquoted, creating cleaner CSV files.

Where can I use OPTIONALLY?

Only inside the FIELDS clause of LOAD DATA INFILE and SELECT ... INTO OUTFILE in MySQL or MariaDB.

What happens if I omit OPTIONALLY?

MySQL will quote every column value when exporting, and will expect every column value to be quoted when importing.

Does OPTIONALLY affect performance?

Yes, slightly. Exporting fewer quote characters reduces file size, which can improve IO performance when moving large datasets.