Copyright (C) 2000-2012 |
GNU Info (mysql.info)Problems with NULLProblems with `NULL' Values --------------------------- The concept of the `NULL' value is a common source of confusion for newcomers to SQL, who often think that `NULL' is the same thing as an empty string `'''. This is not the case! For example, the following statements are completely different: mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (""); Both statements insert a value into the `phone' column, but the first inserts a `NULL' value and the second inserts an empty string. The meaning of the first can be regarded as "phone number is not known" and the meaning of the second can be regarded as "she has no phone". In SQL, the `NULL' value is always false in comparison to any other value, even `NULL'. An expression that contains `NULL' always produces a `NULL' value unless otherwise indicated in the documentation for the operators and functions involved in the expression. All columns in the following example return `NULL': mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL); If you want to search for column values that are `NULL', you cannot use the `=NULL' test. The following statement returns no rows, because `expr = NULL' is FALSE, for any expression: mysql> SELECT * FROM my_table WHERE phone = NULL; To look for `NULL' values, you must use the `IS NULL' test. The following shows how to find the `NULL' phone number and the empty phone number: mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ""; In MySQL, as in many other SQL servers, you can't index columns that can have `NULL' values. You must declare such columns `NOT NULL'. Conversely, you cannot insert `NULL' into an indexed column. When reading data with `LOAD DATA INFILE', empty columns are updated with `'''. If you want a `NULL' value in a column, you should use `\N' in the text file. The literal word `'NULL'' may also be used under some circumstances. Note: `LOAD DATA'. When using `ORDER BY', `NULL' values are presented first. If you sort in descending order using `DESC', `NULL' values are presented last. When using `GROUP BY', all `NULL' values are regarded as equal. To help with `NULL' handling, you can use the `IS NULL' and `IS NOT NULL' operators and the `IFNULL()' function. For some column types, `NULL' values are handled specially. If you insert `NULL' into the first `TIMESTAMP' column of a table, the current date and time is inserted. If you insert `NULL' into an `AUTO_INCREMENT' column, the next number in the sequence is inserted. automatically generated by info2www version 1.2.2.9 |