Problem
obligatory. I have a table in my database that contains all of these variables; is it better to insert a NULL value or an empty string into DB columns when the user hasn’t entered any data?
Asked by roflwaffle
Solution #1
You can tell the difference between “put no data” and “place empty data” by using NULL.
Some more differences:
Answered by Quassnoi
Solution #2
If you ever plan on moving databases, keep in mind that Oracle does not accept empty strings. They are automatically transformed to NULL, and you can’t query for them with WHERE somefield = ” clauses.
Answered by Matt Solnit
Solution #3
Keep in mind that NULL can make your codepaths a lot more challenging. Most database adapters / ORMs in Python, for example, map NULL to None.
So things like:
print "Hello, %(title)s %(firstname) %(lastname)!" % databaserow
“Hello, None Joe Doe!” might be the result. You’ll need something like this code to avoid it:
if databaserow.title:
print "Hello, %(title)s %(firstname) %(lastname)!" % databaserow
else:
print "Hello, %(firstname) %(lastname)!" % databaserow
This can make things a lot more difficult.
Answered by max
Solution #4
For consistency in your MySQL database, it is preferable to insert NULL. Foreign keys can be saved as NULL or empty strings, but not both.
An empty string in the constraints will cause problems. To meet a Foreign Key restriction, you may need to insert a bogus record with a unique empty string. I guess it’s a bad habit.
Also see: Is it possible for a foreign key to be NULL and/or duplicate?
Answered by micaball
Solution #5
I’m not sure what the best practice is here, but unless you want null to imply something other than empty-string and the user’s input matches your empty-string definition, I’d go with null.
It’s important to note that I’m saying YOU must determine how you want them to differ. It makes sense to have them different at times, but not always. If not, simply choose one and stick to it. As I have stated, I prefer the NULL most of the time.
Oh, and if the column is null, the record is less likely to appear in virtually any query that selects (in SQL language, contains a where clause) based on that column, unless the selection is for a null field.
Answered by Platinum Azure
Post is based on https://stackoverflow.com/questions/1267999/mysql-better-to-insert-null-or-empty-string