Comma Separated Values (CSV) files are a way of transferring data between applications. Databases like MySQL and spreadsheet software like Excel support importing and exporting 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. [1
If you have access to the server running MySQL, you can export a selection using the command
INTO OUTFILE .
SELECT id, column1, column2 FROM table IN PROGRESS & # 39; /tmp/mysqlfiles/table.csv' FIELDS ENDED BY & # 39;, & # 39; CLOSED BY & # 39; "& # 39; LINES ENDED BY & # 39; n & # 39;
This will output a CSV file to
/tmp/mysqlfiles/table.csv or wherever you have configured it. You must ensure that the user has MySQL (usually
root ) owner and write access to the directory.
You also need to make sure that the
secure_file_priv setting gives MySQL access to that directory. By default, this blocks read and write access to SQL queries. This is a good thing; if your code is vulnerable to SQL injection, any potential attacker can only access MySQL and not the rest of the file system.
You can whitelist specific folders by editing your MySQL configuration file (usually found at
/ etc /my.cnf"19459007]) including:
[mysqld] secure-file-priv = "/ tmp / mysqlfiles"
This allows MySQL to read and write to
/ tmp / mysqlfiles / (which you must create with
mkdir ). Once MySQL can export files, you should be able to run the query and run CSV files.
ENCLOSED BY setting, commas are correctly escaped, for example:
"3", "Escape, this", "also, this"
which you can directly into any spreadsheet program or other software.
Keep Note that the exported CSV file does not contain column headers, but the columns are in the same order as the
SELECT statement. Also null values are exported as
N which is normal behavior, but if you want to change this you can change the selection by
ifnull (field, "") [19459007intepakken] Around your fields in your
From the MySQL command line
If you only have command line access to the MySQL instance and not access to the server itself (such as when it is not managed by you, in the case of Amazon RDS), the problem is Little trickier. Although you
FIELDS TERMINATED BY & # 39;, & # 39; On the server to generate a comma-separated list, the MySQL CLI is separated by tabs by default.
Simply enter a query from the command line, and direct it to a file:
mysql -u root -e "select * from database;" > output.tsv
Since MySQL output is tab-delimited, it is called a TSV file for & # 39; tab-delimited values & # 39; and it can work instead of your CSV file in some programs like importing spreadsheets. But it is not a CSV file and converting it to one is complicated.
You could easily replace any tab with a comma, which would work, but would cause it to fail if there are commas in the input data. If you are 100% sure that there are no commas in your TSV file (check with
grep ), you can replace the tabs with
sed "s / t /, / g" output.tsv> output.csv
But if you have commas in your data, you will have to use a much longer regex:
sed "s / & # 39; / & # 39; /; s / t / ", "/ g; s / ^ / " /; s / $ / "/; s / n // g" output.tsv> output.csv
This will correct escape fields with quotes, which will solve the comma problem.
Note: The tab character
t is not standard. On macOS and BSD, it's unavailable, leading to a mess of every lowercase & # 39; t & # 39;, inserting
sed erroneous commas. To fix this, use a literal tab instead of
sed "s / /, / g" output.tsv> output.csv
If you have input data tabs, you're out of luck and will have to generate a CSV file with a scripting language.
Do it manually with a real programming language
MySQL (and most databases) are designed to interact with, so you probably already have some kind of programming language associated with MySQL. Most languages can also write to disk, so you can create your own CSV output scripts by reading fields directly from the database, correctly escaping them, and writing a comma-separated file.
An example in Python.