phpmysqlformssql-null

Can MySQL automatically convert empty strings to NULL?

There is an excellent SO on MySQL empty strings versus NULL here, MySQL, better to insert NULL or empty string?, however it doesn't take in to account 'uniformity' - i.e. if you want to have just one choice in your tables (i.e. empty string OR NULL), which should it be?

My question is, can I get MySQL to automatically store empty strings as NULLs?

After reading the previous SO I am generally inclined to store NULL but the problem is that I have a lot of PHP forms with optional fields, and (when left blank) these return empty strings.

Solution

  • You can enclose your strings with NULLIF().

    You use it like this:

    NULLIF('test','') --> returns 'test'
NULLIF(''    ,'') --> returns NULL