Mysql mengekspor skrip csv shell

Ringkasan. dalam tutorial ini, Anda akan mempelajari berbagai teknik cara mengekspor tabel MySQL ke file CSV

CSV adalah singkatan dari nilai yang dipisahkan koma. Anda sering menggunakan format file CSV untuk bertukar data antar aplikasi seperti Microsoft Excel, Open Office, Google Docs, dll

Akan sangat berguna untuk memiliki data dari database MySQL dalam format file CSV karena Anda dapat menganalisis dan memformat data sesuai keinginan Anda

MySQL menyediakan cara mudah untuk mengekspor hasil kueri ke file CSV yang berada di server basis data

Sebelum mengekspor data, Anda harus memastikannya

• Proses server MySQL memiliki akses tulis ke folder target yang berisi file CSV target
• File CSV target tidak boleh ada

Kueri berikut memilih pesanan yang dibatalkan dari tabel  orders

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled';
Code language: SQL (Structured Query Language) (sql)

Untuk mengekspor kumpulan hasil ini ke file CSV, Anda menambahkan beberapa klausa ke kueri di atas sebagai berikut

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

Pernyataan tersebut membuat file CSV bernama  cancelled_orders.csv di folder  C:\tmp yang berisi kumpulan hasil

File CSV berisi baris baris di set hasil. Setiap baris diakhiri dengan urutan carriage return dan karakter umpan baris yang ditentukan oleh klausa LINES TERMINATED BY '\r\n'. Setiap baris berisi nilai dari setiap kolom baris di set hasil

Setiap nilai diapit oleh tanda kutip ganda yang ditunjukkan oleh klausa  FIELDS ENCLOSED BY '”'. Ini mencegah nilai yang mungkin mengandung koma (,) akan ditafsirkan sebagai pemisah bidang. Saat melampirkan nilai dengan tanda kutip ganda, koma di dalam nilai tidak dikenali sebagai pemisah bidang

Mengekspor data ke file CSV yang nama filenya berisi stempel waktu

Anda sering perlu mengekspor data ke file CSV yang namanya berisi stempel waktu saat file dibuat. Untuk melakukannya, Anda perlu menggunakan pernyataan yang disiapkan MySQL

Perintah berikut mengekspor seluruh tabel pesanan menjadi file CSV dengan stempel waktu sebagai bagian dari nama file

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');

SET @FOLDER = 'c:/tmp/';
SET @PREFIX = 'orders';
SET @EXT    = '.csv';

SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
				   "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
				   "  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;
Code language: SQL (Structured Query Language) (sql)

Mari kita periksa perintah di atas lebih terinci

• Pertama, kami membuat kueri dengan stempel waktu saat ini sebagai bagian dari nama file
• Kedua, kami menyiapkan pernyataan untuk dieksekusi dengan menggunakan perintah

  SELECT 
      orderNumber, status, orderDate, requiredDate, comments
  FROM
      orders
  WHERE
      status = 'Cancelled' 
  INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
  FIELDS ENCLOSED BY '"' 
  TERMINATED BY ';' 
  ESCAPED BY '"' 
  LINES TERMINATED BY '\r\n';
  Code language: SQL (Structured Query Language) (sql)

  0 pernyataan

  SELECT 
      orderNumber, status, orderDate, requiredDate, comments
  FROM
      orders
  WHERE
      status = 'Cancelled' 
  INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
  FIELDS ENCLOSED BY '"' 
  TERMINATED BY ';' 
  ESCAPED BY '"' 
  LINES TERMINATED BY '\r\n';
  Code language: SQL (Structured Query Language) (sql)

  1
• Ketiga, kami mengeksekusi pernyataan tersebut dengan menggunakan perintah

  SELECT 
      orderNumber, status, orderDate, requiredDate, comments
  FROM
      orders
  WHERE
      status = 'Cancelled' 
  INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
  FIELDS ENCLOSED BY '"' 
  TERMINATED BY ';' 
  ESCAPED BY '"' 
  LINES TERMINATED BY '\r\n';
  Code language: SQL (Structured Query Language) (sql)

  2

Anda dapat menggabungkan perintah dengan acara dan menjadwalkan acara berjalan secara berkala jika diperlukan

Mengekspor data dengan judul kolom

Akan lebih mudah jika file CSV berisi baris pertama sebagai judul kolom sehingga file tersebut lebih mudah dipahami

Untuk menambahkan judul kolom, Anda perlu menggunakan pernyataan UNION sebagai berikut

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');
Code language: SQL (Structured Query Language) (sql)

Seperti yang ditunjukkan kueri, Anda harus menyertakan tajuk kolom dari setiap kolom

Menangani nilai NULL

Jika nilai dalam kumpulan hasil berisi nilai NULL, file target akan berisi 

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
FROM
    orders INTO OUTFILE 'C:/tmp/orders2.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

Kami mengganti nilai

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';



Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

Mengekspor data ke file CSV menggunakan MySQL Workbench

Jika Anda tidak memiliki akses ke server database untuk mendapatkan file CSV yang diekspor, Anda dapat menggunakan MySQL Workbench untuk mengekspor kumpulan hasil kueri ke file CSV di komputer lokal Anda sebagai berikut

• Pertama, jalankan kueri, dapatkan set hasilnya
• Kedua, dari panel hasil, klik "export recordset to an external file". Set hasil juga dikenal sebagai recordset
• Ketiga, dialog baru ditampilkan. Ini meminta Anda untuk nama file dan format file. Masukkan nama file, pilih CSV sebagai format file dan klik tombol Simpan

File CSV yang diekspor oleh MySQL Workbench mendukung judul kolom,

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)

Bagaimana cara mengekspor file CSV dari MySQL?

Bagaimana cara mengekspor kueri MySQL sebagai CSV di Linux?

Bagaimana cara mengekspor hasil kueri SQL ke CSV?

Bagaimana cara mengekspor data dari tabel MySQL dari baris perintah?