Add the field _ MySQL in the specified position of the MySQL database

Adding a field to a specified position in the MySQL database is a basic MySQL syntax. here is an example.

View plaincopy to clipboardprint?
Mysql> -- add a field to the test database at a specified position
Mysql> drop table if exists t;
Query OK, 0 rows affected (0.06 sec)
Mysql> create table t (age int, address varchar (50 ));
Query OK, 0 rows affected (0.06 sec)
Mysql> desc t;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Age | int (11) | YES | NULL |
| Address | varchar (50) | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
2 rows in set (0.02 sec)
Mysql> alter table t add column name varchar (20) after age;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc t;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Age | int (11) | YES | NULL |
| Name | varchar (20) | YES | NULL |
| Address | varchar (50) | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
3 rows in set (0.00 sec)
Mysql> alter table t add column id int FIRST;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc t;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | NULL |
| Age | int (11) | YES | NULL |
| Name | varchar (20) | YES | NULL |
| Address | varchar (50) | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
4 rows in set (0.00 sec)
Mysql>
Mysql> -- add a field to the test database at a specified position
Mysql> drop table if exists t;
Query OK, 0 rows affected (0.06 sec)
Mysql> create table t (age int, address varchar (50 ));
Query OK, 0 rows affected (0.06 sec)
Mysql> desc t;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Age | int (11) | YES | NULL |
| Address | varchar (50) | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
2 rows in set (0.02 sec)
Mysql> alter table t add column name varchar (20) after age;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc t;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Age | int (11) | YES | NULL |
| Name | varchar (20) | YES | NULL |
| Address | varchar (50) | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
3 rows in set (0.00 sec)
Mysql> alter table t add column id int FIRST;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> desc t;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | NULL |
| Age | int (11) | YES | NULL |
| Name | varchar (20) | YES | NULL |
| Address | varchar (50) | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
4 rows in set (0.00 sec)
Mysql>

The related syntax is described

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name alter_specification [, alter_specification]... alter_specification: table_option... | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]