MySQL默認值選型是空,還是 NULL-愛可生


MySQL默認值選型是空,還是 NULL-愛可生

如果對一個字段沒有過多要求,是使用“”還是使用 NULL,一直是個讓人困惑的問題。即使有前人留下的開發規範,但是能說清原因的也沒有幾個。NULL 是“”嗎?在辨別 NULL 是不是空的這個問題上,感覺就像是在證明 1 + 1 是不是等於 2。

在 MySQL 中的 NULL 是一種特殊的數據。一個字段是否允許為 NULL,字段默認值是否為 NULL。

主要有如下幾種情況:

字段類型表定義中設置方式字段值數值類型 (INT/BIGINT)Default NULL / Default 0NULL / NUM字符類型 (CHAR/VARCHAR)Default NULL / Default '' / Default 'ab'NULL / '' / String


1. NULL 與空字符存儲上的區別

表中如果允許字段為 NULL,會為每行記錄分配 NULL 標誌位。NULL 除了在每行的行首存有 NULL 標誌位,實際存儲不佔有任何空間。如果表中所有字段都是非 NULL,就不存在這個標示位了。網上有一些驗證 MySQL 中 NULL 存儲方式的文章,可以參考下。


2. NULL使用上的一些問題。

數值類型,對一個允許為NULL的字段進行min、max、sum、加減、order by、group by、distinct 等操作的時候。字段值為非 NULL 值時,操作很明確。如果使用 NULL, 需要清楚的知道如下規則:

數值類型,以 INT 列為例

1) 在 min / max / sum / avg 中 NULL 值會被直接忽略掉,如下是測試結果,可能 min / max / sum 還比較可以理解,但 avg 真的是你想要的結果嗎?

<code>    CREATE TABLE `t1` (    `id` int(16) NOT NULL AUTO_INCREMENT,    `name` varchar(20) DEFAULT NULL,    `number` int(11) DEFAULT NULL,    PRIMARY KEY (`id`)    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;    select * from t1;    +------+----------+--------+    | id   | name     | number |    +------+----------+--------+    |    1 | zhangsan |   NULL |    |    2 | lisi     |   NULL |    |    3 | wangwu   |      0 |    |    4 | zhangliu |      4 |    +------+----------+--------+    select max(number) from t1;    +-------------+    | max(number) |    +-------------+    |           4 |    +-------------+    select min(number) from t1;    +-------------+    | min(number) |    +-------------+    |           0 |    +-------------+    select sum(number) from t1;    +-------------+    | sum(number) |    +-------------+    |           4 |    +-------------+    select avg(number) from t1;    +-------------+    | avg(number) |    +-------------+    |      2.0000 |    +-------------+/<code>

2) 對 NULL 做加減操作,如 1 + NULL,結果仍是 NULL

<code>    select 1+NULL;    +--------+    | 1+NULL |    +--------+    |   NULL |    +--------+/<code>

3) order by 以升序檢索字段的時候 NULL 會排在最前面(倒序相反)

<code>    select * from t1 order by number;    +----+----------+--------+    | id | name     | number |    +----+----------+--------+    |  1 | zhangsan |   NULL |    |  2 | lisi     |   NULL |    |  3 | wangwu   |      0 |    |  4 | zhangliu |      4 |    +----+----------+--------+    select * from t1 order by number desc;    +----+----------+--------+    | id | name     | number |    +----+----------+--------+    |  4 | zhangliu |      4 |    |  3 | wangwu   |      0 |    |  1 | zhangsan |   NULL |    |  2 | lisi     |   NULL |    +----+----------+--------+/<code>

4) group by / distinct 時,NULL 值被視為相同的值

<code>    select distinct(number) from t1;    +--------+    | number |    +--------+    |   NULL |    |      0 |    |      4 |    +--------+    select number,count(*) from t1 group by number;    +--------+----------+    | number | count(*) |    +--------+----------+    |   NULL |        2 |    |      0 |        1 |    |      4 |        1 |    +--------+----------+/<code>


字符類型,在使用 NULL 值的時候,也需要格外注意

1) 字段是字符時,你無法一目瞭然的區分這個值到底是 NULL ,還是字符串 'NULL'

<code>    insert into t1 (name,number) values ('NULL',5);    insert into t1 (number) values (6);    select * from t1 where number in (5,6);    +----+------+--------+    | id | name | number |    +----+------+--------+    |  5 | NULL |      5 |    |  6 | NULL |      6 |    +----+------+--------+    select name is NULL from t1 where number=5;    +--------------+    | name is NULL |    +--------------+    |            0 |    +--------------+    select name is NULL from t1 where number=6;    +--------------+    | name is NULL |    +--------------+    |            1 |    +--------------+/<code>

2) 統計包含 NULL 字段的值,NULL 值不包括在裡面

<code>    select count(*) from t1;    +----------+    | count(*) |    +----------+    |        6 |    +----------+    select count(name)from t1;    +-------------+    | count(name) |    +-------------+    |           5 |    +-------------+    select * from t1 where name is null;    +----+------+--------+    | id | name | number |    +----+------+--------+    |  6 | NULL |      6 |    +----+------+--------+/<code>

3) 如果你用 length 去統計一個 VARCHAR 的長度時,NULL 返回的將不是數字

<code>    select length(name) from t1 where name is null;    +--------------+    | length(name) |    +--------------+    |         NULL |    +--------------+/<code>


總結:

NULL 本身是一個特殊值,MySQL 採用特殊的方法來處理 NULL 值。從理解肉眼判斷,操作符運算等操作上,可能和我們預期的效果不一致。可能會給我們項目上的操作不符合預期。

你必須要使用 IS NULL / IS NOT NULL 這種與普通 SQL 大相徑庭的方式去處理 NULL。

儘管在存儲空間上,在索引性能上可能並不比空值差,但是為了避免其身上特殊性,給項目帶來不確定因素,因此建議默認值不要使用 NULL

MySQL默認值選型是空,還是 NULL-愛可生


分享到:


相關文章: