MySQL null的一些易錯(cuò)點(diǎn)

依據(jù)null-values,MySQL的值為null的意思只是代表沒有數(shù)據(jù),null值和某種類型的零值是兩碼事,比如int類型的零值為0,字符串的零值為””,但是它們依然是有數(shù)據(jù)的,不是null.

我們在保存數(shù)據(jù)的時(shí)候,習(xí)慣性的把暫時(shí)沒有的數(shù)據(jù)記為null,表示當(dāng)前我們無法提供有效的信息.

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

使用null的易錯(cuò)點(diǎn)

下面我摘取MySQL官方給出的null的易錯(cuò)點(diǎn)做講解.

對MySQL不熟悉的人很容易搞混null和零值

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

比如下面這2句SQL產(chǎn)生的數(shù)據(jù)是獨(dú)立的

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

第一句SQL只是表示暫時(shí)不知道電話號(hào)碼是多少,第二句是電話號(hào)碼知道并且記錄為''

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的邏輯判斷要單獨(dú)處理

對于是否為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.

如果你使用=判斷,那么永遠(yuǎn)是false

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

比如你這樣寫,where后判斷的結(jié)果永不會(huì)是true:

SELECT * FROM my_table WHERE phone = NULL;

如果你使用null和其他數(shù)據(jù)做計(jì)算,那么結(jié)果永遠(yuǎn)是null,除非MySQL文檔對某些操作做了額外的特殊說明

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

對有null值的列做索引要額外預(yù)料到隱藏的細(xì)節(jié)

只有InnoDB,MyISAM,MEMORY 存儲(chǔ)引擎支持給帶有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.

索引的長度會(huì)比普通索引大1,也就是略微耗內(nèi)存點(diǎn)

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值做分組,去重,排序會(huì)被特殊對待

和上文講的=null永遠(yuǎn)是false相反,這時(shí)null 被認(rèn)為是相等的.

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

對null排序會(huì)被特殊對待

null值要么被排在最前面,要么最后面

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

聚合操作時(shí)null被忽略

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

例如count(*)不會(huì)統(tǒng)計(jì)值為null的數(shù)據(jù).

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的一些易錯(cuò)點(diǎn)的詳細(xì)內(nèi)容，更多關(guān)于MySQL null的資料請關(guān)注腳本之家其它相關(guān)文章！