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

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

目录

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

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

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

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

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

createtable 表名 (<字段名><数据类型>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>createtable t32(->     id intprimarykey,->     name char(20)notnull,->     gender char(1)notnulldefault'男',->     birth datetimenotnull,->     salary intnotnull->);
Query OK,0rows affected (0.03 sec)

插入数据：

-- 正常插入数据
mysql>insertinto t32 values(1001,'张强','男','1989-1-22',5500);
Query OK,1row affected (0.00 sec)

mysql>insertinto t32 values(1002,'刘云','女','1992-10-3',5200);
Query OK,1row affected (0.00 sec)-- 插入数据时 gender 列不指定数据值，结果为【男】
mysql>insertinto t32(id,name,birth,salary)values(1003,'刘刚','1990-8-8',5000);
Query OK,1row affected (0.02 sec)-- 插入数据时使用 default 代替数据值，结果为【男】
mysql>insertinto t32 values(1004,'刘鹏',default,'1997-10-9',4500);
Query OK,1row affected (0.00 sec)

mysql>select*from t32;+------+--------+--------+---------------------+--------+| id   | name   | gender | birth               | salary |+------+--------+--------+---------------------+--------+|1001| 张强   | 男     |1989-01-2200:00:00|5500||1002| 刘云   | 女     |1992-10-0300:00:00|5200||1003| 刘刚   | 男     |1990-08-0800:00:00|5000||1004| 刘鹏   | 男     |1997-10-0900:00:00|4500|+------+--------+--------+---------------------+--------+4rowsinset(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>createtable t33(->     id intprimarykey,->     name char(20)notnull,->     gender char(1)notnull,->     birth datetimenotnull,->     salary intnotnull,->     createtime datetimedefaultnow()->);
Query OK,0rows affected (0.03 sec)

插入数据：

mysql>insertinto t33(id,name,gender,birth,salary)values(1001,'张强','男','1989-1-22',5500);
Query OK,1row affected (0.01 sec)

mysql>insertinto t33(id,name,gender,birth,salary)values(1002,'张静静','女','1992-6-9',5000);
Query OK,1row affected (0.01 sec)

mysql>insertinto t33(id,name,gender,birth,salary)values(1003,'刘涛','女','1990-10-15',5200);
Query OK,1row affected (0.01 sec)

mysql>insertinto t33(id,name,gender,birth,salary)values(1004,'郑强','男','1994-8-13',4800);
Query OK,1row affected (0.00 sec)

mysql>select*from t33;+------+-----------+--------+---------------------+--------+---------------------+| id   | name      | gender | birth               | salary | createtime          |+------+-----------+--------+---------------------+--------+---------------------+|1001| 张强      | 男     |1989-01-2200:00:00|5500|2022-07-1817:24:27||1002| 张静静    | 女     |1992-06-0900:00:00|5000|2022-07-1817:24:27||1003| 刘涛      | 女     |1990-10-1500:00:00|5200|2022-07-1817:24:27||1004| 郑强      | 男     |1994-08-1300:00:00|4800|2022-07-1817:24:28|+------+-----------+--------+---------------------+--------+---------------------+4rowsinset(0.00 sec)

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

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

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

例如：

（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>createtable t41(->     id intprimarykey,->     name char(20)notnull,->     gender char(1)notnull,->     birth datetimenotnull,->     salary intnotnull,->     createtime datetimenotnull->);
Query OK,0rows affected (0.04 sec)

mysql>insertinto t41 values(1001,'张强','男','1989-1-22',5500,now());
Query OK,1row affected (0.06 sec)

mysql>insertinto t41 values(1002,'张静静','女','1992-6-9',5000,now());
Query OK,1row affected (0.04 sec)

mysql>insertinto t41 values(1003,'刘涛','女','1990-10-15',5200,now());
Query OK,1row affected (0.00 sec)

mysql>insertinto t41 values(1004,'郑强','男','1994-8-13',4800,now());
Query OK,1row 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||+------------+----------+------+-----+---------+-------+6rowsinset(0.00 sec)

mysql>select*from t41;+------+-----------+--------+---------------------+--------+---------------------+| id   | name      | gender | birth               | salary | createtime          |+------+-----------+--------+---------------------+--------+---------------------+|1001| 张强      | 男     |1989-01-2200:00:00|5500|2022-07-1817:48:57||1002| 张静静    | 女     |1992-06-0900:00:00|5000|2022-07-1817:50:04||1003| 刘涛      | 女     |1990-10-1500:00:00|5200|2022-07-1817:50:13||1004| 郑强      | 男     |1994-08-1300:00:00|4800|2022-07-1817:50:19|+------+-----------+--------+---------------------+--------+---------------------+4rowsinset(0.00 sec)

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

mysql>altertable t41 add nation varchar(20)notnulldefault'汉族'after birth;
Query OK,0rows affected (0.10 sec)
Records: 0  Duplicates: 0Warnings: 0

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||| nation     |varchar(20)|NO|| 汉族    ||| salary     |int(11)|NO||NULL||| createtime |datetime|NO||NULL||+------------+-------------+------+-----+---------+-------+7rowsinset(0.01 sec)

mysql>select*from t41;+------+-----------+--------+---------------------+--------+--------+---------------------+| id   | name      | gender | birth               | nation | salary | createtime          |+------+-----------+--------+---------------------+--------+--------+---------------------+|1001| 张强      | 男     |1989-01-2200:00:00| 汉族   |5500|2022-07-1817:48:57||1002| 张静静    | 女     |1992-06-0900:00:00| 汉族   |5000|2022-07-1817:50:04||1003| 刘涛      | 女     |1990-10-1500:00:00| 汉族   |5200|2022-07-1817:50:13||1004| 郑强      | 男     |1994-08-1300:00:00| 汉族   |4800|2022-07-1817:50:19|+------+-----------+--------+---------------------+--------+--------+---------------------+4rowsinset(0.00 sec)

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

mysql>altertable t41 modify createtime datetimenotnulldefaultnow();
Query OK,0rows affected (0.02 sec)
Records: 0  Duplicates: 0Warnings: 0

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||| nation     |varchar(20)|NO|| 汉族              ||| salary     |int(11)|NO||NULL||| createtime |datetime|NO||CURRENT_TIMESTAMP||+------------+-------------+------+-----+-------------------+-------+7rowsinset(0.02 sec)

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

mysql>altertable t41 altercolumn gender setdefault'男';
Query OK,0rows affected (0.02 sec)
Records: 0  Duplicates: 0Warnings: 0

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||+------------+-------------+------+-----+-------------------+-------+7rowsinset(0.00 sec)

三、删除某个列的默认值

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

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

-- 重新定义列的类型，不带 default 关键词ALTERTABLE<表名>MODIFY<字段名><数据类型>;-- 重新定义列的类型，加：default nullALTERTABLE<表名>MODIFY<字段名><数据类型>DEFAULTNULL;-- 使用 alter column drop default altertable<表名>altercolumn<字段名>dropdefault;

（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||+------------+-------------+------+-----+-------------------+-------+7rowsinset(0.00 sec)

mysql>altertable t41 modify gender char(1)notnull;
Query OK,0rows affected (0.02 sec)
Records: 0  Duplicates: 0Warnings: 0

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||| nation     |varchar(20)|NO|| 汉族              ||| salary     |int(11)|NO||NULL||| createtime |datetime|NO||CURRENT_TIMESTAMP||+------------+-------------+------+-----+-------------------+-------+7rowsinset(0.01 sec)

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

mysql>altertable t41 modify nation varchar(20)defaultnull;
Query OK,0rows affected (0.10 sec)
Records: 0  Duplicates: 0Warnings: 0

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||| nation     |varchar(20)| YES  ||NULL||| salary     |int(11)|NO||NULL||| createtime |datetime|NO||CURRENT_TIMESTAMP||+------------+-------------+------+-----+-------------------+-------+7rowsinset(0.00 sec)

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

mysql>altertable t41 altercolumn createtime dropdefault;
Query OK,0rows affected (0.00 sec)
Records: 0  Duplicates: 0Warnings: 0

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||| nation     |varchar(20)| YES  ||NULL||| salary     |int(11)|NO||NULL||| createtime |datetime|NO||NULL||+------------+-------------+------+-----+---------+-------+7rowsinset(0.00 sec)