9.3.20 NULLThis is NOT the latest copy of this book; click here for the latest version.
When MySQL wants to tell you that a field contains an unknown value, it uses the special value NULL. NULL does not mean "nothing" or "empty" - those are values in their own right. NULL simply means that MySQL does not know what value a field should be set to, and is used if you do not supply field values.
Take a look at this SQL query and result:
mysql> SELECT 1 < NULL, 1 > NULL, NULL = NULL, NULL != NULL;
+----------+----------+-------------+--------------+
| 1 < NULL | 1 > NULL | NULL = NULL | NULL != NULL |
+----------+----------+-------------+--------------+
| NULL | NULL | NULL | NULL |
+----------+----------+-------------+--------------+
As you can see, you cannot meaningfully use normal comparison operators with NULL values, because they will all return NULL. The reason for this is because NULL means "unknown", and MySQL does not know whether 1 is greater than an unknown value, whether one unknown value is equal to another unknown value, etc.
To solve this problem, MySQL has two special operators, IS NULL and IS NOT NULL that allow you to compare against the NULL value. Here is an example of them in use:
SELECT ID FROM usertable WHERE FirstName IS NULL;
NULL values can be very tricky to use, and you should try to avoid them if possible. Provide values for all your fields, even if they are empty values.
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
