JIYIK CN >

Comma-separated value files ( CSV ) are a way to transfer data between applications. Databases such as MySQL and spreadsheet software such as Excel both support importing and exporting via CSV , so we can use CSV files to exchange data between the two.

CSV files are plain text, so they are naturally lightweight and easy to export from MySQL.

Export from the database server

If we have access to a server running MySQL, we can INTO OUTFILEexport the selection using the command.

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

This will output the CSV file to /tmp/mysqlfiles/table.csv , or wherever we configured it to go. We need to make sure that the user that runs MySQL (usually mysql or root) has ownership and write permissions to that directory.

We also need to make sure that secure_file_privthe settings allow MySQL to access this directory. By default, this blocks read and write access for SQL queries. This is a good thing; if our code is vulnerable to SQLinjection, any potential attacker will only have access to MySQL and not the rest of the file system.

We can whitelist specific directories by editing the MySQL configuration file (usually located at /etc/my.cnf ), including:

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

This will allow MySQL to read and write to /tmp/mysqlfiles/ (which we had to mkdircreate using ). Once MySQL can export files, we should be able to run queries and output a CSV file.

With ENCLOSED BYthe setting, commas will be escaped correctly, for example:

"3","Escape, this","also, this"

We can import this directly into any spreadsheet program or other software.

请记住, the exported CSV file does not contain column headers, but the columns are SELECTin the same order as the statement. Also, null values ​​are exported as N, which is the expected behavior, but if we want to change this, we can modify the selection by enclosing around the fields in SELECTthe statement .ifnull(field, "")

Exporting from the MySQL command line

If we only have access to the MySQL instance through the command line, and not to the server itself (such as when it is not managed by us, in the case of Amazon RDS), the problem is a bit tricky. Although we can use on the server FIELDS TERMINATED BY ','to generate a comma-delimited list, the MySQL CLI will use tab delimiters by default.

Simply enter the query from the command line and pipe it to the file:

$ mysql -u root -e "select * from database;" > output.tsv

Because MySQL output is tab-delimited, it is called a TSV file, which stands for "Tab Separated Values," and can be used in place of a CSV file in some programs (such as spreadsheet import). But it is not a CSV file, and converting it to a CSV file is complicated.

We could simply replace each tab with a comma, which would work, but would fail if there were commas in the input data. If we are absolutely 100%sure that there are no commas in the TSV file ( grepcheck with ), we can replace the tabs with sed(see our sed tutorial for this command):

$ sed "s/t/,/g" output.tsv > output.csv

But if your data has commas in it, you'll have to use a longer regular expression:

$ sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv

This will correctly escape the quoted fields, thus solving the comma issue.

注意: The tab character t is not standard. On macOS and BSD, it is not available, which causes each lowercase "t" to be mangled, resulting in sedincorrect commas being inserted. To fix this, we need to use a literal tab character instead t:

sed "s/  /,/g" output.tsv > output.csv

If our input data contains tab characters, we are out of luck and must generate a CSV file ourselves using a scripting language.

Do it manually using a real programming language

MySQL (and most databases) are designed to be interacted with, so we probably already have some programming language connected to MySQL. Most languages ​​can also write to disk, so we can create our own CSV output script by reading the fields directly from the database, escaping them properly, and then writing a comma-delimited file.

Related Reading

• Back up the MySQL database to a file
• Restoring a MySQL database from a backup file
• Linux extraction command grep (I)
• Linux extraction command grep (Part 2)
• Linux extraction command grep (Part 3)