Menu Close

How to drop a column in mysql table

In this 101 article, I will show how to drop/remove a column from a table in MySQL.

In this article I will use an example table:

CREATE TABLE tb(
c1 INT PRIMARY KEY,
c2 char(1),
c3 varchar(2)
) ENGINE=InnoDB;

To remove a column we will make use of ALTER TABLE command:

ALTER TABLE tb DROP COLUMN c2;

The command allows you to remove multiple columns at once:

ALTER TABLE tb DROP COLUMN c2, DROP COLUMN c3;

If you are running MySQL 5.6 onwards, you can make this operation online, allowing other sessions to read and write to your table while the operation is been performed:

ALTER TABLE tb DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

Reference:

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Liked? Help and Share!