CSV (Comma Separated Value) files are a popular data format for data storage due to their compatibility and simplicity. These files can be opened and manipulated in text editors and spreadsheet programs, presenting data in columns and rows for easy analysis, which makes it convenient for data sharing and analysis.
SQL databases are excellent for data storage and analysis but are not easily shareable. Exporting data from a MySQL or MariaDB database as a CSV file is a practical data extraction and sharing solution. The required data can be selected using SQL queries and saved in CSV format.
Access to the table and the destination folder is required to export data from a MySQL or MariaDB database to a CSV file. The formatting of the CSV file must also be specified, including the terminator, encloser, and line terminator characters. This ensures the data in the CSV file is formatted correctly and readable by other applications.
$ sudo mysql -u root [sudo] password for user: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32-0ubuntu0.22.10.2 (Ubuntu) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> describe salaries; +-----------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------+------+-----+---------+-------+ | emp_no | int | NO | PRI | NULL | | | salary | int | NO | | NULL | | | from_date | date | NO | PRI | NULL | | | to_date | date | NO | | NULL | | +-----------+------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
mysql> SELECT * FROM salaries; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10001 | 60117 | 1986-06-26 | 1987-06-26 | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | ##### snipped | 499999 | 70745 | 1999-11-30 | 2000-11-29 | | 499999 | 74327 | 2000-11-29 | 2001-11-29 | | 499999 | 77303 | 2001-11-29 | 9999-01-01 | +--------+--------+------------+------------+ 2844047 rows in set (0.46 sec)
mysql> SELECT * FROM salaries WHERE salary > 150000; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 43624 | 151115 | 1998-03-23 | 1999-03-23 | | 43624 | 153166 | 1999-03-23 | 2000-03-22 | | 43624 | 153458 | 2000-03-22 | 2001-03-22 | ##### snipped | 493158 | 151565 | 2000-05-05 | 2001-05-05 | | 493158 | 152208 | 2001-05-05 | 2002-05-05 | | 493158 | 154376 | 2002-05-05 | 9999-01-01 | +--------+--------+------------+------------+ 36 rows in set (0.33 sec)
mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.02 sec)
mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/myfile.csv' -> FIELDS TERMINATED BY ',' -> ENCLOSED BY '"' -> LINES TERMINATED BY '\n' -> FROM salaries -> WHERE salary > 150000; Query OK, 36 rows affected (0.32 sec)
In this example, the “FIELDS TERMINATED BY ','” clause specifies that the fields should be separated by commas, the “ENCLOSED BY '”'“ clause specifies that the fields should be enclosed in double quotes, and the “LINES TERMINATED BY '\n'” clause specifies that each row should be terminated by a newline character.
Writing the file to inappropriate location will throw the following error:
ERROR 1290 (HY000): The MySQL server is running with the %%--secure-file-priv%% option so it cannot execute this statement
$ sudo cat /var/lib/mysql-files/myfile.csv "43624","151115","1998-03-23","1999-03-23" "43624","153166","1999-03-23","2000-03-22" "43624","153458","2000-03-22","2001-03-22" ##### snipped "493158","151565","2000-05-05","2001-05-05" "493158","152208","2001-05-05","2002-05-05" "493158","154376","2002-05-05","9999-01-01"
Comment anonymously. Login not required.