Movatterモバイル変換

The concept of theNULL value is a common source of confusion for newcomers to SQL, who often think thatNULL 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 thephone column, but the first inserts aNULL 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“the person is known to have no phone, and thus no phone number.”

To help withNULL handling, you can use theIS NULL andIS NOT NULL operators and theIFNULL() function.

In SQL, theNULL value is never true in comparison to any other value, evenNULL. An expression that containsNULL always produces aNULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression. All columns in the following example returnNULL:

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

To search for column values that areNULL, you cannot use anexpr = NULL test. The following statement returns no rows, becauseexpr = NULL is never true for any expression:

mysql> SELECT * FROM my_table WHERE phone = NULL;

To look forNULL values, you must use theIS NULL test. The following statements show how to find theNULL phone number and the empty phone number:

mysql> SELECT * FROM my_table WHERE phone IS NULL;mysql> SELECT * FROM my_table WHERE phone = '';

SeeSection 5.3.4.6, “Working with NULL Values”, for additional information and examples.

You can add an index on a column that can haveNULL values if you are using theMyISAM,InnoDB, orMEMORY storage engine. Otherwise, you must declare an indexed columnNOT NULL, and you cannot insertNULL into the column.

When reading data withLOAD DATA, empty or missing columns are updated with''. To load aNULL value into a column, use\N in the data file. The literal wordNULL may also be used under some circumstances. SeeSection 15.2.9, “LOAD DATA Statement”.

When usingDISTINCT,GROUP BY, orORDER BY, allNULL values are regarded as equal.

When usingORDER BY,NULL values are presented first, or last if you specifyDESC to sort in descending order.

Aggregate (group) functions such asCOUNT(),MIN(), andSUM() ignoreNULL values. The exception to this isCOUNT(*), which counts rows and not individual column values. For example, the following statement produces two counts. The first is a count of the number of rows in the table, and the second is a count of the number of non-NULL values in theage column:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

For some data types, MySQL handlesNULL values in special ways. For example, if you insertNULL into an integer or floating-point column that has theAUTO_INCREMENT attribute, the next number in the sequence is inserted. Under certain conditions, if you insertNULL into aTIMESTAMP column, the current date and time is inserted; this behavior depends in part on the server SQL mode (seeSection 7.1.11, “Server SQL Modes”) as well as the value of theexplicit_defaults_for_timestamp system variable.