CODEWITHSUNDEEP
mysqlsqlnull
Pierre
Pierre

Reputation: 1246

Replace all NULL values to empty in MYSQL Jointure

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!

Upvotes: 1

Views: 998

Answers (3)

Max Makhrov
Max Makhrov

Reputation: 18707

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'

Upvotes: 0

Mike Jones
Mike Jones

Reputation: 532

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.

Upvotes: 0

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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.

Upvotes: 1

Related Questions