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)