Export danych z MySQL-a

Pytano się mnie niedawno w jaki sposób zapisać wynik zapytania SQL do pliku w bazach danych MySQL. Przedstawię kilka możliwości.


Poniżej wyświetliłam dane z tabeli testowa, która pojawiała się już w moich wcześniejszych postach.

[11:58:33 root@goblin ~] mysql -h 127.0.0.1 -uroot -proot -P3309 -e "select * from test_myisam" ela_db 
+----+--------------+-------------------------------+---------------------+
| c1 | c2           | c3                            | c4                  |
+----+--------------+-------------------------------+---------------------+
|  3 | essss        | 2014-02-17 06:00:46           | 2014-02-17 07:14:29 |
|  1 | essss        | to jest cos innego            | 2014-02-17 06:03:22 |
|  4 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 06:03:22 |
|  5 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 07:14:49 |
|  7 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 06:03:22 |
|  6 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 07:15:03 |
|  2 | easdfsdfssss | bxcbvx to jest cos innffffego | 2014-02-17 06:03:22 |
|  8 | easdfsdfssss | bxcbvx to jest cos innffffego | 2014-02-17 08:20:08 |
+----+--------------+-------------------------------+---------------------+

Program klienta do połączenia z serwerem - mysql (w tym przypadku v5.5) może exportować dane:

• wertykalnie (pionowo)
• html-u
• xml-u
• tabeli (domyślnie)

Chciała bym przekierować wynik tego selektu do pliku tak aby móc później obrabiać te dane przy pomocy innych programów.
Jednym z najprostszych sposobów jest wywołanie komendy mysql z linii poleceń, przekierowanie wyniku do pliku i zakończenie połączenia z serwerem. Wykorzystamy do tego celu opcję: -e, --execute.
Problemem może jednak być to, że w wynik jest w formie tabeli, a dla nas ważne są tylko dane w niej zawarte. Skorzystamy w tym przypadku z opcji --skip-column-names i --silent. Pierwszą chyba nie muszę tłumaczyć ( :-) ). Ta ostatnia powoduje, że dane w kolumnach są oddzielone tabulatorami, a każdy wiersz jest pisany w nowej linii:

[12:11:28 root@goblin ~] /usr/local/mysql5.5/bin/mysql -h 127.0.0.1 -uroot -proot -P3309 --skip-column-names -e "select * from test_myisam" --silent ela_db 
3 essss 2014-02-17 06:00:46 2014-02-17 07:14:29
1 essss to jest cos innego 2014-02-17 06:03:22
4 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
5 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:14:49
7 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
6 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:15:03
2 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 06:03:22
8 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 08:20:08

Gdy już jednak jesteśmy zalogowani, także możemy przekierować wynik działania zapytania SELECT do pliku. Może nam pomóc w tym 'SELECT ... INTO OUTFILE'. Poważnym utrudnieniem jest jednak to, że utworzony plik został stworzony na maszynie, gdzie działa serwer MySQL.

mysql (root@ela_db)> select * INTO OUTFILE '/tmp/result.csv' from test_myisam;
Query OK, 8 rows affected (0.07 sec)

[12:18:30 root@goblin ~] cd /tmp/
[12:18:33 root@goblin tmp] ll result.csv
-rw-rw-rw- 1 mysql mysql 469 02-24 12:17 result.csv
[12:18:45 iloop@goblin tmp] cat result.csv 
3 essss 2014-02-17 06:00:46 2014-02-17 07:14:29
1 essss to jest cos innego 2014-02-17 06:03:22
4 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
5 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:14:49
7 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
6 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:15:03
2 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 06:03:22
8 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 08:20:08

Jest to o tyle elastyczna opcja, że możemy zdefiniować, że wynik ma być plikiem CSV. To my definiujmy jakim znakiem oddzielać dane w kolumnach i nowie wiersze:

mysql (root@ela_db)> select * FROM test_myisam INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 8 rows affected (0.00 sec)

[12:34:07 iloop@goblin tmp] cat result.csv 
"3","essss","2014-02-17 06:00:46","2014-02-17 07:14:29"
"1","essss","to jest cos innego","2014-02-17 06:03:22"
"4","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 06:03:22"
"5","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 07:14:49"
"7","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 06:03:22"
"6","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 07:15:03"
"2","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 06:03:22"
"8","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 08:20:08"

To somo polecenie możemy wykonać także przy pomocy polecenia mysqldump:

[06:38:49 root@goblin ~] /usr/local/mysql5.5/bin/mysqldump -h 127.0.0.1 -uroot -proot -P3309 \
> --no-create-db --no-create-info --skip-opt --tab=/tmp \
> --fields-terminated-by=,  --fields-enclosed-by='"' ela_db test_myisam

Wynik:

[06:39:13 root@goblin tmp] cat test_myisam.*
"3","essss","2014-02-17 06:00:46","2014-02-17 13:14:29"
"1","essss","to jest cos innego","2014-02-17 12:03:22"
"4","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 12:03:22"
"5","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 13:14:49"
"7","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 12:03:22"
"6","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 13:15:03"
"2","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 12:03:22"
"8","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 14:20:08"

To mysqldump z tymi opcjami wykonuje SELECT z INTO OUTFILE dlatego plik wynikowy można znaleźć na podanej ścieżce na maszynie działającego serwera MySQL.