MySQL: how to handle the mismatching numbers of fields when importing data

In the last post, we showed how to discover the warnings generated by importing data into the database. We know that LOAD DATA has some advantage over the mysqlimport command.

In this post, we show some more advantage of using LOAD DATA to import data into MySQL DB.

There is one kind of warnings when the imported data have more fields than the number of columns specified, i.e.:

Warning - 1262 - Row 1 was truncated; it contained more data than there were input columns

To eliminate this warning, we can discard the extra input value by assigning it to a dummy user variable:

LOAD DATA INFILE 'MyTable1.txt' INTO TABLE MyTable1 (MyColumn1, @dummy, MyColumn2, @dummy, MyColumn3);

There is another case that the table has more columns than the fields of the data in the file. And LOAD DATA allows us to set a value for that extra column during the import, e.g.:

LOAD DATA INFILE 'MyTable1.txt' INTO TABLE MyTable1 (MyColumn1, @dummy, MyColumn2, @dummy, MyColumn3) SET MyColumn4 = MyColumn3 * 10;

This can also be achieved by using the mysqlimport command:

mysqlimport --local --columns=MyColumn1,@dummy,MyColumn2,@dummy,MyColumn3 MyDatabase MyTable.txt