Best practice for importing a .sql file with error on MySQL

Question

I am trying to import a .sql file to MySQL using this command line

mysql -u username -p databasename < path/myfile.sql

Here is a sample of myfile.sql

INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3798,'0',35,'0000-00-00','test','test','1',108,'021752f8-7194-4a4b-8919-fb8216ecba07');
INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3799,'0',9.4,'2018-05-02','CB Frichti','CB Frichti','1',108,'316a3da6-821b-42c8-80ea-3c1ac437484e');
INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3800,'0',50,'0000-00-00','test','test','1',108,'316a3da6-821b-42c8-80ea-3c1ac437484e');

My file contains some date but some of those are wrong date 0000-00-00. Therefore the command above returns an error.

What is the best way to import ? Should I remove the line where date is wrong ? Is there a way to import only import lines that are correct and ignore those with error ?

Thanks for your help

Answer 1

As stated here

You can use the --force (-f) flag on your mysql import. Rather than stopping, MySQL will continue and just log the errors to the console.

For example:

mysql -u username -p -f -D path/myfile.sql < path/myfile.sql

Replacing invalid values with a default value (e.g., 1900-01-01) is not a good practice, you should prefer NULL instead