MySQL-load data infile- invalid data validation

Question

Is it possible to validate the data and load it in a table using load data infile?

This is the query

load data local infile 'C:/Users/970685/Desktop/SFcheck.csv'
into table wow_macro.starting_footage_stg
fields terminated by ','
IGNORE 1 LINES
FIELDS ESCAPED BY \N
(store_id,category_id,footage,plan_id,@merch_id) ;

This is data in CSV:

store_id category-id footage plan_id merch_id
1 1 3 1 fdfd

All the fields are INT in the table but when I execute the query the value 'fdfd' is not validated but saved as 0. (I have also given not null in the table and have not set any default value)

Answer 1

You can validate and have check the values before inserting. Check the example below:

load data infile ‘path/to/file.csv’ into table TABLENAME fields terminated by ‘,’ (col1,col2,@TRUEFALSECOLVAR) set TRUEFALSECOL=case @TRUEFALSECOLVAR when ‘true’ then 0 when ‘false’ then ‘1’ end;

More load data infile usecases.