Import and Export CSV in MySQL

Design the CSV Contract Before Importing

CSV is a delimited text exchange format, not a database backup. Before loading, define the file contract: UTF-8 encoding, column order, header presence, comma delimiter, quote/escape rule, line ending, date format, decimal symbol, NULL representation and duplicate policy.

Spreadsheet programs can change long IDs, dates and leading zeros. Treat an uploaded CSV as untrusted input and never build SQL from its filename or field values.

Create a 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;

Save this approved sample as students.csv using UTF-8 and LF line endings:

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

The quoted comma in Sara's name tests enclosure handling; her blank email must become SQL NULL. Expected result is three rows and total fee 3650.50.

Import a Trusted Client File with LOAD DATA LOCAL

LOCAL means the client program reads the file and transfers it to MySQL. The server and client must both permit the capability. With the mysql client, enable it only for this trusted operation:

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));

Use forward slashes or correctly escaped paths. If the file uses CRLF, declare LINES TERMINATED BY '\r\n' instead of compensating in the last field. A format mismatch can shift columns or retain carriage returns.

Use Variables and a Staging Table for Safe Conversion

User variables let the loader receive text before assigning typed columns. For production, an all-text staging table is even safer because every source row can be retained with batch and 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;

Validate in staging, then transform valid rows:

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;

Do not use REPLACE or broad ON DUPLICATE KEY UPDATE until the business owner has approved how duplicates merge. A rejected-row report is safer than silent coercion.

Validate Counts, Warnings and Business Totals

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;

SELECT student_id,student_name,email,admission_date,fee
FROM students_csv_lab ORDER BY student_id;

Check warnings immediately in the same session. Strict SQL mode and table constraints help, but they do not understand business rules. Validate:

• exact header and column count;
• file size, row limit and allowed encoding;
• IDs, dates, decimals, email length and mandatory values;
• duplicates within the file and against live data;
• expected counts, sums and sampled records;
• authorization for the destination school/class/tenant.

Load to a new batch, reconcile it, and commit the final merge as one controlled transaction when possible. Keep sensitive rejected rows out of public logs.

Understand Server-Side INFILE and 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;

Without LOCAL, the MySQL server process reads a server-host file. The account needs the powerful global FILE privilege and the operating-system account must be able to read the file. secure_file_priv behaves as follows:

Avoid granting FILE to normal application accounts. Managed hosting may disable server-side operations; use an approved client/application import instead.

Export Query Results with INTO OUTFILE

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';

The server writes this file, so FILE privilege, OS permissions and secure_file_priv apply. MySQL refuses to overwrite an existing file. The statement does not add a header row. Use a controlled application/ETL library when headers, RFC-specific quoting, streaming downloads, authorization or cloud storage are required.

Before sharing an export, apply row-level authorization, select only required columns, mask sensitive values, spreadsheet-formula injection controls, encryption and expiry. CSV is not a substitute for backup and recovery.

Production CSV Checklist

1. Publish a versioned CSV contract and sample.
2. Use a random server-side storage name outside the web root.
3. Check extension, size and content; never trust MIME alone.
4. Use UTF-8, explicit charset, delimiter, enclosure and line ending.
5. Prefer staging; validate before touching live tables.
6. Run with least privilege and verified TLS.
7. Capture batch ID, counts, warnings, checksum and operator.
8. Reconcile totals and sample records; make retries idempotent.
9. Encrypt/archive or securely delete source and rejected rows.
10. Test commas, quotes, Unicode, blank values, CRLF/LF and duplicates.

Review user privileges, transactions and database security before production use.

Official References

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

File location, LOCAL capability, formatting and export behavior were checked against the official MySQL manual. Confirm the deployed client/server version and hosting policy.