MySQL में CSV Import और Export

Import से पहले CSV Contract बनाएँ

CSV delimited text exchange format है, database backup नहीं। Load से पहले UTF-8 encoding, column order, header, delimiter, quote/escape rule, line ending, date/decimal format, NULL और duplicate policy तय करें।

Spreadsheet long IDs, dates और leading zeros बदल सकता है। Uploaded CSV को untrusted input मानें; filename/value से SQL न बनाएँ।

Reproducible Import Lab

DROP TABLE IF EXISTS students_csv_lab;
CREATE TABLE students_csv_lab (
 student_id INT PRIMARY KEY,
 student_name VARCHAR(80) NOT NULL,
 email VARCHAR(190) NULL,
 admission_date DATE NOT NULL,
 fee DECIMAL(10,2) NOT NULL,
 CHECK (fee >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UTF-8/LF में students.csv बनाएँ:

student_id,student_name,email,admission_date,fee
101,"Aman Verma",aman@example.test,2026-04-01,1250.00
102,"Sara, Khan",,2026-04-02,1500.50
103,"Kabir Rao",kabir@example.test,2026-04-03,900.00

Sara के quoted comma से enclosure test होता है और blank email SQL NULL बनेगा। Expected three rows और total fee 3650.50 है।

LOAD DATA LOCAL से Client File Import

LOCAL में client file पढ़कर MySQL को भेजता है। Server और client दोनों capability allow करें:

mysql --login-path=importer --local-infile=1 \
 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/approved/ca.pem school

LOAD DATA LOCAL INFILE '/approved/import/students.csv'
INTO TABLE students_csv_lab
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@email,@date,@fee)
SET student_id=CAST(TRIM(@id) AS UNSIGNED),
 student_name=TRIM(@name),
 email=NULLIF(TRIM(@email),''),
 admission_date=STR_TO_DATE(TRIM(@date),'%Y-%m-%d'),
 fee=CAST(TRIM(BOTH '\r' FROM @fee) AS DECIMAL(10,2));

CRLF file में LINES TERMINATED BY '\r\n' declare करें। Format mismatch columns shift या carriage return retain कर सकता है।

Variables और Staging से Safe Conversion

User variables assignment से पहले text receive करते हैं। Production में all-text staging table हर source row को batch/line metadata सहित रखती है:

CREATE TABLE student_import_stage (
 batch_id CHAR(36) NOT NULL,
 source_line INT NOT NULL,
 raw_id VARCHAR(40), raw_name VARCHAR(200),
 raw_email VARCHAR(250), raw_date VARCHAR(40),
 raw_fee VARCHAR(60), validation_error VARCHAR(500),
 PRIMARY KEY(batch_id,source_line)
) ENGINE=InnoDB;

INSERT INTO students_csv_lab
 (student_id,student_name,email,admission_date,fee)
SELECT CAST(raw_id AS UNSIGNED),TRIM(raw_name),
 NULLIF(TRIM(raw_email),''),
 STR_TO_DATE(raw_date,'%Y-%m-%d'),
 CAST(raw_fee AS DECIMAL(10,2))
FROM student_import_stage
WHERE batch_id=@batch AND validation_error IS NULL;

Business owner की approved merge policy के बिना REPLACE या broad duplicate update न करें। Silent coercion से बेहतर rejected-row report है।

Counts, Warnings और Totals Validate करें

SHOW WARNINGS LIMIT 100;
SELECT COUNT(*) AS imported_rows,SUM(fee) AS total_fee,
       SUM(email IS NULL) AS missing_email
FROM students_csv_lab;

Same session में warnings देखें। Strict mode/constraints useful हैं, पर business rules नहीं समझते। Exact header, column count, file size, encoding, IDs, dates, decimals, mandatory values, duplicates, counts, totals और destination authorization validate करें।

New batch load करके reconcile करें और final merge controlled transaction में करें। Sensitive rejected rows public logs में न लिखें।

Server-Side INFILE और secure_file_priv

SHOW VARIABLES LIKE 'secure_file_priv';
LOAD DATA INFILE '/var/lib/mysql-files/students.csv'
INTO TABLE students_csv_lab
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES;

LOCAL के बिना server process server-host file पढ़ता है। Powerful global FILE privilege और OS read permission चाहिए।

Normal application को FILE न दें। Managed hosting disable करे तो approved client/application import use करें।

INTO OUTFILE से CSV Export

SELECT student_id,student_name,email,
 DATE_FORMAT(admission_date,'%Y-%m-%d') AS admission_date,fee
FROM students_csv_lab ORDER BY student_id
INTO OUTFILE '/var/lib/mysql-files/students_export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Server file लिखता है, इसलिए FILE, OS permissions और secure_file_priv लागू हैं। Existing file overwrite नहीं होती और header row automatic नहीं आती। Headers, exact quoting, streaming download, cloud storage या authorization के लिए controlled application/ETL library use करें।

Share करने से पहले row authorization, minimum columns, masking, spreadsheet-formula injection control, encryption और expiry लगाएँ। CSV backup नहीं है।

Production CSV Checklist

1. Versioned contract/sample publish करें।
2. Web root से बाहर random storage name रखें।
3. Extension, size और content check करें।
4. Charset, delimiter, enclosure, line ending explicit रखें।
5. Staging में validate करके live merge करें।
6. Least privilege और verified TLS use करें।
7. Batch ID, counts, warnings, checksum/operator record करें।
8. Totals/samples reconcile और retries idempotent रखें।
9. Source/rejects encrypt, archive या securely delete करें।
10. Commas, quotes, Unicode, blanks, CRLF/LF, duplicates test करें।

privileges, transactions और security पढ़ें।

Official संदर्भ

• MySQL 8.4: LOAD DATA
• MySQL 8.4: LOAD DATA LOCAL Security
• MySQL 8.4: SELECT INTO OUTFILE

File location, LOCAL capability, formatting और export behavior official MySQL manual से verified हैं। Deployed version और hosting policy confirm करें।