Kursus MariaDB / Mengimpor dan Mengekspor CSV dengan Jalur, Hak, dan Pengodean yang Benar
Impor CSV dengan menyamakan pemisah, tanda kutip, akhir baris, dan pengodean, memeriksa nilai teks pada tabel penampung, lalu mengonversinya ke tipe tujuan. LOAD DATA LOCAL INFILE membaca berkas di komputer klien, sedangkan SELECT INTO OUTFILE menulis pada server sehingga kebutuhan jalur dan haknya berbeda.
Tentukan komputer yang membaca atau menulis berkas
Comma-separated values atau CSV adalah format data teks yang memisahkan bidang dengan koma pada contoh ini. Nilai yang memuat koma perlu ditandai sesuai aturan kutip yang disepakati. Pengodean karakter dan konvensi akhir baris juga merupakan bagian dari format yang harus cocok.
| Operasi | Lokasi berkas | Persyaratan |
|---|---|---|
| LOAD DATA LOCAL INFILE | Komputer klien | Klien dan server mengizinkan LOCAL |
| LOAD DATA INFILE | Server basis data | Hak FILE dan kebijakan jalur server |
| SELECT INTO OUTFILE | Server basis data | Hak FILE, secure_file_priv, dan izin sistem operasi |
Aktifkan LOCAL hanya untuk server tepercaya dengan mempertimbangkan berkas klien yang dapat dibaca dan dikirim. Jangan melonggarkan seluruh pengaturan global atau izin berkas hanya agar kesalahan impor hilang. Panduan klien dan jalur berkas SQL menjelaskan sisi klien, sedangkan panduan INSERT menjelaskan pemetaan kolom tujuan.
Referensi LOAD DATA INFILE MariaDB dan referensi SELECT INTO OUTFILE menjelaskan operasi beserta batasannya.
Siapkan berkas UTF-8 dan tabel penampung teks mentah
Buat products.csv baru di C:/mariadb-lab pada komputer klien dan pertahankan berkas yang sudah ada. Simpan isi berikut sebagai UTF-8. Baris pertama menamai tiga bidang; nama produk pertama berarti buku catatan besar dan memuat koma sehingga memakai tanda kutip.
product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00
Hubungkan ke server belajar, periksa SELECT VERSION(), DATABASE();, lalu pilih shopcourse. Pastikan csv45_stage dan csv45_products belum ada menggunakan SHOW TABLES; jika sudah ada, periksa isinya sebelum memutuskan apakah boleh dibuang. Tampung teks dahulu agar konversi angka tidak langsung menyembunyikan masukan yang salah.
USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Muat berkas klien dan periksa peringatan sebelum konversi
Jalankan perintah klien berikut di terminal sistem operasi dan masukkan kata sandi melalui prompt. Sesuaikan port bila server belajar memakai nilai berbeda. Sesudah tersambung, jalankan SQL berikutnya di dalam klien MariaDB.
mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse
LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
OR CAST(raw_id AS DECIMAL(65,0))>2147483647
OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
OR CHAR_LENGTH(raw_name)>100;
Jika kebijakan local_infile server melarang operasi, tinjau bersama administrator. Impor LOCAL tidak selalu menolak seluruh masukan salah hanya karena mode ketat aktif. Periksa SHOW WARNINGS dan string asli sebelum mempercayai hasil konversi.
Contoh memisahkan baris menggunakan LF dan membuang CR di akhir bidang terakhir untuk kasus CRLF sederhana yang umum. Baris tertanam dalam bidang serta aturan escape yang lebih kompleks perlu disepakati dengan alat pembuat berkas. Jalankan pemeriksaan format dahulu; hanya setelah lolos, lanjutkan pemeriksaan ID yang dinormalisasi, duplikat, rentang angka, dan panjang nama.
Untuk klien Ubuntu, buat direktori kerja pada terminal dan simpan products.csv UTF-8 di sana. Perintah kedua mencetak jalur berkas yang akan digunakan; perintah tersebut tidak membuat isi CSV secara otomatis.
mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"
Ganti jalur LOCAL dengan jalur absolut sebenarnya yang dicetak. Contoh berikut mengasumsikan nama login klien learner.
LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
Jalankan hanya impor Windows atau Ubuntu yang sesuai dengan komputer klien. Menjalankan keduanya menambahkan baris penampung dua kali. Tujuan OUTFILE pada Ubuntu mengikuti kebijakan server yang dijelaskan terpisah, bukan direktori klien ini.
Pindahkan hanya nilai yang sudah divalidasi ke kolom bertipe
Pemeriksaan masukan salah dan duplikat harus menghasilkan nol baris. ID harus berada pada rentang INT bertanda positif, harga sesuai DECIMAL(10,2), serta nama paling panjang 100 karakter. Teks ID 01 dan 1 dapat menjadi nilai sama sesudah konversi, sehingga keunikan hasil normalisasi juga perlu diperiksa.
Jika validasi gagal, perbaiki sumber atau tentukan kebijakan masukan sebelum menjalankan INSERT. Jangan menganggap satu nilai angka yang berhasil dikonversi membuktikan string sumber seluruhnya benar.
INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;
Harus ada tiga produk dan nama pertama tetap memuat koma. Nama lain berarti cangkir serta pena. Bandingkan bidang dengan sumber, bukan hanya jumlah baris. Mengulang LOAD pada tabel penampung yang sama dapat menambahkan baris; simpan berkas sumber dan hasil diagnosis sebelum membersihkan percobaan gagal.
Ekspor CSV sisi server melalui akun yang berwenang
Gunakan administrator atau akun ekspor yang disetujui dan mempunyai hak FILE. Periksa secure_file_priv, izin tulis sistem operasi untuk proses server, dan nama tujuan baru. Jika server jarak jauh memakai jalur C:, jalur tersebut milik server itu, bukan komputer klien.
SELECT @@secure_file_priv;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Keluaran memuat tiga rekaman CSV tanpa baris judul, dan berkas yang sudah ada tidak ditimpa. Jika memerlukan judul, tentukan prosedur ekspor yang menambahkannya secara jelas. Mengganti ekstensi keluaran tab biasa dari mariadb --batch menjadi .csv tidak mengubahnya menjadi format CSV.
Untuk server Ubuntu, periksa SELECT @@secure_file_priv serta izin tulis akun layanan MariaDB. Jika direktori yang disetujui adalah /var/lib/mysql-files/, berkas server baru dapat dibuat seperti berikut. Direktori ini bukan direktori home klien.
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Ganti direktori dengan lokasi yang benar-benar disetujui dan jalankan sekali pada server tersebut. Jangan menghapus berkas yang sudah ada atau memperluas hak FILE hanya untuk melewati kesalahan.
Periksa hasil pulang-pergi per bidang
Bandingkan koma, teks Unicode, string kosong, NULL, dan akhir baris selain jumlah rekaman. Alat CSV tidak selalu memakai konvensi NULL yang sama; tentukan apakah bidang kosong berarti teks kosong atau data tidak tersedia. Contoh ini sengaja memakai tiga bidang terisi.
Pastikan hasil ekspor dibaca dengan aturan pemisah dan kutip yang sesuai sebelum dipakai sistem lain. Tinjau kebutuhan retensi berkas secara terpisah dari tabel. Setelah memastikan objek boleh dibuang, hapus hanya dua tabel latihan berikut. DROP TABLE menghapus struktur serta baris dan tidak dibalik dengan ROLLBACK biasa; berkas sumber dan ekspor tidak otomatis ikut dihapus.
DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;




