not null default 说明不能是NULL, 并设置默认值
default 设置默认值 , 但值也可能是NULL
mysql> create table test (id int, name varchar(10) default 'a', addr varchar(10)
not null default 'b');
Query OK, 0 rows affected (0.04 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | a | |
| addr | varchar(10) | NO | | b | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test id values(1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'id
values(1)' at line 1
mysql> insert into test id values(1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'id
values(1)' at line 1
mysql> insert into test(id) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(id) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(id) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+------+
| id | name | addr |
+------+------+------+
| 1 | a | b |
| 1 | a | b |
| 1 | a | b |
+------+------+------+
3 rows in set (0.00 sec)
mysql> insert into test(id,name) values (1,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+------+
| id | name | addr |
+------+------+------+
| 1 | a | b |
| 1 | a | b |
| 1 | a | b |
| 1 | NULL | b |
+------+------+------+
4 rows in set (0.00 sec)
mysql> insert into test(id,addr) values (1,null);
ERROR 1048 (23000): Column 'addr' cannot be null
mysql>