Export Table to CSV - (MySQL Misc)

Sometimes we may need to export data from a MySQL table to a CSV file. This can be useful for sharing data with others or for use in another application. In this tutorial, we'll show you how to export a MySQL table to a CSV file using SQL.

Syntax

The basic syntax for exporting a MySQL table to a CSV file is as follows:

SELECT column1, column2, column3...
FROM table_name
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• column1, column2, column3: The columns you want to export from the table.
• table_name: The name of the table you want to export.
• /path/to/file.csv: The path and filename of the CSV file you want to create.
• FIELDS TERMINATED BY ',': Specifies the delimiter used to separate fields in the CSV file.
• ENCLOSED BY '"': Specifies the enclosing character used for string fields.
• LINES TERMINATED BY '\n': Specifies the line separator used in the CSV file.

Example

Let's say we have a table called "customers" with columns "id", "first_name", and "last_name", and we want to export this table to a CSV file called "customers.csv" in the "/tmp" directory. Here's how we can do it:

SELECT id, first_name, last_name
FROM customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Output

When we run the example code above, the "customers" table will be exported to a CSV file called "customers.csv" in the "/tmp" directory. The file will contain the data from the "id", "first_name", and "last_name" columns, separated by commas and enclosed in double quotes (if applicable).

Explanation

In the example above, we used a SELECT statement to select data from the "customers" table. We then used the INTO OUTFILE clause to specify the path and filename of the CSV file we wanted to create. We specified the delimiter, enclosing character, and line separator used in the CSV file.

Use

Exporting data from a MySQL table to a CSV file can be useful for sharing data with others or for use in another application. For example, you might use this technique to export customer data from a database to a CSV file, which could then be imported into a CRM application.

Important Points

• The path and filename of the CSV file must be specified as an absolute path, not a relative path.
• The MySQL user account used to execute the query must have the FILE privilege.
• If the CSV file already exists, it will be overwritten.

Summary

In this tutorial, we showed you how to export a MySQL table to a CSV file using SQL. We covered the syntax, example, output, explanation, use, and important points of exporting a MySQL table to a CSV file. With this knowledge, you can now export data from your MySQL tables to a CSV file for use in other applications.