MySQL, better to insert NULL or empty string?

Question

I have a form on a website which has a lot of different fields. Some of the fields are optional while some are mandatory. In my DB I have a table which holds all these values, is it better practice to insert a NULL value or an empty string into the DB columns where the user didn't put any data?

Answer 1

To differentiate between "put no data" and "put empty data", you can use NULL.

Another differences are as follows:

• The LENGTH of an empty string is 0 and a LENGTH of NULL is NULL, 
• NULLs will be sorted before the empty strings.
• COUNT(message) can count empty strings but not NULLs.
• By using a bound variable, you can search for an empty string but not for a NULL. 

Query:

SELECT  *

FROM    mytable 

WHERE   mytext = ?

In above query, whatever value you pass from the client it will never match a NULL in mytext, . 

To match NULLs, use below Query:

SELECT  *

FROM    mytable 

WHERE   mytext IS NULL