I have a csv file where some integer fields are blank (e.g. , ,) or have an NA value (e.g. ,NA ,). When I load the data (LOAD DATA LOCAL INFILE...) into a table MySQL correctly warns that the fields have incorrect integer values, however it inserts a 0, not the DEFAULT value specified in the CREATE TABLE DDL, into the columns. Is there any way I can specify the value to use in case of incorrect values in a data file?


When you need to 'cleanse' the incoming data, there are two techniques to consider. Suppose the column foo needs to be -1 to represent "NA":

Using @variables...

LOAD into and @variable, then use SET to manipulate the value while storing it. There are examples in the docs. In your case you might use something like SET foo := IF(@foo = 'NA', -1, @foo) inside the LOAD DATA statement.

Using a temp table...

Instead of LOADing directly into the target table, load into a separate table (possible TEMPORARY). Then have a few SQL statements to manipulate the data in that table before copying to the "real" table. In your case: UPDATE tmp SET foo = -1 WHERE foo = 'NA';. Then INSERT INTO real SELECT * FROM tmp;. Note foo would be VARCHAR in tmp in spite of being INT in real.

The temp table approach can handle more complex transformations.

  • Two excellent suggestions, thank you. It would be nice if there were an option to LOAD/INSERT to use a column's default value in case of incorrect data.
    – Lee
    Sep 28 '17 at 18:08
  • bugs.mysql.com accepts "feature requests".
    – Rick James
    Sep 28 '17 at 23:26

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .