mysql-修改字段属性(8）-蒲公英云

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: 1
mysql> 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: 1
mysql> 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 '1122@126.com';
Query OK, 0 rows affected (0.02 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(40) | NO | UNI | NULL | | | addr | varchar(30) | NO | | 北京 | | | email | varchar(40) | NO | UNI | 1122@126.com | | +-------+------------------+------+-----+--------------+----------------+ 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 '12345@11.com';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> 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 | 12345@11.com | | +-------+------------------+------+-----+--------------+----------------+ 4 rows in set (0.00 sec)