sql - Load Data Infile - negative decimal truncated (to positive number) -

i having trouble loading decimal data database - specifically, negative numbers getting truncated, , can't figure out.

here query looks like:

> create table if not exists mytable (id int(12) not null auto_increment,                                     mydecimal decimal(13,2),primary key(id)); > load data infile 'data.dat' table mytable fields terminated ';'; 

and data.dat i'm loading:

;000000019.50 ; ;000000029.50-; ;000000049.50 ; 

when completes, giving me warning "data truncated column 'mydecimal' @ row 2." , when @ data, it's stored positive number. ideas how fix this?

the best way handle data abnormalities in input file load them local variable, set actual column value based on transformation of local variable.

in case, can load strings local variable, either leave alone or multiply negative 1 depending on whether ends minus sign.

something should work you:

load data infile 'data.dat'  table mytable fields terminated ';'  (id,@mydecimal) set mydecimal = if(@mydecimal '%-',@mydecimal * -1,@mydecimal);