when using LOAD DATA LOCAL and having a date in the first column then it always stores '0000-00-00 00:00:00' for only the very first row.
When placing a string column as very first column instead everything works fine. See cases A and B:
A) this first two rows in csv:
2022-01-31,hello_world
2022-01-31,hello_stackover
lead to this data in the mysql table:
0000-00-00 00:00;00,hello_world
2022-01-31 00:00:00,hello_stackover
B) this first two rows in csv:
hello_world,2022-01-31
hello_stackover,2022-01-31
lead to this data in the mysql table:
hello_world,2022-01-31 00:00:00
hello_stackover,2022-01-31 00:00:00
This is my sql:
LOAD DATA LOCAL
INFILE 'myfile.csv'
INTO TABLE mydatabase.`mytable`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
(my_date, any_string_column);
Any idea why 0000-00-00 00:00:00 only happens for the very first row and only when it is the very first column? Thanks.
Edit: 8.0.30-0ubuntu0.20.04.2
CodePudding user response:
Your editor is saving the file with a BOM (Byte Order Mark) character at the beginning, which MySQL doesn't recognize. You need to disable this setting.