Database system principle and Application Tutorial （033）—— MySQL Data integrity （ 6、 ... and ）： Non empty （NOT NULL） constraint

One 、NULL Characteristics

About NULL,MySQL The official explanation is as follows ：
NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.（NULL Columns need extra space in rows to record whether their values are empty . about myisam surface , Every NULL Column requires an extra digit , Round to the nearest byte .）

This explanation ,MySQL Medium NULL In fact, it takes up space . for example ：

/* CREATE TABLE t21( id int primary key, name VARCHAR(10) NOT NULL, addr VARCHAR(100) NULL ) ENGINE = MYISAM; */
mysql> CREATE TABLE t21(
    ->     id int primary key,
    ->     name VARCHAR(10) NOT NULL,
    ->     addr VARCHAR(100) NULL
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.04 sec)

--  insert data , When  name  The value of the column is  null  The insertion fails 
mysql> insert into t21 values(1,null,' Xinxiang City, Henan Province ');
ERROR 1048 (23000): Column 'name' cannot be null

--  Empty strings can be inserted , therefore  null  Different from empty string 
mysql> insert into t21 values(1,'',' Xinxiang City, Henan Province ');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t21;
+----+------+--------------------+
| id | name | addr               |
+----+------+--------------------+
|  1 |      |  Xinxiang City, Henan Province        |
+----+------+--------------------+
1 row in set (0.02 sec)

If a column is allowed to take NULL There will be the following problems with the value ：

（1） Do not store when indexing tables NULL value , If the index field can be NULL, The efficiency of the index will decrease .

（2） When data and NULL When performing mathematical operations and comparison operations on values , The result is still NULL. therefore , To judge whether a data is NULL value , Must use IS NULL or IS NOT NULL To judge .

（3） It is recommended that when creating tables , All columns are set to not allow null values （NOT NULL）.

Two 、 Define non null constraints

Create table time , All fields can be null by default , If you need to define a field as not allowed to take null value , You can use non empty constraints （NOT NULL）.

The syntax is as follows ：

create table  Table name (
     Name   type  not null,
    ....
);

for example ： Create table t31, Null values are not allowed for all columns

/* create table t31( e_id int primary key, e_name char(20) not null, gender char(1) not null, jobtime datetime not null, salary int not null, phone char(20) not null, addr varchar(100) not null ); */
mysql> create table t31(
    ->     e_id int primary key,
    ->     e_name char(20) not null,
    ->     gender char(1) not null,
    ->     jobtime datetime not null,
    ->     salary int not null,
    ->     phone char(20) not null,
    ->     addr varchar(100) not null
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> desc t31;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id    | int(11)      | NO   | PRI | NULL    |       |
| e_name  | char(20)     | NO   |     | NULL    |       |
| gender  | char(1)      | NO   |     | NULL    |       |
| jobtime | datetime     | NO   |     | NULL    |       |
| salary  | int(11)      | NO   |     | NULL    |       |
| phone   | char(20)     | NO   |     | NULL    |       |
| addr    | varchar(100) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.03 sec)

insert data , All columns must specify data .

mysql> insert into t31 values(1001,' Wang Dawei ',' male ','1998-7-1',10200,'13737325689',' Xinxiang City, Henan Province ');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t31;
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| e_id | e_name    | gender | jobtime             | salary | phone       | addr               |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| 1001 |  Wang Dawei     |  male      | 1998-07-01 00:00:00 |  10200 | 13737325689 |  Xinxiang City, Henan Province        |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
1 row in set (0.02 sec)

--  Any column cannot be  null, Otherwise, the insertion fails 
mysql> insert into t31 values(1002,' Liu tao ',' male ','1996-6-11',12200,'13703732544',null);
ERROR 1048 (23000): Column 'addr' cannot be null

3、 ... and 、 Remove non empty constraints

To make a column allow null values , Then modify the properties of the column , Get rid of not null Options can be .

for example ： send t31 In the table addr Column is allowed to be empty .

mysql> desc t31;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id    | int(11)      | NO   | PRI | NULL    |       |
| e_name  | char(20)     | NO   |     | NULL    |       |
| gender  | char(1)      | NO   |     | NULL    |       |
| jobtime | datetime     | NO   |     | NULL    |       |
| salary  | int(11)      | NO   |     | NULL    |       |
| phone   | char(20)     | NO   |     | NULL    |       |
| addr    | varchar(100) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table t31 modify addr varchar(100);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t31;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id    | int(11)      | NO   | PRI | NULL    |       |
| e_name  | char(20)     | NO   |     | NULL    |       |
| gender  | char(1)      | NO   |     | NULL    |       |
| jobtime | datetime     | NO   |     | NULL    |       |
| salary  | int(11)      | NO   |     | NULL    |       |
| phone   | char(20)     | NO   |     | NULL    |       |
| addr    | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

insert data , send addr by null：

mysql> insert into t31 values(1002,' Liu tao ',' male ','1996-6-11',12200,'13703732544',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t31;
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| e_id | e_name    | gender | jobtime             | salary | phone       | addr               |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| 1001 |  Wang Dawei     |  male      | 1998-07-01 00:00:00 |  10200 | 13737325689 |  Xinxiang City, Henan Province        |
| 1002 |  Liu tao       |  male      | 1996-06-11 00:00:00 |  12200 | 13703732544 | NULL               |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
2 rows in set (0.00 sec)