Search code examples
mysqlmariadbdatabase-administration

Is there a way to replace empty string into NULL value in MariaDB?

here is a simplified version of my question:

csv file:

| id | colA | colB |
| 1  |      |  1.5 |
| 2  |      |  2.2 |
| 3  |  3.3 |  3.5 |

...

I am trying to perform a "load data local infile" operation, but I keep getting warnings on "colA"'s first two entries: because they are empty strings for DB when the DB read the file.

Is there a way I can replace them during the load data step?

Thank you so much!

Solution

  • Use a user variable to transform the column

    LOAD DATA LOCAL INFILE "filename"
INTO TABLE tablename
(id, @colA, @colB)
SET colA = NULLIF(@colA, ''), colB = NULLIF(@colB, '')