MySQL Drop Columns Tutorial and Examples

Occasionally, you may need to drop one or more columns from an existing table for the following reasons:

• This column is redundant.
• This column has been replaced by another column.
• The data type of this column has changed, you need to drop the column, then recreate the column and import the data.

Suppose, you have a user table which has information like username, email, password etc. But for security, you need to migrate the password column to another table, and then drop the password column in the user table.

MySQL allows you to use the ALTER TABLEstatement to modify an existing table . To drop one or more columns from a table, use the ALTER TABLE ... DROP COLUMN statement.

Drop Column Attentions

Dropping a column is a dangerous action. Like using DROP TABLE to drop a table, once a column has been dropped, it is very difficult for you to restore the data in it.

Be sure to determine the necessity of this action before dropping a column.

In addition, dropping columns can also bring some hidden problems:

• If the column to be dropped is referenced by a foreign key, you need to drop the foreign key synchronously before proceeding. This may destroy the integrity of the data.
• After dropping the column, you need to synchronously modify the code in your application that depends on this column. This includes references in stand-alone applications and triggers, views, stored procedures, and functions.
• For a large table, dropping columns is a time-consuming process.

Important: Be sure to back up the table and the rows in the table before Dropping .

MySQL DROP COLUMN syntax

The following is the syntax of MySQL ALTER TABLE ... DROP COLUMN statement:

ALTER TABLE table_name
DROP [COLUMN] column_name
[, DROP [COLUMN] column_name];

Here:

• The table_name is the name of the table from which the column was dropped after the ALTER TABLE keyword .
• The column_name is the name of the column to drop after the DROP COLUMN keyword.
• You can omit the COLUMN keyword in the DROP COLUMN keyword. It is optional.
• If you need to drop multiple columns in one statement, use multiple DROP COLUMN clauses.

Determine if column exists

Before Dropping a column from a table, you may need to first determine whether the column exists in the table.

To view information for all columns in a table, you can use DESC or SHOW COLUMNS statements. Both statements can display all the columns in a table, but the SHOW COLUMNS statement is more flexible and convenient because it can filter the result set according to your requirements.

The syntax of the DESC statement is as follows:

DESC table_name;

The syntax of the SHOW COLUMNS statement is as follows:

SHOW [FULL] COLUMNS FROM table_name [LIKE pattern]

The LIKE clause is used to specify the filter pattern.

MySQL DROP COLUMN Examples

This example demonstrates how to use the ALTER TABLE ... DROP COLUMN statement to drop one or more columns from a table.

Suppose, we have a user table with five columns for ID, username, age, email and phone number.

We create a table named user in the testdb database to store user information using the following statement:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL DEFAULT '20',
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

If you do not have the testdb database, you should create the database and select the database first using the following statement:

CREATE DATABASE testdb;
use testdb;

After the table is created, you can use the DESC statement to view all the columns in this table:

DESC user;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| age   | int          | NO   |     | 20      |                |
| email | varchar(255) | NO   |     | NULL    |                |
| phone | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Insert a row of data into the user table, as follows;

INSERT INTO user (name, age, email, phone)
values ('Tim', 20, '[email protected]', '8769232');

Drop a column using MySQL DROP COLUMN statement

To remove the age column from the user table, use the following statement:

ALTER TABLE user
DROP COLUMN age;

Note that when you drop this column, the data in this column is also dropped.

Let’s view all the columns in the user table with the DESC statement:

DESC user;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| email | varchar(255) | NO   |     | NULL    |                |
| phone | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

We can also verify the data by querying the table with the following SELECT statement:

SELECT * FROM user;

+----+------+---------------+---------+
| id | name | email         | phone   |
+----+------+---------------+---------+
|  1 | Tim  | [email protected] | 8769232 |
+----+------+---------------+---------+

Drop two columns from a table using MySQL DROP COLUMN statement

To drop the two columns email and phone from the user table, use the following statement:

ALTER TABLE user
DROP COLUMN email,
DROP COLUMN phone;

Let’s examine the rows of the user table:

SELECT * FROM user;

+----+------+
| id | name |
+----+------+
|  1 | Tim  |
+----+------+

Conclusion

MySQL provides the ALTER TABLE ... DROP COLUMN statement to drop one or more columns from a table. With the ALTER TABLE statement, you can rename tables, rename columns, add columns, drop columns, modify column properties, and more.