数据库系统原理与应用教程（034）—— MySQL 的数据完整性（七）：默认值（DEFAULT）

默认值（DEFAULT）"/>

数据库系统原理与应用教程（034）—— MySQL 的数据完整性（七）：默认值（DEFAULT）

数据库系统原理与应用教程（034）—— MySQL 的数据完整性（七）：默认值（DEFAULT）

目录

• 数据库系统原理与应用教程（034）—— MySQL 的数据完整性（七）：默认值（DEFAULT）
• • 一、创建表同时设置默认值
  • 二、为表中的列指定默认值
  • 三、删除某个列的默认值

严格意义上来讲，默认值不属于约束，因为当某个列设置默认值以后，并不会限制该列的取值。

如果为某个列指定默认值，在表中插入一条新记录时，如果没有为该列指定数据，系统就会自动为该列插入默认值。例如：学生表中的政治面貌列，由于绝大部分学生的【政治面貌】为【共青团员】，则可以把【政治面貌】列的默认值设置为【共青团员】，当插入学生数据时，如果没有为【政治面貌】列指定数据，则自动把【共青团员】插入该列。

默认值通常用在设置了非空约束的列。

一、创建表同时设置默认值

创建表时可以使用 DEFAULT 为某个列设置默认值，语法如下：

create table 表名 (<字段名> <数据类型> DEFAULT <默认值>,....
);

例如：

（1）创建表 t32，设置列【gender】的默认值为【男】

/*
create table t32(id int primary key,name char(20) not null,gender char(1) not null default '男',birth datetime not null,salary int not null
);
*/
mysql> create table t32(->     id int primary key,->     name char(20) not null,->     gender char(1) not null default '男',->     birth datetime not null,->     salary int not null-> );
Query OK, 0 rows affected (0.03 sec)

插入数据：

-- 正常插入数据
mysql> insert into t32 values(1001,'张强','男','1989-1-22',5500);
Query OK, 1 row affected (0.00 sec)mysql> insert into t32 values(1002,'刘云','女','1992-10-3',5200);
Query OK, 1 row affected (0.00 sec)-- 插入数据时 gender 列不指定数据值，结果为【男】
mysql> insert into t32(id,name,birth,salary) values(1003,'刘刚','1990-8-8',5000);
Query OK, 1 row affected (0.02 sec)-- 插入数据时使用 default 代替数据值，结果为【男】
mysql> insert into t32 values(1004,'刘鹏',default,'1997-10-9',4500);
Query OK, 1 row affected (0.00 sec)mysql> select * from t32;
+------+--------+--------+---------------------+--------+
| id   | name   | gender | birth               | salary |
+------+--------+--------+---------------------+--------+
| 1001 | 张强   | 男     | 1989-01-22 00:00:00 |   5500 |
| 1002 | 刘云   | 女     | 1992-10-03 00:00:00 |   5200 |
| 1003 | 刘刚   | 男     | 1990-08-08 00:00:00 |   5000 |
| 1004 | 刘鹏   | 男     | 1997-10-09 00:00:00 |   4500 |
+------+--------+--------+---------------------+--------+
4 rows in set (0.00 sec)

（2）创建表 t33，设置列 createtime 的默认值为当前时间

/*
create table t33(id int primary key,name char(20) not null,gender char(1) not null,birth datetime not null,salary int not null,createtime datetime default now()
);
*/mysql> create table t33(->     id int primary key,->     name char(20) not null,->     gender char(1) not null,->     birth datetime not null,->     salary int not null,->     createtime datetime default now()-> );
Query OK, 0 rows affected (0.03 sec)

插入数据：

mysql> insert into t33(id,name,gender,birth,salary) values(1001,'张强','男','1989-1-22',5500);
Query OK, 1 row affected (0.01 sec)mysql> insert into t33(id,name,gender,birth,salary) values(1002,'张静静','女','1992-6-9',5000);
Query OK, 1 row affected (0.01 sec)mysql> insert into t33(id,name,gender,birth,salary) values(1003,'刘涛','女','1990-10-15',5200);
Query OK, 1 row affected (0.01 sec)mysql> insert into t33(id,name,gender,birth,salary) values(1004,'郑强','男','1994-8-13',4800);
Query OK, 1 row affected (0.00 sec)mysql> select * from t33;
+------+-----------+--------+---------------------+--------+---------------------+
| id   | name      | gender | birth               | salary | createtime          |
+------+-----------+--------+---------------------+--------+---------------------+
| 1001 | 张强      | 男     | 1989-01-22 00:00:00 |   5500 | 2022-07-18 17:24:27 |
| 1002 | 张静静    | 女     | 1992-06-09 00:00:00 |   5000 | 2022-07-18 17:24:27 |
| 1003 | 刘涛      | 女     | 1990-10-15 00:00:00 |   5200 | 2022-07-18 17:24:27 |
| 1004 | 郑强      | 男     | 1994-08-13 00:00:00 |   4800 | 2022-07-18 17:24:28 |
+------+-----------+--------+---------------------+--------+---------------------+
4 rows in set (0.00 sec)

二、为表中的列指定默认值

为表中已存在的列指定默认值，语法格式如下：

-- 添加列同时指定默认值
ALTER TABLE <表名>
ADD <字段名> <数据类型> DEFAULT <默认值>;-- 为已存在的列指定默认值
ALTER TABLE <表名>
MODIFY <字段名> <数据类型> DEFAULT <默认值>;--或者
ALTER TABLE <表名> ALTER COLUMN <字段名> SET DEFAULT <默认值>;

例如：

（1）定义表 t41 并插入数据

/*
create table t41(id int primary key,name char(20) not null,gender char(1) not null,birth datetime not null,salary int not null,createtime datetime not null
);
insert into t41 values(1001,'张强','男','1989-1-22',5500,now());
insert into t41 values(1002,'张静静','女','1992-6-9',5000,now());
insert into t41 values(1003,'刘涛','女','1990-10-15',5200,now());
insert into t41 values(1004,'郑强','男','1994-8-13',4800,now());
*/mysql> create table t41(->     id int primary key,->     name char(20) not null,->     gender char(1) not null,->     birth datetime not null,->     salary int not null,->     createtime datetime not null-> );
Query OK, 0 rows affected (0.04 sec)mysql> insert into t41 values(1001,'张强','男','1989-1-22',5500,now());
Query OK, 1 row affected (0.06 sec)mysql> insert into t41 values(1002,'张静静','女','1992-6-9',5000,now());
Query OK, 1 row affected (0.04 sec)mysql> insert into t41 values(1003,'刘涛','女','1990-10-15',5200,now());
Query OK, 1 row affected (0.00 sec)mysql> insert into t41 values(1004,'郑强','男','1994-8-13',4800,now());
Query OK, 1 row affected (0.00 sec)mysql> desc t41;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id         | int(11)  | NO   | PRI | NULL    |       |
| name       | char(20) | NO   |     | NULL    |       |
| gender     | char(1)  | NO   |     | NULL    |       |
| birth      | datetime | NO   |     | NULL    |       |
| salary     | int(11)  | NO   |     | NULL    |       |
| createtime | datetime | NO   |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> select * from t41;
+------+-----------+--------+---------------------+--------+---------------------+
| id   | name      | gender | birth               | salary | createtime          |
+------+-----------+--------+---------------------+--------+---------------------+
| 1001 | 张强      | 男     | 1989-01-22 00:00:00 |   5500 | 2022-07-18 17:48:57 |
| 1002 | 张静静    | 女     | 1992-06-09 00:00:00 |   5000 | 2022-07-18 17:50:04 |
| 1003 | 刘涛      | 女     | 1990-10-15 00:00:00 |   5200 | 2022-07-18 17:50:13 |
| 1004 | 郑强      | 男     | 1994-08-13 00:00:00 |   4800 | 2022-07-18 17:50:19 |
+------+-----------+--------+---------------------+--------+---------------------+
4 rows in set (0.00 sec)

（2）在表 t41 中添加列 nation 并指定默认值为【汉族】

mysql> alter table t41 add nation varchar(20) not null default '汉族' after birth;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t41;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| name       | char(20)    | NO   |     | NULL    |       |
| gender     | char(1)     | NO   |     | NULL    |       |
| birth      | datetime    | NO   |     | NULL    |       |
| nation     | varchar(20) | NO   |     | 汉族    |       |
| salary     | int(11)     | NO   |     | NULL    |       |
| createtime | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)mysql> select * from t41;
+------+-----------+--------+---------------------+--------+--------+---------------------+
| id   | name      | gender | birth               | nation | salary | createtime          |
+------+-----------+--------+---------------------+--------+--------+---------------------+
| 1001 | 张强      | 男     | 1989-01-22 00:00:00 | 汉族   |   5500 | 2022-07-18 17:48:57 |
| 1002 | 张静静    | 女     | 1992-06-09 00:00:00 | 汉族   |   5000 | 2022-07-18 17:50:04 |
| 1003 | 刘涛      | 女     | 1990-10-15 00:00:00 | 汉族   |   5200 | 2022-07-18 17:50:13 |
| 1004 | 郑强      | 男     | 1994-08-13 00:00:00 | 汉族   |   4800 | 2022-07-18 17:50:19 |
+------+-----------+--------+---------------------+--------+--------+---------------------+
4 rows in set (0.00 sec)

（3）为表 t41 中的列 createtime 设置默认值为 now()

mysql> alter table t41 modify createtime datetime not null default now();
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field      | Type        | Null | Key | Default           | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id         | int(11)     | NO   | PRI | NULL              |       |
| name       | char(20)    | NO   |     | NULL              |       |
| gender     | char(1)     | NO   |     | NULL              |       |
| birth      | datetime    | NO   |     | NULL              |       |
| nation     | varchar(20) | NO   |     | 汉族              |       |
| salary     | int(11)     | NO   |     | NULL              |       |
| createtime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.02 sec)

（4）为表 t41 中的列 gender 设置默认值为【男】

mysql> alter table t41 alter column gender set default '男';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field      | Type        | Null | Key | Default           | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id         | int(11)     | NO   | PRI | NULL              |       |
| name       | char(20)    | NO   |     | NULL              |       |
| gender     | char(1)     | NO   |     | 男                |       |
| birth      | datetime    | NO   |     | NULL              |       |
| nation     | varchar(20) | NO   |     | 汉族              |       |
| salary     | int(11)     | NO   |     | NULL              |       |
| createtime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)

三、删除某个列的默认值

当一个表中的列不需要设置默认值时，就需要从表中将其删除。

删除默认值约束的语法格式如下：

-- 重新定义列的类型，不带 default 关键词
ALTER TABLE <表名> MODIFY <字段名> <数据类型>;-- 重新定义列的类型，加：default null
ALTER TABLE <表名> MODIFY <字段名> <数据类型> DEFAULT NULL;-- 使用 alter column drop default 
alter table <表名> alter column <字段名> drop default; 

（1）删除表 t41 中 gender 列的默认值

mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field      | Type        | Null | Key | Default           | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id         | int(11)     | NO   | PRI | NULL              |       |
| name       | char(20)    | NO   |     | NULL              |       |
| gender     | char(1)     | NO   |     | 男                |       |
| birth      | datetime    | NO   |     | NULL              |       |
| nation     | varchar(20) | NO   |     | 汉族              |       |
| salary     | int(11)     | NO   |     | NULL              |       |
| createtime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)mysql> alter table t41 modify gender char(1) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field      | Type        | Null | Key | Default           | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id         | int(11)     | NO   | PRI | NULL              |       |
| name       | char(20)    | NO   |     | NULL              |       |
| gender     | char(1)     | NO   |     | NULL              |       |
| birth      | datetime    | NO   |     | NULL              |       |
| nation     | varchar(20) | NO   |     | 汉族              |       |
| salary     | int(11)     | NO   |     | NULL              |       |
| createtime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.01 sec)

（2）删除表 t41 中 nation 列的默认值

mysql> alter table t41 modify nation varchar(20) default null;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field      | Type        | Null | Key | Default           | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id         | int(11)     | NO   | PRI | NULL              |       |
| name       | char(20)    | NO   |     | NULL              |       |
| gender     | char(1)     | NO   |     | NULL              |       |
| birth      | datetime    | NO   |     | NULL              |       |
| nation     | varchar(20) | YES  |     | NULL              |       |
| salary     | int(11)     | NO   |     | NULL              |       |
| createtime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)

（3）删除表 t41 中 createtime 列的默认值

mysql> alter table t41 alter column createtime drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t41;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| name       | char(20)    | NO   |     | NULL    |       |
| gender     | char(1)     | NO   |     | NULL    |       |
| birth      | datetime    | NO   |     | NULL    |       |
| nation     | varchar(20) | YES  |     | NULL    |       |
| salary     | int(11)     | NO   |     | NULL    |       |
| createtime | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)