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!

Community · Accepted Answer · 2017-05-23 10:33:41Z

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, 2017 at 10:33

CommunityBot

11 silver badge

answered Nov 15, 2014 at 7:37

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pierre Over a year ago

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.

Mike Jones · Accepted Answer · 2014-11-15 10:22:28Z

0

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.

answered Nov 15, 2014 at 10:22

Mike Jones

5422 silver badges9 bronze badges

Comments

Max Makhrov · Accepted Answer · 2020-11-27 08:38:34Z

0

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'

edited Nov 27, 2020 at 8:38

answered Nov 27, 2020 at 8:22

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Collectives™ on Stack Overflow

Replace all NULL values to empty in MYSQL Jointure

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related