MySQL LOAD DATA INFILE can be used to load thousands or millions of records very quickly. Each row in a CSV text file will be created as a record in the database.
Let’s say a developer needs to load thousands of customer records from another system, and receives a CSV file representing the customer records. A couple of example records of customer data might look like this:
$ cat customers.txt
bob@example.com,Bob Johnson
jane@example.com,Jane Doe
This example will use a database called company_stuff and a table called customers. The customers table has columns for email and full_name.
mysql> create database company_stuff;
mysql> use company_stuff;
mysql> CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100),
full_name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Using LOAD DATA LOCAL INFILE it is possible to load these rows from the CSV file as records in the customers table. If the CSV file contains a header row that should be skipped at import time, add IGNORE 1 LINES after the LINES TERMINATED BY clause.
mysql> LOAD DATA LOCAL INFILE 'customers.txt'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(email, full_name);
Verify the records were created.
mysql> select * from customers;
+----+------------------+-------------+
| id | email | full_name |
+----+------------------+-------------+
| 1 | bob@example.com | Bob Johnson |
| 2 | jane@example.com | Jane Doe |
+----+------------------+-------------+
2 rows in set (0.00 sec)
Instead of loading customer records, what if the developer had to export customer data? MySQL has the ability to write the results of a SQL query to a file. By reading and writing data to and from a CSV file, large amounts of data can be manipulated very quickly. The SQL commands to interact with the data can be scripted from a language like Ruby to automate the process further.
To export the customer database to a file in the tmp directory /tmp/my_customers.txt:
mysql> SELECT email, full_name
INTO OUTFILE "/tmp/my_customers.txt"
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM customers;
Finally, verify the contents in the new CSV file are the records from the customers table as follows:
$ cat /tmp/my_customers.txt
bob@example.com,Bob Johnson
jane@example.com,Jane Doe
Comments