为什么表设计时必须把字段定义为NOT NULL并设默认值

灰太狼 2024-05-23 20:31 59阅读 0赞

## 为什么表设计时必须把字段定义为NOT NULL并设默认值 ##

### 空间占用的坑 ###

*  空’'在[存储过程][Link 1]中是不会占用空间的，但是NULL会。就像一个杯子，空表示杯子是真空的，NULL表示装的空气。
 *      mysql> SELECT length('1'),length(NULL),length('');
        
        +-------------+--------------+------------+
        | length('1') | length(NULL) | length('') |
        +-------------+--------------+------------+
        |           1 | NULL         |          0 |
        +-------------+--------------+------------+
        1 row in set
        
        mysql>

### 查询的坑 ###

*  如果要查询表的NULL需要使用 `is null`或`is not null`，如果直接使用`=/!=/in/not inl`将查询不到值
 *      mysql> SELECT * FROM `user`;
        +----+------+-----+---------+
        | id | name | age | address |
        +----+------+-----+---------+
        |  1 | wyf  |  32 | 合肥    |
        |  2 | xx   |  31 | 北京    |
        |  3 | yy   |  30 | 上海    |
        |  4 | zz   |  11 |         |
        |  5 | aa   |  21 | NULL    |
        +----+------+-----+---------+
        5 rows in set
        
        mysql> SELECT * FROM `user` WHERE address IS NULL;
        +----+------+-----+---------+
        | id | name | age | address |
        +----+------+-----+---------+
        |  5 | aa   |  21 | NULL    |
        +----+------+-----+---------+
        1 row in set
        
        mysql> SELECT * FROM `user` WHERE address = NULL;
        Empty set
        
        mysql>

### NULL统计的坑 ###

*  如果使用`count()`等统计函数，将不会统计NULL。如下，一共有5条数据，统计address就只返回4。
 *      mysql> SELECT * FROM `user`;
        +----+------+-----+---------+
        | id | name | age | address |
        +----+------+-----+---------+
        |  1 | wyf  |  32 | 合肥    |
        |  2 | xx   |  31 | 北京    |
        |  3 | yy   |  30 | 上海    |
        |  4 | zz   |  11 |         |
        |  5 | aa   |  21 | NULL    |
        +----+------+-----+---------+
        5 rows in set
        
        mysql> SELECT COUNT(address) FROM `user`;
        +----------------+
        | COUNT(address) |
        +----------------+
        |              4 |
        +----------------+
        1 row in set
        
        mysql>

### 索引的坑 ###

*  Mysql的索引会为NULL值做特殊处理，导致整个索引的查询效率下降。如果是语句中有 `is null`会使用索引，如果语句中有`is not null`则会导致索引失效，如下：
 *  **查看索引：**
 *      mysql> show index from user;
        +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        | Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
        +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        | user  |          0 | PRIMARY     |            1 | id          | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
        | user  |          1 | idx_name    |            1 | name        | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
        | user  |          1 | idx_address |            1 | address     | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
        +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        3 rows in set
 *  is null **会使用索引**
 *      mysql> explain select * from user where address is null;
        +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
        | id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
        +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
        |  1 | SIMPLE      | user  | NULL       | ref  | idx_address   | idx_address | 1023    | const |    1 |      100 | Using index condition |
        +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
        1 row in set
 *  is not null **不会使用索引**
 *      mysql> explain select * from user where address is not null;
        +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
        | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
        +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
        |  1 | SIMPLE      | user  | NULL       | ALL  | idx_address   | NULL | NULL    | NULL |    5 |       80 | Using where |
        +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
        1 row in set
        
        mysql>

[Link 1]: https://so.csdn.net/so/search?q=%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B&spm=1001.2101.3001.7020

发表评论取消回复

表情：

评论列表（有 0 条评论，59人围观）

还没有评论，来说两句吧...

相关阅读

相关为什么表设计时必须把字段定义为NOT NULL并设默认值

空’'在中是不会占用空间的，但是NULL会。就像一个杯子，空表示杯子是真空的，NULL表示装的空气。

灰太狼/ 2024年05月23日 20:31/ 0 赞/ 60 阅读

相关 MySQL查询字段为空(null)时设置默认值

文章目录 1 摘要 2 代码示例 3 参考文档 1 摘要根据项目需要,在数据库查询时，可能需要对查

怼烎@/ 2023年10月09日 12:32/ 0 赞/ 24 阅读

相关 Sql查询时字段为null值的并赋值-postgre

Sql查询时字段为null值的并赋值 > SELECT > > COALESCE(字段名，‘赋值’) 别名 > > FROM 表名 > > WHER

缺乏、安全感/ 2023年02月24日 12:29/ 0 赞/ 456 阅读

相关为什么数据库字段要使用NOT NULL？

最近刚入职新公司，发现数据库设计有点小问题，数据库字段很多没有NOT NULL，对于强迫症晚期患者来说，简直难以忍受，因此有了这篇文章。基于目前大部分的开发现状来说，我们都

妖狐艹你老母/ 2022年11月16日 15:27/ 0 赞/ 198 阅读

相关为什么 MySQL 不推荐默认值为 null ？

![e52a04fc0ecb28b146488d6231cc131e.png][] 作者 | guangsu. 来源 | https://blog.csdn.net/

妖狐艹你老母/ 2022年08月28日 13:54/ 0 赞/ 268 阅读

相关数据库字段为时间时默认值的设置

类型：datetime <table> <thead> <tr> <th>名</th> <th>类型</th> <th>长度</th>

╰+攻爆jí腚メ/ 2022年04月24日 06:50/ 0 赞/ 576 阅读

相关修改表：为表中现有的字段设置默认值

第一种情况：表中并没数据 alter table 表名 alter column 字段 set default 0; 第二种情况：是表中已存在数据 upd

╰+攻爆jí腚メ/ 2022年02月04日 01:01/ 0 赞/ 367 阅读

相关 SQOOP 解决import时字段为NULL值的情况

想必有些朋友刚开始接触sqoop时，在从mysql(或者别的RDBMS)导入数据到hdfs后会发现原来在mysql中字段值明明是NULL, 为什么到hive查询后 where

绝地灬酷狼/ 2022年02月01日 12:05/ 0 赞/ 387 阅读

相关 mysql 把弄字段值置为NULL

update tablename set col=null where 条件更多内容： oracle 中 NULL , '' 是相等的。 mysql 中，上面二者是不相

曾经终败给现在/ 2022年01月29日 15:15/ 0 赞/ 448 阅读

相关 postgresql 修改表的字段由NOT NULL修改为NULL

postgresql 修改表的字段由NOT NULL修改为NULL： alter table mytable alter test drop not null;

古城微笑少年丶/ 2022年01月11日 10:57/ 0 赞/ 1005 阅读