学习ALTER TABLE删除、添加和修改字段和类型
CREATE TABLE alter_tab01(
id int,
col01 char(20))
engin=InnoDB default charset=utf8;
删除字段
ALTER TABLE <tab_name> DROP <col_name>;
mysql> alter table alter_tab01 drop col01;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加字段
ALTER TABLE <tab_name> ADD <col_name> TYPE;
ALTER TABLE <tab_name> ADD <col_name> TYPE [ FIRST| AFTER <col_name>];
ALTER TABLE <tab_name> ADD <col_name> TYPE NOT NULL;
mysql> alter table alter_tab01 add col01 char(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table alter_tab01 add col02 char(20) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table alter_tab01 add col03 char(20) after id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table alter_tab01 add col04 char(20) not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) mysql> show columns from alter_tab01;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col02 | char(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| col03 | char(20) | YES | | NULL | |
| col01 | char(20) | YES | | NULL | |
| col04 | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段类型及名称
ALTER TABLE <tab_name> MODIFY <col_name> TYPE;
ALTER TABLE <tab_name> CHANGE <old_name> <new_name> TYPE;
mysql> alter table alter_tab01 modify col02 varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table alter_tab01 change col02 new_col02 char(2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_tab01;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| new_col02 | char(2) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| col03 | char(20) | YES | | NULL | |
| col01 | char(20) | YES | | NULL | |
| col04 | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段NOT NULL约束与默认值
ALTER TABLE <tab_name> MODIFY <col_name> TYPE NOT NULL DEFAULT <默认值>;
mysql> alter table alter_tab01 modify id bigint not null default 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_tab01;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| new_col02 | char(2) | YES | | NULL | |
| id | bigint(20) | NO | | 1 | |
| col03 | char(20) | YES | | NULL | |
| col01 | char(20) | YES | | NULL | |
| col04 | char(20) | NO | | NULL | |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段的默认值
ALTER TABLE <tab_name> ALTER <col_name> SET DEFAULT <默认值>;
ALTER TABLE <tab_name> ALTER <col_name> DROP DEFAULT;
mysql> alter table alter_tab01 alter new_col02 set default '01';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_tab01;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| new_col02 | char(2) | YES | | 01 | |
| id | bigint(20) | NO | | 1 | |
| col03 | char(20) | YES | | NULL | |
| col01 | char(20) | YES | | NULL | |
| col04 | char(20) | NO | | NULL | |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec) mysql> alter table alter_tab01 alter new_col02 drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_tab01;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| new_col02 | char(2) | YES | | NULL | |
| id | bigint(20) | NO | | 1 | |
| col03 | char(20) | YES | | NULL | |
| col01 | char(20) | YES | | NULL | |
| col04 | char(20) | NO | | NULL | |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改表的存储引擎
ALTER TABLE <tab_name> ENGINE=<引擎名>【MyISAM | InnoDB | BDB | Memory | Merge | Archive | Federated | Cluster/NDB | Other】
mysql> show table status like 'alter_tab01'\G
*************************** 1. row ***************************
Name: alter_tab01
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2018-05-03 16:11:39
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec) mysql> alter table alter_tab01 engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show table status like 'alter_tab01'\G
*************************** 1. row ***************************
Name: alter_tab01
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 54887620458577919
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2018-05-03 16:12:35
Update_time: 2018-05-03 16:12:35
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
修改表的名称
ALTER TABLE <tab_name> RENAME TO <new_name>;
mysql> alter table alter_tab01 rename to alter_tab02;
Query OK, 0 rows affected (0.00 sec) mysql> show table status like 'alter_tab02'\G
*************************** 1. row ***************************
Name: alter_tab02
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2018-05-03 16:14:02
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)