mysql-修改字段属性(8）

modify和change关键字修改属性，使用modify和change关键字都可以修改字段属性，可以字段的数据格式类型，以及约束性条件。
但是change和modify也有不同之处的，change可以修改字段名，但是modify不能。下面分别测试一下change和Modify
--修改字段类型，字段属性 alter table table_name modify 字段名称 字段类型 [字段属性] [first | after]
--修改字段名称，字段类型，字段属性 alter table table_name change 原字段名称 新字段名称 字段属性[first | after]
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的同时修改varchar最大长度以及删除了默认值 mysql> alter table test_
alter change name usrname varchar(30) unique not null; Query OK, 0 rows
affected, 1 warning (0.28 sec) Records: 0 Duplicates: 0 Warnings: 1mysql> desc
test_alter;
+---------+------------------+------+-----+---------+----------------+ | Field
| Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+ | id |
int(10) unsigned | NO | PRI | NULL | auto_increment | | usrname | varchar(30) |
NO | UNI | NULL | |
+---------+------------------+------+-----+---------+----------------+
-----可以看到name字段名称改成了usrname，varchar长度也已经修改，default默认值也删除了。mysql> create table
test_alter( -> id int unsigned auto_increment key, -> name varchar(30) not
null unique)engine=innodb charset=utf8; Query OK, 0 rows affected (0.01 sec)
mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+ | Field |
Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+ | id |
int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO
| UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+ 2 rows in
set (0.02 sec) ---使用modify修改varchar数据长度 mysql> alter table test_alter ->
modify name varchar(40) not null unique key; Query OK, 0 rows affected, 1
warning (0.03 sec) Records: 0 Duplicates: 0 Warnings: 1mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+ | Field |
Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+ | id |
int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(40) | NO
| UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+ 2 rows in
set (0.00 sec) ----添加两列addr(地址),email(邮箱) mysql> alter table test_alter add
addr varchar(30) not null default'北京', add email varchar(40) not null unique
default'[email protected]'; Query OK, 0 rows affected (0.02 sec) Records: 0
Duplicates: 0 Warnings: 0mysql> desc test_alter;
+-------+------------------+------+-----+--------------+----------------+ |
Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+--------------+----------------+ | id
| int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(40) |
NO | UNI | NULL | | | addr | varchar(30) | NO | | 北京 | | | email | varchar(40)
| NO | UNI | [email protected] | |
+-------+------------------+------+-----+--------------+----------------+ 4
rows in set (0.00 sec) --使用Modify关键字同时修改addr字段和email字段的属性以及约束条件，中间采用逗号分隔符.
mysql> alter table test_alter modify addr varchar(40) default '深圳', modify
email varchar(50) not null unique default'[email protected]'; Query OK, 0 rows
affected, 1 warning (0.02 sec) Records: 0 Duplicates: 0 Warnings: 1mysql> desc
test_alter;
+-------+------------------+------+-----+--------------+----------------+ |
Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+--------------+----------------+ | id
| int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(40) |
NO | UNI | NULL | | | addr | varchar(40) | YES | | 深圳 | | | email | varchar(50)
| NO | UNI | [email protected] | |
+-------+------------------+------+-----+--------------+----------------+ 4
rows in set (0.00 sec)

• « 上一篇：函数阶段性练习
• » 下一篇：我司使用了六年的分布式锁