MySQL – Add a Column at Particular Ordinal Position in Table

Sometimes you may need to add a column at a particular ordinal position in the table. In SQL Server there is no option to do it via SQL although you can use Designer from SQL Server Management Studio. In MySQL you can do it as part of ALTER TABLE statement.

Let us consider the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Suppose you want to add another column named modified_date between item_id and sales_date, you can use the following command

ALTER TABLE sales ADD COLUMN modified_date DATETIME after item_id;

Now the new column named modified_date will be added after item_id. You can check this using the following command

Field      Type    Null    Key Default Extra
--------------------------------------------------------------
sales_id    int(11) NO  PRI     auto_increment
item_id int(11) YES         
modified_date  datetime    YES         
sales_date  datetime    YES         
sales_amount    decimal(12,2)   YES

This is sometimes useful. In relational database the order of column in a table should not matter. The database design and modeling should be such that there should not be any dependency on the order the columns are created inside MySQL table. This is the reason, in SQL Server we can’t do this operation via T-SQL and when we do with the help of SSMS, it practically creates and drop the entire table in the background – (a very bad thing).

Reference: Pinal Dave (https://blog.sqlauthority.com)