MySQL null的一些易錯點



不過使用null但是時候,需要我們註意一些問題.對此MySQL文檔說明如下: problems-with-null




The concept of the NULL value is a common source of confusion for newcomers to SQL


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 “the person is known to have no phone, and thus no phone number.”


對於是否為null的判斷必須使用專門的語法IS NULL,IS NOT NULL,IFNULL().

To help with NULL handling, you can use the IS NULL and IS NOT NULL operators and the IFNULL() function.


In SQL, the NULL value is never true in comparison to any other value, even NULL

To search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true


SELECT * FROM my_table WHERE phone = 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


mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);
| NULL | 1+NULL | CONCAT('Invisible',NULL) |
| NULL |  NULL | NULL           |
1 row in set (0.00 sec)

所以你要對null做邏輯判斷,還是乖乖的使用IS NULL

To look for NULL values, you must use the IS NULL test


隻有InnoDB,MyISAM,MEMORY 存儲引擎支持給帶有null值的列做索引

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.


Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column.


和上文講的=null永遠是false相反,這時null 被認為是相等的.

When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are regarded as equal.



When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order.


Aggregate (group) functions such as COUNT(), MIN(), and SUM() ignore NULL values


The exception to this is COUNT(*), 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 the age column:

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

以上就是MySQL null的一些易錯點的詳細內容,更多關於MySQL null的資料請關註WalkonNet其它相關文章!