How to use null as default value on a mysql decimal field with LOAD DATA?

Question

I load a CSV file using LOAD DATA LOCAL INFILE. Some values eg for decimals are optional and thus not set in csv.

I tried both inserting a blank ,, in csv, and a ,null,. For both of them the mysql table shows a 0 as decimal value.

CREATE TABLE `my_table` (
     ...
    `price` decimal(10,2) DEFAULT NULL,
)

But why isn't the default value used if I leave the field blank during load?

Sidenote: the linked question is not an answer, as the comment accepted has nothing to do with the special null handling in mysql! Moreover, an acceptable answer to that question should state the solution as a oneliner, so everyone finds a quick solution. Which is also not the case in the linked "duplciate" question.

Possible duplicate of [MySQL load NULL values from CSV data](https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data) — Jan Zeiseweis, Aug 16 '17 at 11:16

membersound · Answer 1 · 2017-08-16T11:24:51.270

0

Found it: mysql is case sensitive here and expects a uppercase ,NULL,. Or ,\N,

edited Aug 16 '17 at 11:24

answered Aug 16 '17 at 11:17

membersound

81,582
193
585
1,120

How to use null as default value on a mysql decimal field with LOAD DATA?

1 Answers1