LOAD DATA INFILE vs. INSERT in MySQL: Why, How, When?

Tags: mysql, guide
Published: 2021-09-23 22:04:02

## Introduction
If you have been working with MySQL for a while, you probably already know that MySQL offers multiple methods for importing data. One of those – the `INSERT INTO` statement – could be considered a standard way to do so. However, MySQL also has another way that dumps can be imported – `LOAD DATA INFILE`. Such a statement might be helpful if we find ourselves working with a lot of data inside our database instances. However, when we are facing those two choices, we probably are not sure which one of them is better – that's what we are looking into today.
​
## INSERTing Data
To begin with, the `INSERT` statement, as its name suggests allows us to insert data into a table. In its most basic form, the statement looks like so:
​
`INSERT INTO demo_table (column_1) VALUES ('Demo Data');`
​
As you can see, the statement is comprised of a few parts:
​
1. The `INSERT INTO` statement tells MySQL we want to `INSERT` data instead of `SELECT`ing, deleting, or updating it.
2. `demo_table` is the name of the table into which we want to insert the data.
3. `column_1` refers to the column name of the column to which we want to insert data.
4. `VALUES` specifies that values will be set after this parameter.
5. `'Demo Data'` inserts a row with the text `'Demo Data'` inside a column `column_1`.
​

### INSERT Examples
After we would run such a SQL statement, some demo data will be added to our table. We can also run `INSERT` statements like so:  

`INSERT INTO arctype (column, column_2) VALUES ('Demo', 'Demo 2');`

In this case, a value of `Demo` would be inserted into a column called `column` and a value of `Demo 2` would be inserted into `column_2`.
​
Also, keep in mind that when we're adding values for every column in the table, we don't need to specify columns:

`INSERT INTO arctype VALUES ('Demo', Demo 2');`
​
It would work just the same as an earlier example.
​
Also, we could make use of inserting multiple rows at once, a query like so:
​
``` 
INSERT INTO arctype (demo_1, demo_2) 
VALUES ('Demo Data', 'Demo 2'),('Demo Data Again', 'Data123'),('Data here', 'Data here too');
```

It would flawlessly execute too! The upside of this is that we also reduce calls made to the database itself.
​
### When INSERT Doesn't Work
​However, the `INSERT` query also comes with some caveats: the data inserted into the table must conform with the data type of the column set in the table. What that means is that if, for example, we would run a query like so:
​ 
`INSERT INTO arctype (number_column) values ('Demo');`

Where `number_column` is of type `INT`, MySQL would not execute the query and instead would return an error like so:
​
`#1366 - Incorrect integer value: 'Demo' for column 'number_column' at row 1`
​
The error is pretty self-explanatory: the `number_column` column accepts numbers and not text values: we have fed it something else, and MySQL did not like it.
​
## Importing Large Amounts of Data
​Inserting data into your MySQL instances gets you pretty far. Still, at some point in your career as a MySQL DBA, you might need to look into `INSERT` statements and import rather large amounts of data (data comprised of 100,000 rows or more) into your database instances. A couple of big data-based projects could make use of this feature ([for example, did you know that one of the most significant data breach search engines in the world was built on MySQL?](https://breachdirectory.com)). Still, to fully understand it, we must realize our options. 
​
### Using LOAD DATA INFILE
​To import large amounts of data into MySQL, we can either use the technique for inserting multiple rows (outlined above) or use `LOAD DATA INFILE` instead. As the name suggests, `LOAD DATA INFILE` loads the data into a table from a file.  The basic syntax of the `LOAD DATA INFILE` query looks like so:
   
`LOAD DATA INFILE '/path/to/file.csv' INTO TABLE demo_table;`
​
Where `/path/to/file.csv` depicts a path to the file we want to load data from and `demo_table` represents the table's name.
​
`LOAD DATA INFILE` also has other options that can be specified. For example, we can specify columns that we want to load data into at the end of the statement as well:
​
`LOAD DATA INFILE '/path/to/file.csv' INTO TABLE demo_table (demo_column);`
​
### What is IGNORE?
​A statement like the one above would only insert data into one column called `demo_column`. We can also specify an `IGNORE` keyword if we so desire:
​
`LOAD DATA INFILE '/path/to/file.csv' IGNORE INTO TABLE demo_table;`
​
This statement will `IGNORE` any errors it encounters and loads the data regardless. The `IGNORE` statement can also be used in combination with specific columns:

`LOAD DATA INFILE '/path/to/file.csv' IGNORE INTO TABLE demo_table (demo_column);`
​
This statement is very powerful for one simple reason: if our given dataset is vast and we don't even need half of the information in it (say, we only need the values of the first column, but not the second, third, etc.), this statement would conveniently "forget" to load data into those columns and ignore any errors that MySQL might encounter on the way. Talk about saving time.

Also, keep in mind that `LOAD DATA INFILE` can also be `LOCAL` meaning that you can quickly execute a query like `LOAD LOCAL DATA INFILE` as well: that would work too. The `LOCAL` statement changes the location where MySQL can expect the data: if the `LOCAL` statement is in use, MySQL would expect the data to be located somewhere on the client host; otherwise, it should reside on the server host.
​
## Why is LOAD DATA INFILE So Fast?
​When working with MySQL and comparing the import time between `INSERT INTO` and `LOAD DATA INFILE` statements in MySQL, you are likely to notice that the `LOAD DATA INFILE` statement loads data into a database way, way faster than `INSERT` does. The reason why is pretty simple: `LOAD DATA INFILE` bothers MySQL less than `INSERT` statements do: for example, the `LOAD DATA INFILE` statement can skip lines, columns, or, if we wish, load data only into specific columns, skipping all others (refer to the example above.) The `INSERT` statement offers no such features; although it can easily insert a couple of rows at once, it's ill-equipped to handle data sets more extensive than, say, a million rows. That's not to say that `LOAD DATA INFILE` is without its flaws either, though; for example, run `LOAD DATA INFILE` with 100 million or more rows, and you will notice that it will probably slow down after a while: to avoid that, you might want to break up the files you use to load data into the database and instead of loading data with, say, 100 or 200 million rows at once, load it in smaller chunks: have, for example, ten files with 10 million rows each, get your `LOAD DATA INFILE` statements from a loop and run them one by one. You're done!
​
## Modifying Data with Arctype
​If you frequently find yourself `INSERT`ing data into MySQL, chances are you are using an editor. A SQL editor like Arctype will let you create and edit tables with ease. It will allow you to share queries with your team as well – all with blazing fast performance. If you need a SQL editor, definitely give [Arctype](https://arctype.com) a try. For example, here's how the process of editing tables might look like when Arctype is in use:

![](https://arctype.com/blog/content/images/size/w1000/2021/08/image-10.png)

And that's not even it! If you so desire, you can edit constraints or foreign keys as well: all without worrying about running queries that modify them for you!

![](https://arctype.com/blog/content/images/2021/08/image-11.png)

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com