Automatic Empty to Null Conversion

[mlager]

Is there any way to have empty values converted to NULL on an insert or an update? I've got an application that will inserted '' rather than NULL if a textbox is left blank. My desire is to have it insert a NULL. I was hoping to find a solution that would enable me to not have to do all this checking in my code and do it at the DB level, possibly a trigger?

 

[r937]

this should be done in your application code before the data reaches mysql

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GuardianOfTheFlame]

I also think it should be done by your application

However, you can force the NULL values using before triggers.
Here is an example:

DELIMITER $$
CREATE TRIGGER t_insert_before_table BEFORE INSERT ON table 
    FOR EACH ROW BEGIN
    IF NEW.Textfield = '' THEN
       NEW.Textfield=NULL;
    END IF;
END;
$$

DELIMITER ;

NEW is the record that will be inserted in the table.
In UPDATE TRIGGER you have NEW that contains the variable OLD that contains the values before the update)

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)