mysql-添加删除默认值(6)

创建表格的时候，给列添加默认值使用default，现在添加默认值仍然需要使用default，具体命令格式如下

alter table 表名 alter 字段名 set default 默认值；
alter table 表名 alter 字段名 drop default;

我们使用提前创建号的一个表格test_alter，现在在name字段上测试添加以及删除默认值

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
--给字段name添加默认值'小明'
mysql> alter table test_alter alter name set default '小明';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   | UNI | 小明    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
---给字段name删除默认值'小明'
mysql> alter table test_alter alter name drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
----使用add关键字添加两个字段
mysql> alter table test_alter add email varchar(30) not null unique,
    -> add address varchar(30) not null;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   | UNI | NULL    |                |
| email   | varchar(30)      | NO   | UNI | NULL    |                |
| address | varchar(30)      | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
-----同时给三个字段添加默认值
mysql> alter table test_alter alter name set default '小明',
    -> alter email set default '12343@qq.com',
    -> alter address set default '北京';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+---------+------------------+------+-----+--------------+----------------+
| Field   | Type             | Null | Key | Default      | Extra          |
+---------+------------------+------+-----+--------------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL         | auto_increment |
| name    | varchar(20)      | NO   | UNI | 小明         |                |
| email   | varchar(30)      | NO   | UNI | 12343@qq.com |                |
| address | varchar(30)      | NO   |     | 北京         |                |
+---------+------------------+------+-----+--------------+----------------+
4 rows in set (0.00 sec)
-----下面同时删除三个字段的默认值
mysql> alter table test_alter alter name drop default,
    -> alter email drop default,
    -> alter address drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   | UNI | NULL    |                |
| email   | varchar(30)      | NO   | UNI | NULL    |                |
| address | varchar(30)      | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
----可以查看到执行成功，name、email、address这三个字段确实没有默认值了。