Last Updated: 2024-11-23

I wanted to load data from a csv file using mysql. I created a script using the LOAD DATA command:

 LOAD DATA LOCAL INFILE './data/mietendeckel_wohnlage.csv'
 INTO TABLE mietendeckel_wohnlage
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 CHARACTER SET UTF8
 (postcode, street, house_number, quality)
 SET created_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP;

Unfortunately, this complained of a syntax error. But - based on my understanding of syntax at the time -I could see nothing of the sort.

The docs for the command showed the following:

LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
    [, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},

The only difference I could see between that and what I had was that they pu tthe CHARACTER SET line above the IGNORE line but I did not. So I tried switching them around - and it worked!

 LOAD DATA LOCAL INFILE './data/mietendeckel_wohnlage.csv'
 INTO TABLE mietendeckel_wohnlage
 CHARACTER SET UTF8
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (postcode, street, house_number, quality)
 SET created_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP;

Lesson

The moral of the story is that argument order matters in SQL. Even though it's written closer to English than something like Python is, SQL is, in fact, actually super strict about argument order.