Generally, database developers add one or more columns anywhere in a MySQL table without preferences. But sometimes, you may need to add one or more columns after another column in MySQL. In this article, we will learn how to add column after another column in MySQL.
How to Add Column After Another Column in MySQL
Let us say you have the following MySQL table sales(id, created_at, amount).
mysql> create table sales(id int, created_at date, amount int);
Let us say you want to add another column product after created_at column. Here is the command to do it.
alter table table_name add column column_name1 column_definition AFTER column_name2
In the above command, we use ALTER TABLE command to add column, followed by ADD COLUMN command. We need to specify the new column name after ADD COLUMN, followed by its definition. Then we need to mention AFTER clause and then the column name after which you want to add the new column.
mysql> ALTER TABLE sales ADD COLUMN `product` varchar(6) AFTER `created_at`,
Let us say you want to add multiple columns product, modified at, quantity after created_at, then here is how to do it using multiple ADD COLUMN commands, each followed by AFTER clause.
ALTER TABLE sales ADD COLUMN `product` varchar(6) AFTER `created_at`, ADD COLUMN `modified_at` date AFTER `product`, ADD COLUMN `quantity` int AFTER `modified_at`;
In this case, MySQL will first add product column after created_at, modified_at column after created_at, and quantity column after modified_at.
You can also use a single AFTER statement after the last ADD COLUMN command.
ALTER TABLE sales ADD COLUMN `quantity` int, ADD COLUMN `modified_at` date, ADD COLUMN `product` varchar(6) AFTER `created_at`;
But please note, in this case, the last ADD COLUMN statement should mention the first column that you want to be added. In this case it works in reverse manner. First product column is added after created_at, then modified_at is added, then quantity is added. In both cases, the end result is the same.
In this article, we have learnt how to add one or more columns after a specific column in MySQL. You can use them to customize your database schema as per your requirement.
Also read:
How to Retrieve MySQL Username and Password
How to Find & Replace Text in Entire Table in MySQL
How to Take Backup of Single Table in MySQL
How to Show Indexes on Table or Database in MySQL
How to Set Global SQL Mode in MySQL