MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解

最新推荐文章于 2024-06-10 21:14:09 发布
最新推荐文章于 2024-06-10 21:14:09 发布
阅读量1.4w 收藏 18
点赞数 4
分类专栏： MySQL
原文链接：https://blog.csdn.net/qq_30549099/article/details/107395521
版权

MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解
MySQL不推荐列默认值设置为null到底是为什么

 

参考资料：

https://blog.csdn.net/qq_30549099/article/details/107395521

https://www.jb51.net/article/191848.htm

 

 

必须把字段定义为NOT NULL并且提供默认值

解读：

  • null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
  • null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；
    同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
  • null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识
  • 对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。
    如：where name!=’tom’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

 

通常能听到的答案是使用了NULL值的列将会使索引失效,但是如果实际测试过一下,你就知道IS NULL会使用索引.所以上述说法有漏洞.

着急的人拉到最下边看结论

Preface

Null is a special constraint of columns.
The columns in table will be added null constrain if you do not define the column with “not null” key words explicitly
when creating the table.Many programmers like to define columns by default
because of the conveniences(reducing the judgement code of nullibility) what consequently
cause some uncertainty of query and poor performance of database.

NULL值是一种对列的特殊约束,我们创建一个新列时,如果没有明确的使用关键字not null声明该数据列,Mysql会默认的为我们添加上NULL约束.
有些开发人员在创建数据表时,由于懒惰直接使用Mysql的默认推荐设置.(即允许字段使用NULL值).而这一陋习很容易在使用NULL的场景中得出不确定的查询结果以及引起数据库性能的下降.

Introduce

Null is null means it is not anything at all,we cannot think of null is equal to ‘’ and they are totally different.
MySQL provides three operators to handle null value:“IS NULL”,“IS NOT NULL”,"<=>" and a function ifnull().
IS NULL: It returns true,if the column value is null.
IS NOT NULL: It returns true,if the columns value is not null.
<=>: It’s a compare operator similar with “=” but not the same.It returns true even for the two null values.
(eg. null <=> null is legal)
IFNULL(): Specify two input parameters,if the first is null value then returns the second one.
It’s similar with Oracle’s NVL() function.

NULL并不意味着什么都没有,我们要注意 NULL 跟 ''(空值)是两个完全不一样的值.MySQL中可以操作NULL值操作符主要有三个.

  • IS NULL
  • IS NOT NULL
  • <=> 太空船操作符,这个操作符很像=,select NULL<=>NULL可以返回true,但是select NULL=NULL返回false.
  • IFNULL 一个函数.怎么使用自己查吧…反正我会了

Example

Null never returns true when comparing with any other values except null with “<=>”.
NULL通过任一操作符与其它值比较都会得到NULL,除了<=>.

 1 (root@localhost mysql3306.sock)[zlm]>create table test_null(
 2     -> id int not null,
 3     -> name varchar(10)
 4     -> );
 5 Query OK, 0 rows affected (0.02 sec)
 6 
 7 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(1,'zlm');
 8 Query OK, 1 row affected (0.00 sec)
 9 
10 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(2,null);
11 Query OK, 1 row affected (0.00 sec)
12 
13 (root@localhost mysql3306.sock)[zlm]>select * from test_null;
14 +----+------+
15 | id | name |
16 +----+------+
17 |  1 | zlm  |
18 |  2 | NULL |
19 +----+------+
20 2 rows in set (0.00 sec)
21 // -------------------------------------->这个很有代表性<----------------------
22 (root@localhost mysql3306.sock)[zlm]>select * from test_null where name=null;
23 Empty set (0.00 sec)
24 
25 (root@localhost mysql3306.sock)[zlm]>select * from test_null where name is null;
26 +----+------+
27 | id | name |
28 +----+------+
29 |  2 | NULL |
30 +----+------+
31 1 row in set (0.00 sec)
32 
33 (root@localhost mysql3306.sock)[zlm]>select * from test_null where name is not null;
34 +----+------+
35 | id | name |
36 +----+------+
37 |  1 | zlm  |
38 +----+------+
39 1 row in set (0.00 sec)
40 
41 (root@localhost mysql3306.sock)[zlm]>select * from test_null where null=null;
42 Empty set (0.00 sec)
43 
44 (root@localhost mysql3306.sock)[zlm]>select * from test_null where null<>null;
45 Empty set (0.00 sec)
46 
47 (root@localhost mysql3306.sock)[zlm]>select * from test_null where null<=>null;
48 +----+------+
49 | id | name |
50 +----+------+
51 |  1 | zlm  |
52 |  2 | NULL |
53 +----+------+
54 2 rows in set (0.00 sec)
55  //null<=>null always return true,it's equal to "where 1=1".
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55

Null means “a missing and unknown value”.Let’s see details below.
NULL代表一个不确定的值,就算是两个NULL,它俩也不一定相等.(像不像C中未初始化的局部变量)

 1 (root@localhost mysql3306.sock)[zlm]>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
 2 +-----------+---------------+------------+----------------+
 3 | 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
 4 +-----------+---------------+------------+----------------+
 5 |         0 |             1 |          0 |              1 |
 6 +-----------+---------------+------------+----------------+
 7 1 row in set (0.00 sec)
 8 
 9 //It's not equal to zero number or vacant string.
10 //In MySQL,0 means fasle,1 means true.
11 
12 (root@localhost mysql3306.sock)[zlm]>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
13 +----------+-----------+----------+----------+
14 | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
15 +----------+-----------+----------+----------+
16 |     NULL |      NULL |     NULL |     NULL |
17 +----------+-----------+----------+----------+
18 1 row in set (0.00 sec)
19 
20 //It cannot be compared with number.
21 //In MySQL,null means false,too.
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

It truns null as a result if any expression contains null value.
任何有返回值的表达式中有NULL参与时,都会得到另外一个NULL值.

 1 (root@localhost mysql3306.sock)[zlm]>select ifnull(null,'First is null'),ifnull(null+10,'First is null'),ifnull(concat('abc',null),'First is null');
 2 +------------------------------+---------------------------------+--------------------------------------------+
 3 | ifnull(null,'First is null') | ifnull(null+10,'First is null') | ifnull(concat('abc',null),'First is null') |
 4 +------------------------------+---------------------------------+--------------------------------------------+
 5 | First is null                | First is null                   | First is null                              |
 6 +------------------------------+---------------------------------+--------------------------------------------+
 7 1 row in set (0.00 sec)
 8 
 9   //null value needs to be disposed with ifnull() function,what usually causes sql statement more complex.
 10  //As we all know,MySQL does not support funcion index.Therefore,indexes on the column may not be used.That's really worse.
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

It’s diffrent when using count(*) & count(null column).
使用count(*) 或者 count(null column)结果不同,count(null column)<=count(*).

 1 (root@localhost mysql3306.sock)[zlm]>select count(*),count(name) from test_null;
 2 +----------+-------------+
 3 | count(*) | count(name) |
 4 +----------+-------------+
 5 |        2 |           1 |
 6 +----------+-------------+
 7 1 row in set (0.00 sec)
 8 
 9 //count(*) returns all rows ignore the null while count(name) returns the non-null rows in column "name".
10 // This will also leads to uncertainty if someone is unaware of the details above.
 如果使用者对NULL属性不熟悉,很容易统计出错误的结果.
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

When using distinct,group by,order by,all null values are considered as the same value.
虽然select NULL=NULL的结果为false,但是在我们使用distinct,group by,order by时,NULL又被认为是相同值.

 1 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(3,null);
 2 Query OK, 1 row affected (0.00 sec)
 3 
 4 (root@localhost mysql3306.sock)[zlm]>select distinct name from test_null;
 5 +------+
 6 | name |
 7 +------+
 8 | zlm  |
 9 | NULL |
10 +------+
11 2 rows in set (0.00 sec)
12 
13 //Two rows of null value returned one and the result became two.
14 
15 (root@localhost mysql3306.sock)[zlm]>select name from test_null group by name;
16 +------+
17 | name |
18 +------+
19 | NULL |
20 | zlm  |
21 +------+
22 2 rows in set (0.00 sec)
23 
24 //Two rows of null value were put into the same group.
25 //By default,group by will also sort the result(null row showed first).
26 
27 (root@localhost mysql3306.sock)[zlm]>select id,name from test_null order by name;
28 +----+------+
29 | id | name |
30 +----+------+
31 |  2 | NULL |
32 |  3 | NULL |
33 |  1 | zlm  |
34 +----+------+
35 3 rows in set (0.00 sec)
36 
37 //Three rows were sorted(two null rows showed first).
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

MySQL supports to use index on column which contains null value(what’s different from oracle).
MySQL中支持在含有NULL值的列上使用索引,但是Oracle不支持.这就是我们平时所说的如果列上含有NULL那么将会使索引失效.
严格来说,这句话对与MySQL来说是不准确的.

 1 (root@localhost mysql3306.sock)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest10           |
 7 | sbtest2            |
 8 | sbtest3            |
 9 | sbtest4            |
10 | sbtest5            |
11 | sbtest6            |
12 | sbtest7            |
13 | sbtest8            |
14 | sbtest9            |
15 +--------------------+
16 10 rows in set (0.00 sec)
17 
18 (root@localhost mysql3306.sock)[sysbench]>show create table sbtest1\G
19 *************************** 1. row ***************************
20        Table: sbtest1
21 Create Table: CREATE TABLE `sbtest1` (
22   `id` int(11) NOT NULL AUTO_INCREMENT,
23   `k` int(11) NOT NULL DEFAULT '0',
24   `c` char(120) NOT NULL DEFAULT '',
25   `pad` char(60) NOT NULL DEFAULT '',
26   PRIMARY KEY (`id`),
27   KEY `k_1` (`k`)
28 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
29 1 row in set (0.00 sec)
30 
31 (root@localhost mysql3306.sock)[sysbench]>alter table sbtest1 modify k int null,modify c char(120) null,modify pad char(60) null;
32 Query OK, 0 rows affected (4.14 sec)
33 Records: 0  Duplicates: 0  Warnings: 0
34 
35 (root@localhost mysql3306.sock)[sysbench]>insert into sbtest1 values(100001,null,null,null);
36 Query OK, 1 row affected (0.00 sec)
37 
38 (root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where id=100001;
39 +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
40 | id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
41 +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
42 |  1 | SIMPLE      | sbtest1 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
43 +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
44 1 row in set, 1 warning (0.00 sec)
45 
46 (root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where k is null;
47 +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
48 | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
49 +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
50 |  1 | SIMPLE      | sbtest1 | NULL       | ref  | k_1           | k_1  | 5       | const |    1 |   100.00 | Using where; Using index |
51 +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
52 1 row in set, 1 warning (0.00 sec)
53 
54 //In the first query,the newly added row is retrieved(检索) by primary key.
55 //In the second query,the newly added row is retrieved by secondary key "k_1"
56 // It has been proved that indexes can be used on the columns which contain null value.
   通过explain 可以看到 mysql支持含有NULL值的列上使用索引 
57 //column "k" is int datatype which occupies 4 bytes,but the value of "key_len" turn out to be 5.
   // what's happed?Because null value needs 1 byte to store the null flag in the rows.
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59

这个是我自己测试的例子.

mysql> select * from test_1;
+-----------+------+------+
| name      | code | id   |
+-----------+------+------+
| gaoyi     | wo   |    1 |
| gaoyi     | w    |    2 |
| chuzhong  | wo   |    3 |
| chuzhong  | w    |    4 |
| xiaoxue   | dd   |    5 |
| xiaoxue   | dfdf |    6 |
| sujianhui | su   |   99 |
| sujianhui | NULL |   99 |
+-----------+------+------+
8 rows in set (0.00 sec)

mysql> explain select * from test_1 where code is NULL;
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_1 | NULL       | ref  | index_code    | index_code | 161     | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test_1 where code is not NULL;
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_1 | NULL       | range | index_code    | index_code | 161     | NULL |    7 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test_1 where code='dd';
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_1 | NULL       | ref  | index_code    | index_code | 161     | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test_1 where code like "dd%";
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_1 | NULL       | range | index_code    | index_code | 161     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46

Summary 总结

null value always leads to many uncertainties when disposing sql statement.It may cause bad performance accidentally.

列中使用NULL值容易引发不受控制的事情发生,有时候还会严重托慢系统的性能.

例如:

  • null value will not be estimated in aggregate function() which may cause inaccurate results.
    对含有NULL值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值.
  • null value will influence the behavior of the operations such as “distinct”,“group by”,“order by” which causes wrong sort.
    干扰排序，分组,去重结果.
  • null value needs ifnull() function to do judgement which makes the program code more complex.
    有的时候为了消除NULL带来的技术债务,我们需要在SQL中使用IFNULL()来确保结果可控,但是这使程序变得复杂.
  • null value needs a extra 1 byte to store the null information in the rows.
    NULL值并是占用原有的字段空间存储,而是额外申请一个字节去标注,这个字段添加了NULL约束.(就像额外的标志位一样)

As these above drawbacks,it’s not recommended to define columns with default null.
We recommand to define “not null” on all columns and use zero number & vacant string to substitute relevant data type of null.

根据以上缺点,我们并不推荐在列中设置NULL作为列的默认值,你可以使用NOT NULL消除默认设置,使用0或者''空字符串来代替NULL.

 

 

 

 

 

 

 

MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解

 更新时间：2020年07月27日 17:18:16   作者：MSSQL123  

这篇文章主要介绍了MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

java

对于MySQL的一些个规范，某些公司建表规范中有一项要求是所有字段非空，意味着没有值的时候存储一个默认值。其实所有字段非空这么说应该是绝对了，应该说是尽可能非空，某些情况下不可能给出一个默认值。
那么这条要求，是基于哪些考虑因素，存储空间？相关增删查改操作的性能？亦或是其他考虑？该理论到底有没有道理或者可行性，本文就个人的理解，做一个粗浅的分析。

1，基于存储的考虑

这里对存储的分析要清楚MySQL数据行的存储格式，这里直接从这篇文章白嫖一部分结论，文章里分析的非常清楚（其实也是参考《MySQL技术内容Innodb存储引擎》）。
对于默认的Dynamic或者Compact格式的数据行结构，其行结构格式如下：
|变长字段长度列表（1~2字节）|NULL标志位（1字节）|记录头信息（5字节）|RowID（6字节）|事务ID（6字节）|回滚指针（7字节）|row content

1，对于变长字段，当相关的字段值为NULL时，相关字段不会占用存储空间。NULL值没有存储，不占空间，但是需要一个标志位（一行一个）。
2，对于变长字段，相关字段要求NOT NULL，存储成''的时候，也不占用空间，如果一个表中所有的字典都NOT NULL，行头不需要NULL的标志位
3，所有字段都是定长，不管是否要求为NOT NULL，都不需要标志位，同时不需要存储变长列长度

鉴于null值和非空（not null default ''）两种情况，如果一个字段存储的内容是空，也就是什么都没有，前者存储为null，后者存储为空字符串'',两者字段内容本身存储空间大小是一样的。
但是如果一个表中存储在可空字段的情况下，其对应的数据行的头部，都需要一个1字节的NULL标志位，这个就决定了存储同样的数据，如果允许为null，相比not null的情况下，每行多了一个字节的存储空间的。
这个因素或者就是某些公司或者个人坚持“所有表禁止null字段”这个信仰的原因之一（个人持否定态度，可以尝试将数据库中所有的字段都至为not null 然后default一个值后会不会鸡飞狗跳）。
这里不再去做“微观”的分析，直接从“宏观”的角度来看一下差异。

测试demo

直接创建结构一致，但是一个表字段not null，一个表字段为null，然后使用存储此过程，两张表同时按照null值与非null值1:10的比例写入数据，也就是说每10行数据中1行数据字段为null的方式写入600W行数据。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

CREATE TABLE a

(

 id INT AUTO_INCREMENT,

 c2 VARCHAR(50) NOT NULL DEFAULT '',

 c3 VARCHAR(50) NOT NULL DEFAULT '',

 PRIMARY KEY (id)

);

 

CREATE TABLE b

(

 id INT AUTO_INCREMENT,

 c2 VARCHAR(50),

 c3 VARCHAR(50),

 PRIMARY KEY (id)

);

 

 

CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(

 IN `loop_cnt` INT

)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

 DECLARE v2 , v3 VARCHAR(36);

  

 START TRANSACTION;

  

 while loop_cnt>0 do

 SET v2 = UUID();

 SET v3 = UUID();

 

  

 if (loop_cnt MOD 10) = 0 then

 INSERT INTO a (c2,c3) VALUES(DEFAULT,DEFAULT);

 INSERT INTO b (c2,c3) VALUES(DEFAULT,DEFAULT);

 else

 INSERT INTO a (c2,c3) VALUES (v2,v3);

 INSERT INTO b (c2,c3) VALUES (v2,v3);

 END if ;

  

 SET loop_cnt=loop_cnt-1;

 END while;

 COMMIT;

a,b两张表生产完全一致的数据。

查看占用的存储空间情况，从information_schema.TABLES中查询这两个表的存储信息

1，一个字节的差别，体现在avg_row_length,a表因为所有的字段都是not null，因此相比b表，每行节省了每行节省了一个字节的存储
2，总得空间的差别：a表662683648/1024/1024=631.98437500MB，b表666877952/1024/1024=635.98437500MB，
　 也当前情况下，600W行数据有4MB的差异，差异在1%之内，其实实际情况下，字段多，table size更大的的时候，这个差异会远远小于1%。

就存储空间来说，你跟我说1T的数据库你在乎1GB的存储空间，随便一点数据/索引碎片空间，一点预留空间，垃圾文件空间，无用索引空间……，都远远大于可为空带来的额外这一点差异。

2，增删查改的效率

读写操作对比，通过连续读写一个范围之内的数据，来对比a，b两张表在读上面的情况。
　　2.1.）首先buffer pool是远大于table size的，因此不用担心物理IO引起的差异，目前两张表的数据完全都存在与buffer pool中。
　　2.1.）读测试操作放在MySQL实例机器上，因此网络不稳定引起的差异可以忽略。

增删查改的差异与存储空间的差异类似，甚至更小，因为单行相差1个字节，放大到600W+才能看到一个5MB级别的差异，增删查改的话，各种测试下来，没有发现有明显的差异

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

#!/usr/bin/env python3

import pymysql

import time

mysql_conn_conf = {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': '******', 'db': 'db01'}

 

 

def mysql_read(table_name):

 conn = pymysql.connect(host=mysql_conn_conf['host'], port=mysql_conn_conf['port'], database=mysql_conn_conf['db'],user=mysql_conn_conf['user'],password = mysql_conn_conf['password'])

 cursor = conn.cursor()

 try:

 cursor.execute(''' select id,c2,c3 from {0} where id>3888888 and id<3889999;'''.format(table_name))

 row = cursor.fetchall()

 except pymysql.Error as e:

 print("mysql execute error:", e)

 cursor.close()

 conn.close()

 

 

def mysql_write(loop,table_name):

 conn = pymysql.connect(host=mysql_conn_conf['host'], port=mysql_conn_conf['port'], database=mysql_conn_conf['db'],user=mysql_conn_conf['user'],password = mysql_conn_conf['password'])

 cursor = conn.cursor()

 try:

 if loop%10 == 0:

 cursor.execute(''' insert into {0}} (c2,c3) values(DEFAULT,DEFAULT)'''.format(table_name))

 else:

 cursor.execute(''' insert into {1}} (c2,c3) values(uuid(),uuid())'''.format(table_name))

 except pymysql.Error as e:

 print("mysql execute error:", e)

 cursor.close()

 conn.commit()

 conn.close()

 

 

if __name__ == '__main__':

 time_start = time.time()

 loop=10

 while loop>0:

 mysql_write(loop)

 loop = loop-1

 

 time_end = time.time()

 time_c= time_end - time_start

 print('time cost', time_c, 's')

3，相关字段上的语义解析和逻辑考虑

这一点就观点差异就太多了，也是最容易引起口水或者争议的了。

1，对于字符类型，NULL就是不存在，‘'就是空，不存在和空本身就不是一回事，不太认同一定要NOT NULL，然后给出默认值。
2，对于字符类型，任何数据库中，NULL都是不等于NULL的，因为在处理相关字段上进行join或者where筛选的时候，是不需要考虑连接双方都为NULL的情况的，一旦用''替代了NULL，''是等于''的，此时就会出现与存储NULL完全不用的语义
3，对于字符类型，一旦将相关字段default成''，如何区分''与空字符串，比如备注字段，不允许为NULL，default成‘'，那么怎么区分，NULL表达的空和默认值的空字符串''
4，对于相关的查询操作，如果允许为NULL，筛选非NULL值就是where *** is not null，语义上很清晰直观，一旦用字段非空，默认成''，会使用where *** <>''这种看起来超级恶心的写法，究竟要表达什么，语义上就已经开始模糊了
5，对于时间类型，绝大多数时候是不允许有默认值的，默认多少合适，当前时间合适么，千禧年2000合适么，2008年北京奥运会开幕时间合适么？
6，对于数值类型，比如int，比如decimal，在可空的情况下，如果禁止为NULL，默认给多少合适，0合适吗？-1合适吗？-9999999……合适吗？10086合适吗？1024合适吗？说实话，默认多少都不合适，NULL自身就是最合适的。

个人观点很明确，除非有特殊的需求要求一个字段绝对不能出现NULL值的情况，正常情况下，该NULL就NULL。
如果NULL没有存在的意义，干脆数据库就不要存在这个NULL就好了，事实上，哪个数据库没有NULL类型？
当然也不排除，某些DBA为了显得自己专业，弄出来一些莫须有的东西，现在就是有一种风气，在数据库上能提出来的限制条件越多，越有优越感。

想起来一个有关于默认值有意思的事，B站看视频的时候某up主曾提到过，因为B站把注册用户默认为男，出生日期某认为某个指定的日期，导致该up主在对用户点为分析后得到一些无法理解的数据。

个人认识有限，数据实话，非常想知道“所有字段非空”会带来什么其他哪些正面的影响，以及如何衡量这个正面的因素，还有，你们真的做到了，可以禁止整个实例下所有的库表中的字段禁止可空（nullable）？

learningtogrowup
关注
  • 4
    点赞
  • 踩
  • 18
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql 设置not null,MySQL添加一个NOT NULL列
weixin_39946029的博客
01-18 250
I am adding a column to a table:ALTER TABLE t ADD c varchar(10) NOT NULL;The column gets added, and each record has the empty string.Is this expected to work this way under all conditions (strict mode...
mysql查询空值以NULL值填充_MySQL查询只能一起显示空值和NULL值？
weixin_31781061的博客
01-18 1067
要检查NULL，请使用IS NULL。对于空值，您需要检查一个空字符串。现在，我们将看到一个示例。让我们首先创建一个表-mysql>createtableDemoTable691(PlayerIdintNOTNULLAUTO_INCREMENTPRIMARYKEY,PlayerNamevarchar(100),PlayerScoreint);使用插入命令在表中插入一些记...
MySQL中的数据库约束
最新发布
2301_80636143的博客
06-10 1939
目录导读：约束类型1、not null（不能为空）2、unique(唯一)3、default(默认值约束)4、primary key(唯一)与unique相同点：不同点：auto_increment：5、foreign key(外键)语法形式：功能：6、check——了解即可在使用数据库的时候，为了避免插入数据出错，一般会对插入的数据进行约束（限制）。比如插入学生信息（包含学生名字）：如果不对插入的数据进行约束，那么这种情况，也会插入成功：在逻辑上这显然是不成立的，因为学生的名字不能是null。为此，MyS
MySQL中的NULL：sql命令的日常总结
weixin_44440311的博客
04-06 1209
MySQL中的NULL：sql命令的日常总结
null 与 not null
u011579204的专栏
06-06 2445
not null 和 null 是标记该字段是否允许插入空值（这里的空值是一个特殊字符 \0 ）。 not null 是不允许插入这个值，那么你空着这个字段的时候，这个字段里就是空的，没有任何字符。 如果你设置为null，那么他的默认值就是null，如果你填入null或者空着，那么他会插入一个特殊字符 \0 。 仅此而已，允许插入null是为了方便做判定，但是却影响索引，查询结果也可能产生输出
Mysql中创建表字段允许空值NULL和不允许空值NULL
爱吃Java的羊的博客
09-23 2926
alter table 表名 modify 字段名 字段类型 null;MODIFY功能是修改字段属性，只是alter功能下的一个功能模块.null表示对象计算中具有保留的值，用于指示指针不引用有效对象。空值表示值未知，空值一般表示数据未知、不适用或将在以后添加数据。为字段设置允许空值NULL和不允许空值NOT NULL。null表示对象的内容为空，即对象的内容是空白的。空值表示对象的内容无法确定。创建MySql数据表，
MySQL查询空字段或非空字段(is null和not null)
09-10
在MySQL数据库中，查询空字段或非空字段是常见的操作，尤其在处理包含NULL值的数据时。NULL值在SQL中代表未知或者未定义，它与空字符串或零值有着本质的区别。本文将深入探讨如何使用`IS NULL`和`IS NOT NULL`条件来...
MySQL null与not null和null与空值的区别详解
12-14
MySQL数据库在设计表结构时，`NULL` 和 `NOT NULL` 是两个重要的字段约束，它们在数据存储和查询中有着显著的区别。理解这些差异对于优化数据库性能和编写正确的SQL语句至关重要。 首先，我们需要明确“空值”和...
MySQL中可为空的字段设置为NULL还是NOT NULL
09-09
在MySQL数据库中，设计表结构时，一个重要的决策是关于字段是否允许为空（NULL）或必须有值（NOT NULL）。这个问题涉及到数据完整性、存储效率和查询性能等多个方面。以下是对这一主题的详细讨论。 首先，我们要...
为什么mysql字段要使用NOT NULL
菜鸟教程
05-06 3817
文章来源： 学习通http://www.bdgxy.com/普学网http://www.boxinghulanban.cn/智学网http://www.jaxp.net/ 最近刚入职新公司，发现数据库设计有点小问题，数据库字段很多没有NOT NULL，对于强迫症晚期患者来说，简直难以忍受，因此有了这篇文章。 基于目前大部分的开发现状来说，我们都会把字段全部设置成NOT NULL并且给默认值的形式。 通常，对于默认值一般这样设置： 整形，我们一般使用0作为默认值。 字符串，默认空
MySQL表的进阶知识
m0_62404808的博客
12-13 897
主键约束，主键约束就是不能为空，并且是惟一的，也就是含有前两个约束条件，在上述市里的student2表中，对id进行not null 和unique约束后，在查看表结构时，就出现id是primary key的结果。主键自动增长约束，例如在插入数据时，未插入主键，则就在已插入元组的主键+1，作为新插入元组的主键，主要针对整型类型的主键，默认从一开始。外键约束，就是一个表的某字段需要参照另一个表中的某一字段，例如：在学生表和班级表中，学生表中的班级就要参照班级表中的班级号。
MySQL字段设置not null
WXY_Y的博客
06-16 816
一、null是占用空间的，需要一个字节作为判断是否为null。 二、查询时一定程度减少空指针 三、类似使用count(null)，这个列是不计数的 四、
MySQL默认值NULL、空值、Empty String的区别，如何选择？
php菜鸟技术天地
07-29 4392
工作一年左右了，发现其实工作中除了需要实现业务代码外，更多的是与数据库打交道。最近因为项目需要，我也逐渐参与进入数据库的设计，从而发现了很多之前没有过多注意的问题。 回想这一年来，写博客的次数屈指可数，现在想重新拾起，所以打算从数据库方面入手，先总结一个比较简单但非常常用的知识点——mysql中字段默认值的问题。 我们可以看到，默认值的设置主要分为三种： 空值，空白，即什么都不填，等同于NULL（设计表结构时保存空值会自动变成NULL），当然也可以手动输入默认值。 NULL。 Empty S.
MySQL的表为什么要设置NOT NULL？底层原理是什么？
长风破浪会有时的博客
06-21 830
通过设置NOT NULL约束，MySQL可以在插入和更新数据时对列的非空性进行强制检查，避免数据的不完整和不符合业务规则的情况。对于可为空的列，MySQL需要为每个行分配额外的空间来存储是否为空的标识，而对于被设置为NOT NULL的列，不需要额外的空间来表示空值的存在。如果插入或更新操作中涉及到被设置为NOT NULL的列，并且该列的值为空或为NULL，MySQL会拒绝该操作并抛出错误。MySQL中设置表的列为NOT NULL是为了确保该列的值不为空，即要求该列在插入或更新数据时必须有一个非空值。
MySQL判断非空和非空函数
王德昌的博客
08-09 4152
本文介绍了MySQL中判断字段是否为空的常用方法，包括使用IS NULL和IS NOT NULL以及COALESCE函数。同时，还介绍了对非空字段进行处理的非空函数，包括IFNULL函数和NULLIF函数。通过示例代码和测试用例，我们加强了对这些方法和函数的理解。
【MySQL】基础篇【约束概述 NOT NULL】
weixin_59131972的博客
12-22 783
为什么需要约束 数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中 存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
Mysql中is null 和 is not null的使用
热门推荐
u013131716的博客
03-17 2万+
MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。 为了处理这种情况，MySQL提供了三大运算符: IS NULL: 当列的值是 NULL,此运算符返回 true。 IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。 <=>: 比较操作符（不同于 = 运算符）...
mysql null与not null
半路出家，立地为猿
04-16 1630
NULL 和 空字符 NULL 和 NOT NULL的区别
mysql创建数据库和表notnull
07-27
您可以使用以下语句在MySQL中创建数据库和表，并将某些列设置为非空（NOT NULL）： 1. 创建数据库： ```sql CREATE DATABASE your_database_name; ``` 2. 选择数据库： ```sql USE your_database_name; ``` 3. 创建表： ```sql CREATE TABLE your_table_name ( column1 datatype NOT NULL, column2 datatype NOT NULL, ... ); ``` 在上面的代码中，您需要将 `your_database_name` 替换为您想要创建的数据库名称，`your_table_name` 替换为您想要创建的表名称。`column1`, `column2`, ... 是您想要创建的列名，`datatype` 是列的数据类型。 通过在列定义中添加 `NOT NULL` 约束，您可以确保插入记录时这些列的值不为空。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值