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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.