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

By using NULL you can distinguish between "put no data" and "put empty data".

Some more differences:

• A LENGTH of NULL is NULL, a LENGTH of an empty string is 0.

• NULLs are sorted before the empty strings.

• COUNT(message) will count empty strings but not NULLs

• You can search for an empty string using a bound variable but not for a NULL. This query:

  SELECT  *
  FROM    mytable 
  WHERE   mytext = ?
  

  will never match a NULL in mytext, whatever value you pass from the client. To match NULLs, you'll have to use other query:

  SELECT  *
  FROM    mytable 
  WHERE   mytext IS NULL