MySQL Command Line – How to export a CSV file from CloudSave IT
Comma Separated Value Files (CSV) is a way to transfer data between applications. Dikes like Mike MySQL and spreadsheet software support import and export via CSV, so you 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.
From the database server
If you have access to the server in which MySQL is running, you can export a selection with it
INTO OUTFILE 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 a CSV file
/tmp/mysqlfiles/table.csv, Or wherever you have configured it. You need to make sure the user is running MySQL (usually)
rootHas ownership and written access to the directory.
You also have to make sure
secure_file_priv Configuration gives MySQL access to this directory. This, by default, prevents access to MySQL queries from reading and writing. That’s a good thing. If your code suffers from MySQL injection, any potential attacker will only have access to MySQL, not the rest of the file system.
You can whitelist specific directories by editing your MySQL config file (usually located)
/etc/my.cnf) to include:
[mysqld] secure-file-priv = "/tmp/mysqlfiles"
Which will allow MySQL to read and write
/tmp/mysqlfiles/ (With which you have to create
mkdir). Once it can export MySQL files, you should be able to run queries and export CSV files.
ENCLOSED BY By setting, commas will escape correctly, such as:
"3","Escape, this","also, this"
Which allows you to import directly into any spreadsheet program or other software.
Keep in mind that the exported CSV file does not include column headings, but the columns will be in the same order as
SELECT Statements and values will also be exported
N, Which is expected behavior, but if you want to change it you can wrap and edit the selection
ifnull(field, "") Around your fields
From the MySQL command line
If you only have command line access to MySQL for example, and do not have access to the server itself (such as when it does not run through you, in the case of Amazon RDS), the problem is a bit more difficult. Is. While you can use
FIELDS TERMINATED BY ',' To create a comma-separated list on the server, MySQL will be separated by default with LL tabs.
Just enter a query from the command line, and pipe it into the file:
mysql -u root -e "select * from database;" > output.tsv
Because MySQL output is separated with tabs, it is called a TSV file, because “tab-separated values”, and in some programs can work in place of your CSV file. Spreadsheet imports. But this is not a CSV file, and converting it to one is complicated.
You can easily replace each tab with a comma, which will work but fail if you put a comma in the input data. If you are 100% sure that your TSV file does not have a comma (check it out)
grep), You can change the tab with it
sed "s/t/,/g" output.tsv > output.csv
But if your data has a comma, you will have to use a very long regex:
sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv
This quote will correctly escape from the marked fields, which will solve the problem of coma.
Note: Tab Character
t Not standard. On Mac OS and BSD, it’s not available, which disrupts every “T” at the bottom
sed To insert the wrong comma. To solve this you’ll need to use the literal tab character in place
sed "s/ /,/g" output.tsv > output.csv
If your input data includes tabs, you’re out of luck, and have to create a CSV file yourself from the scripting language.
Do this manually with a real programming language
Designed to interact with MySQL (and most databases), so you already have some kind of programming language attached to Mike MySQL. Most languages can also write to disk, so you can read fields directly from the database, escape them correctly, and create your own CSV output script by writing a comma-deleted file.
An example in Python.