Рубрики
Servers: How To

HOWTO: Linux console MYSQL export to CSV (Excel)

Easy!

First of all you have to be root or any user who can work with databases.

Let’s connect to mysql (mariadb and so on, it is doesn’t matter)

# mysql -u root -p

Then choose your database (for example «baza»):

MariaDB [(none)]> use baza;

Export!

Let’s do export to csv file. We will create file «baza.csv» in /tmp folder of linux, because it is guaranteed to record data for any users, and will export all columns (*) from one table with name «maintable»

MariaDB [baza]> select * from maintable into outfile '/tmp/baza.csv' fields terminated by ';' enclosed by '"' escaped by '\';

Result

My result was not so fast. It depends of your hardware and size of database

Query OK, 8742646 rows affected (23.045 sec)

Wait! Mat be we don’t want to create very big csv with useless data?

How to export only selected columns to csv?

We will take 3 columns («id»,»name» and «members_count») from table «groups» and sort them in order of ascendance in third column.

MariaDB [baza]> select id,name,members_count
-> from groups
-> order by members_count
-> into outfile '/tmp/bazamin.csv'
-> fields terminated by ';'
-> enclosed by '"'
-> lines terminated by '\n';
Query OK, 5561639 rows affected (6.953 sec)

Profit!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *