Alter Table Add Field after Another Field MySQL example via Command Line

This is another article related to MySQL Database Server on how to actually add field after another field by doing it via command line. That is actually an action which is going to alter or to change the table itself. Since the change is done by executing it from command line without actually using any help from Graphical MySQL Editor, every steps must be done manually. Below are steps which must be done accordingly :

1. First of all, make sure that the service or the port listening for MySQL Database service is alive and it is in the state of listening incoming request. For details, read the article titled ‘Check MySQL Service Status‘ in this link.

2. After checking MySQL Service Status and considering that the service itself is active, try to connect to the MySQL Database Server. Read this article titled ‘Remote Execute MySQL Query via Command Line‘ which can be visited in this link. Basically, either accessing it locally or remotely, it has the same rules and syntax performed.

3. After successfully connecting to MySQL Database Remote console, try to select the associated database which has the table in it.

4. Successfully selecting the database where the table appointed to be altered the last step which is the most important part is the step for altering the table. For an example the following is shown as follows :

A table named ‘user’ and it can be described as follow to see details of fields exist :

mysql> desc user;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| username  | varchar(255) | YES  |     | NULL    |                |
| password  | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0,01 sec)
mysql>

There are only three fields available as shown in the above output using command ‘desc’ for describing the table’s fields. The scenario which is going to be executed is adding a field after another field. Specifically, inserted a field named ‘firstname’ after the field ‘id’. Try to select the content first as shown below :

mysql> select * from user;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  1 | james    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  2 | mark     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  3 | steve    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  4 | jack     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
+----+----------+-------------------------------------------+
4 rows in set (0,00 sec)
mysql>

As shown in the above output, it can be clearly viewed that the record only has three fields associated. So, in the following lines and output, the fields are going to be changed as follows based on the syntax:

alter table table_name add new_field_name field_type after existing_field
Description : 
alter table : the syntax of SQL which is used to change the table structure 
table_name : the name of the table which is going to be altered
add : the syntax of SQL which is used to add a new field or column
new_field_name : the name of the new field or column
field_type : the type or the attribute of the field or column
after : the syntax of SQL which is used to specify the field's order location and in this case the new one will be placed after this field or column
existing_field : the existed field or column

Below is an example to perform the above query for altering the table by adding field or column after another field of colulmn :

mysql> alter table user add firstname varchar(255) not null after id;
Query OK, 0 rows affected (0,28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>

The above command successfully carried on, below is how to make sure that the impact of the query above is actually working :

mysql> desc user;
+-----------+--------------+------+---------+---------+----------------+
| Field     | Type | Null  | Key  | Default | Extra   |                |
+-----------+--------------+------+---------+---------+----------------+
| id        | int(11)      | NO   | PRI     | NULL    |auto_increment  |
| firstname | varchar(255) | NO   |         | NULL    |                |
| username  | varchar(255) | YES  |         | NULL    |                |
| password  | varchar(255) | YES  |         | NULL    |                |
+-----------+--------------+------+---------+---------+----------------+
4 rows in set (0,01 sec)
mysql>

And the other way is by selecting the content of the table through executing ‘select’ query on the table as shown in the output below :

mysql> select * from user; 
+----+-----------+----------+------------------------------------------+
| id | firstname | username | password                                 |
+----+-----------+----------+------------------------------------------+
| 1 |            | james    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545|
| 2 |            | mark     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545|
| 3 |            | steve    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545|
| 4 |            | jack     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545|
+----+-----------+----------+------------------------------------------+
4 rows in set (0,01 sec)
mysql>

So, the additional field or column has already exist and it is located after another field in the table shown above.