Wondering how to load data infile mysql auto increment? Our in-house experts are here to help you out with this article. Our MySQL support is here to offer a lending hand with your queries and issues.

How to load data infile mysql auto increment?

The best thing to do is just include the 2 non-auto-increment columns in the CSV, and then explicitly set the ID column to NULL in the load data infile statement.

Something like this:

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(AField, BField)
SET ID = NULL;

you can “omit” ID field in data row by placing delimiter sign in the beginning of a row, like this (for table schema ID,AField,BField):

,afieldvalue,bfieldvalue
...

SQL engine will assign NULL to ID field while reading such csv file

Try to use NULL as the value in the csv file for the ID field.

NULL in CSV file is read as a STRING in MySQL. If you want to have null values in a MySQL column instead use \N in place of `NULL. This will fix the issue and you will get the required result.

If you have only a small number of columns, you can just name them: LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable (AField); (assuming only ID & AField)

LINES TERMINATED BY '\r\n' 

That was the key to success for me when using a text file

Loading AUTO_INCREMENT values or not

When loading information from a CSV file it is possible to load the auto incremented values or allow new values to generate and associated with the data load. In other words we can allow the auto increment default behaviour to automatically generate the values.

mysql> DROP TABLE IF EXISTS names;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE names
    ->   (
    ->      id   INT AUTO_INCREMENT,
    ->      name VARCHAR(100),
    ->      PRIMARY KEY(id)
    ->   )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.03 sec)

Consider the table above. Imagine we need to populate this table with a set of names but without caring about the “id” and the value automatically generated for this field. For the following file we have just names:

$ cat name.txt
Unknown
Simpson
Walker
$

We want to load this into the table but allow the “id” field to auto generate the values. Then we issue the following command:

mysql> LOAD DATA INFILE 'name.txt' INTO TABLE names LINES TERMINATED BY '\n' (name);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   |   
+----+----------+
3 rows in set (0.00 sec)

Notice the “(name)” after the line terminated definition. This indicates that the data contained in the file is to associate with this particular field.

So far so good. But what happens if we need to populate values stored in file in addition to the name. For the most part this is still handled the same way as previously. This time there is no field names defined in the LOAD DATA INFILE statement:

The file contains the following data:

$ cat names.txt
1 Unknown
2 Simpson
3 Walker
$

So loading this up we get :

mysql> LOAD DATA INFILE '/home/rcashell/mysql/csv/names.txt' INTO TABLE names LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   |    
+----+----------+

The next value of the auto increment adjusts to the largest value of the AUTO_INCREMENT field and adds 1.

mysql> insert into names(name) values ('Tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | Tom      | 
+----+----------+
4 rows in set (0.00 sec)

So far so good. But what happens if we add an AUTO_INCREMENT value of 0. 0 or NULL is used by MySQL AUTO_INCREMENT fields to auto generate a value. Consider the following:

$ cat name.txt
0 Peter
$

We would expect Peter to add into our table with the “id” of 0.

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | Tom      | 
+----+----------+
4 rows in set (0.00 sec)

mysql> LOAD DATA INFILE 'name.txt' INTO TABLE names;
Query OK, 1 row affected (0.02 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | tom    | 
|  5 | Peter    | 
+----+----------+
5 rows in set (0.00 sec)

But what if we actually need the value to be added with the “id” equal to 0. In this case we need to inform MySQL of the exception. This is done through their SQL_MODE parameter settings.

Setting the session SQL_MODE as follows will overcome this issue:

mysql> SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

So reloading this same information as before we now get:

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | Tom      | 
|  5 | Peter    | 
+----+----------+
5 rows in set (0.00 sec)

mysql> SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE 'name.txt' INTO TABLE names;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  0 | Peter    | 
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   |   
|  4 | Tom      | 
|  5 | Peter    | 
+----+----------+
6 rows in set (0.00 sec)

[Looking for a solution to another query? We’re happy to help.]

Conclusion

In this article, we provide a quick and simple solution from our Support team to how to load data infile mysql auto increment