Export MySQL to CSV with SELECT INTO OUTFILE

Use SELECT ... INTO OUTFILE to write query results to a delimited text file on the MySQL server. You can format the fields and line endings so another tool can read the file as CSV. For importing a CSV file into MySQL, see Import CSV into MySQL.

Export query results as CSV

Suppose a product_export_demo table contains the following columns:

CREATE TABLE product_export_demo (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    is_active TINYINT NOT NULL
);

Export only active products:

SELECT product_id, product_name, price
FROM product_export_demo
WHERE is_active = 1
INTO OUTFILE '/path/allowed/by/secure_file_priv/products.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n';

Replace the example path with a file path on the MySQL server that is permitted by its configuration. This statement writes data rows, but does not add a column-name header row. With the sample field options, a result can look like this:

101,"Notebook",4.50
102,"Pen, blue",1.25

OPTIONALLY ENCLOSED BY '"' encloses string fields while leaving numeric values unquoted; the quotes keep a comma inside a product name from being treated as a field separator. LINES TERMINATED BY '\n' writes LF line endings; use '\r\n' if the receiving system expects CRLF lines.

Check file access and output restrictions

SELECT ... INTO OUTFILE writes the file on the server host, not on the computer running your SQL client. The MySQL account needs the FILE privilege, and the mysqld operating-system user must be able to write to the destination directory. The output file must not already exist; MySQL refuses to overwrite it.

Check the server’s permitted directory with:

SHOW VARIABLES LIKE 'secure_file_priv';

If secure_file_priv is set to a directory, the output file must be written there. If it is NULL, server-side file import and export are disabled. Do not assume the allowed directory is the same on different MySQL installations.

If you need the CSV file on your own computer, use a client-side export feature or another approved file-transfer workflow. SELECT ... INTO OUTFILE writes to the database server’s file system.

Common export issues

• Access denied or secure-file-priv error: Confirm the account has FILE privilege and the destination path is allowed by secure_file_priv.
• File already exists: INTO OUTFILE will not replace it. Choose a new filename or manage the existing file with a separate, authorized file operation.
• Unexpected delimiters or quoting: Match FIELDS TERMINATED BY, OPTIONALLY ENCLOSED BY, and ESCAPED BY to the format expected by the receiving program.
• Wrong line breaks: Match LINES TERMINATED BY to the file’s required line ending.
• Missing column headings: The statement exports the selected values, not column labels. Use a client export tool if you need a header row.

For copying query results into an existing database table instead of a file, use MySQL INSERT INTO SELECT.

Summary

• Use SELECT ... INTO OUTFILE to export selected rows from MySQL to a server-side file.
• Set field separators, quoting, character set, and line endings to match the target format.
• Check FILE privilege, secure_file_priv, server file permissions, and whether the output filename already exists.

See the MySQL 8.4 Reference Manual: SELECT … INTO for complete syntax and file restrictions.