Can MySQL automatically convert empty strings to NULL?

php mysql forms sql-null
DatsunBing picture DatsunBing · Nov 15, 2012 · Viewed 15.7k times · Source

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.

Answer

Wouter van Nifterick picture Wouter van Nifterick · Nov 15, 2012

You can enclose your strings with NULLIF()

You use it like this: 

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

Related questions

MySQL Query Within JavaScript

I am working on a form whereby JavaScript is used to add another line item. The purpose is to add line items to an invoice. I have successfully got this to work ok when using text boxes in my form, …

Read More
Get Contact Form 7 Form ID In PHP

I'm using contact form 7 to load two different forms into a page and then, in addition to sending the email, dynamically adding that information to a database. Unfortunately, because of the plugin, I can't simply just create all inputs with …

Read More
Can't find an input type=image value in $_POST

Well may be it is to easy question but: I want to sort the numbers by clicking an image. I thought that i make a form and add an imagefield. <form id="form1" name="form1" method="post" action="index.…

Read More