MySQL, better to insert NULL or empty string?
mysql, sql, sql-null
Solution
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`.
`NULL`s are sorted before the empty strings.
`COUNT(message)` will count empty strings but not `NULL`s
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 `NULL`s, you'll have to use other query:
SELECT *
FROM mytable
WHERE mytext IS NULL
Problem
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?