MySQL – LOAD DATA INFILE with auto_increment


[adsense id=”0514458240″ width=”468″ height=”60″]

I have been asked what happens if we attempt to load information from a CSV file where the data contained in the file contains the auto_increment values.

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 be generated and associated with the data being loaded. 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
Short
Petersen
Young
$

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, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
+----+----------+
6 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 be associated 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
4	Short
5	Petersen
6	Young
$

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, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
+----+----------+

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 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
|  7 | Tom      | 
+----+----------+
7 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 be added into our table with the “id” of 0.

mysql> select * from names;
+----+----------+
| id | name     |
+----+----------+
|  1 | Unknown  | 
|  2 | Simpson  | 
|  3 | Walker   | 
|  4 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
|  7 | Tom      | 
+----+----------+
7 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 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
|  7 | Tom      | 
|  8 | Peter    | 
+----+----------+
8 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 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
|  7 | Tom      | 
|  8 | Peter    | 
+----+----------+
8 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 | Short    | 
|  5 | Petersen | 
|  6 | Young    | 
|  7 | Tom      | 
|  8 | Peter    | 
+----+----------+
9 rows in set (0.00 sec)