Add a column in a table at a specific position in MySQL / MariaDB
ALTER command adds a column in the table in MySQL/MariaDB.
Following is the syntax of adding the Column in MySQL/ MariaDB:
ALTER TABLE table_name ADD new_column column_type
[ FIRST | AFTER column_name ];If you do not use the first and after clause, it will place the new column in the last position.
Example: Adding State column after District column in City table.
desc city;
Field |Type |Null|Key|Default|Extra |
-----------+--------+----+---+-------+--------------+
ID |int |NO |PRI| |auto_increment|
Name |char(35)|NO | | | |
CountryCode|char(3) |NO |MUL| | |
District |char(20)|NO | | | |
Population |int |NO | |0 | |
ALTER TABLE city ADD COLUMN State varchar(20) AFTER District;
desc city;
Field |Type |Null|Key|Default|Extra |
-----------+-----------+----+---+-------+--------------+
ID |int |NO |PRI| |auto_increment|
Name |char(35) |NO | | | |
CountryCode|char(3) |NO |MUL| | |
District |char(20) |NO | | | |
State |varchar(20)|YES | | | |
Population |int |NO | |0 | |