mysql 约束条件 not null与default

 

 

not null与default

 

是否可空，null表示空，非字符串
not null - 不可空
null - 可空

 

 

use db4；

 

 

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

mysql> create table tb2(id int not null);
Query OK, 0 rows affected (0.01 sec)



mysql> desc tb2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into tb2 values();
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from tb2;
+----+
| id |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

 

 

==================not null====================

mysql> create table t1(id int); #id字段默认可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空

 

 

mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

 

 

==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

mysql> create table tb3(id int default 1);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb3 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)