MySQL: modifying columns in a table
Modifying columns in a MySQL table
Modify the column syntax in the table:
Create a table with N columns. You can also add, delete, or modify columns.
Alter table name add column name column type column parameter; [add the column at the end of the table]
Example: alter table m1 add username char (20) not null default '';
Alter table name add column name column type column parameter after a column; [add a new column after a column]
Example: alter table m1 add username char (20) not null default '';
If you want to add a column at the beginning of the table, use first
Alter table name add column name column type column parameter first;
For example, alter table m1 add pid int not null first;
Bytes -------------------------------------------------------------------------------------------------------------------
Syntax for deleting columns in a table:
Alter table Name drop column name;
For example, alter table m1 drop pid;
Bytes ------------------------------------------------------------------------------------------------------------------
Modify the syntax of the column type in the table:
Alter table name, modify column name, new type, new parameter;
Example: alter table m1 modify gender char (4) not null default '';
Bytes -------------------------------------------------------------------------------------------------------------------
Syntax for modifying column names and types in a table:
Alter table name change old column name new type new parameter;
Example: alter table m1 change id uid int unsigned;
Bytes ---------------------------------------------------------------------------------------------------------------
The following are the operations in mysql:
Mysql> create table m1 (
-> Id int unsigned auto_increment primary key
-> );
Query OK, 0 rows affected (0.62 sec)
Mysql> desc m1;
+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +
1 row in set (0.14 sec)
Mysql> alter table m1 add username char (20) not null default '';
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
+ ---------- + ------------------ + ------ + ----- + --------- + ---------------- +
2 rows in set (0.01 sec)
Mysql> alter table m1 add birth date not null default '2017-00-00 ';
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
3 rows in set (0.01 sec)
Mysql> alter table m1 add gender char (1) not null default ''after username;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
4 rows in set (0.01 sec)
Mysql> # If you want to add a column at the beginning of the table, use first
Mysql> alter table m1 add pid int not null default ''first;
ERROR 1067 (42000): Invalid default value for 'pid'
Mysql> alter table m1 add pid int not null first;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Pid | int (11) | NO | NULL |
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
5 rows in set (0.01 sec)
Mysql> # delete a column
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Pid | int (11) | NO | NULL |
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
5 rows in set (0.01 sec)
Mysql> alter table m1 drop pid;
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
4 rows in set (0.01 sec)
Mysql> alter table m1 modify gender char (4) not null default '';
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (4) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
4 rows in set (0.05 sec)
Mysql> alter table m1 change id uid int unsigned;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ------- +
| Uid | int (10) unsigned | NO | PRI | 0 |
| Username | char (20) | NO |
| Gender | char (4) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ------- +
4 rows in set (0.01 sec)
Mysql> exit;
----------------------------------------------------------------------------------
Welcome to explore and learn ......