Cara Menyimpan Output query MySQL ke File

Jika Anda perlu menyimpan hasil kueri MYSQL Anda misal ke lembar CSV atau Excel, Anda dapat melakukannya dengan bantuan ‘INTO OUTFILE’. Ini menyimpan hasil kueri sebagai ‘CSV’. Anda dapat membuka file CSV ini di Excel dan atau mengimportnya ke Sheets yang sudah ada

Contoh

SELECT id, name, published
FROM products
INTO OUTFILE '/var/lib/mysql-files/output.txt';

Hasil Output

1       Test Product 3  0
2       Test Product 1  1
7       Test Product 2  1
8       Test Product 4  1
9       Test Product 5  1
10      Test Product 6  1
11      Test Product 7  1
12      Test Product 8  1
13      Test Product 9  1
14      Test Product 10 1
15      Test Product 11 1

Agar output menjadi format CSV dapat menambahkan parameter berikut

SELECT id, name, published
FROM products
INTO OUTFILE '/var/lib/mysql-files/output.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Output

"1","Test Product 3","0"
"2","Test Product 1","1"
"7","Test Product 2","1"
"8","Test Product 4","1"
"9","Test Product 5","1"
"10","Test Product 6","1"
"11","Test Product 7","1"
"12","Test Product 8","1"
"13","Test Product 9","1"
"14","Test Product 10","1"
"15","Test Product 11","1"

Include Headings

SELECT 'id', 'name', 'published'
UNION ALL
SELECT id, name, published
FROM products
INTO OUTFILE '/var/lib/mysql-files/output.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Output

"id","name","published"
"1","Test Product 3","0"
"2","Test Product 1","1"
"7","Test Product 2","1"
"8","Test Product 4","1"
"9","Test Product 5","1"
"10","Test Product 6","1"
"11","Test Product 7","1"
"12","Test Product 8","1"
"13","Test Product 9","1"
"14","Test Product 10","1"
"15","Test Product 11","1"

Jika muncul error seperti berikut

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Maka dapat Anda cek variable secure_file_priv lalu sesuaikan lagi outputnya

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)