Replace all NULL values to empty in MYSQL Jointure

Question

I am developing an archiving module. To do so I use a SELECT query which includes many LEFT JOINTURE to combine data from many tables. These data are then inserted into one single archive table. Depending on the data, I may have fields whose value is NULL. As I would like to avoid NULL values in that table (I read many times that this impacts performance), I would like to systematically change NULL in empty string in the result of my SELECT query.

I have found that the COALESCE does that job but, as far as I know, this could be applied on one field only. It has to repeated to every field. But I have more than 50 fields, I would end up with an endless query.. Is there any way to systematically replace NULL values by an empty string in SELECT query ?

Thanks!

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

It is not a good idea. Do not replace NULLs with empty string.

NULL value has the meaning: this field is not set, not filled in. Empty value means it has been filled and is deliberately empty.

See this answer.

edited May 23 '17 at 10:33

Community

1
1

answered Nov 15 '14 at 07:37

Vojtěch Dohnal

7,867
3
43
105

Dear both, thanks for your comments. I will keep the NULL values even if the distinction between NULL and blank value is not really meaningful in my archive table. – Pierre Nov 15 '14 at 14:34

score 0 · Answer 2 · answered Nov 15 '14 at 10:22

Personally, I'd leave the values as NULL, as I think it is better for the applications feeding off the archived table to be able to distinguish between a NULL and a blank value, and for their presentation layers to determine how to display a NULL.

If I had a very compelling reason to convert them, I'd just code the 50 COALESCE functions and be done with it. You could have finished that in almost the same amount of time as asking the question here.

Some databases will let you handle such conversions with triggers or default values defined at the table level, but that is more effort and likely to introduce performance problems if you use triggers to perform the NULL conversions.

Max Makhrov · Answer 3 · 2020-11-27T08:38:34.313

This may help to generate SQL

SELECT 
concat(
  'coalesce(', COLUMN_NAME, ',',    
    CASE 
        WHEN DATA_TYPE in ('bigint', 'int', 'tinyint', 'decimal', 'double', 'float', 'smallint') 
                                     THEN 0
        WHEN DATA_TYPE = 'date'      THEN 'STR_TO_DATE("1900-01-01", "%Y-%m-%d")'
        WHEN DATA_TYPE in('timestamp', 'datetime')  
                                     THEN 'STR_TO_DATE("1900-01-01 12:00:00", "%Y-%m-%d %h:%i:%s")'
        ELSE '''''' -- everything else get's an empty string
    END,
    ') as ', COLUMN_NAME
) as generated_sql
  
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_name'

Replace all NULL values to empty in MYSQL Jointure

3 Answers3