MySQL, better to insert NULL or empty string?

by

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

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. How to create a MySQL hierarchical recursive query?
  3. Error related to only_full_group_by when executing a query in MySql
  4. SQL split values to multiple rows
  5. How to reset AUTO_INCREMENT in MySQL
  6. How to declare a variable in MySQL?
  7. MySQL Query GROUP BY day / month / year
  8. MySQL Cannot Add Foreign Key Constraint
  9. Grouping into interval of 5 minutes within a time range
  10. SQL counting all rows instead of counting individual rows
  11. How do I use on delete cascade in mysql?
  12. Adding a leading zero to some values in column in MySQL
  13. Using SQL to determine word count stats of a text field
  14. Update one MySQL table with values from another
  15. MySQL/MariaDB – order by inside subquery
  16. How to transpose mysql table rows into columns
  17. View’s SELECT contains a subquery in the FROM clause
  18. MySQL Group By and Sum total value of other column
  19. MySQL incorrect key file for tmp table when making multiple joins
  20. Does the order of conditions in a WHERE clause affect MySQL performance?
  21. Difference between these two joining table approaches?
  22. MySQL Stored Procedures not working with SELECT (basic question)
  23. MySQL query, MAX() + GROUP BY
  24. MySQL export into outfile : CSV escaping chars
  25. Simple Random Samples from a MySQL Sql database
  26. ALTER table – adding AUTOINCREMENT in MySQL
  27. MySQL Select minimum/maximum among two (or more) given values
  28. MySQL: Computed Column
  29. mySQL query to search all tables within a database for a string?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment