How To Delete A Column In Mysql

In this tutorial, we will explain how to delete a column in MySQL. MySQL is a popular open-source database management system that is widely used for various applications.

Deleting a column in MySQL can be achieved through the command line, phpMyAdmin, or other GUI tools. We will be focusing on using the command line in this tutorial, as it is applicable across all platforms.

Step 1: Connect to the MySQL server

You need to connect to the MySQL server using the command line. To do this, use the following command:

1
mysql -u <username> -p

Replace <username> with your MySQL username. You will be prompted to enter your password to authenticate.

Step 2: Select the database

After connecting to the MySQL server, select the database in which the column you want to delete resides. Use the following command:

1
USE <database>;

Replace <database> with your target database name.

Step 3: Delete the column

To delete a column, use the following query:

1
ALTER TABLE <table_name> DROP COLUMN <column_name>;

Replace <table_name> with the table name that contains the column you want to delete, and <column_name> with the name of the column to be removed.

Example:

Let’s say we have a database named “testdb” and a table named “employees” with the following structure:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| first_name| varchar(45) | YES  |     | NULL    |       |
| last_name | varchar(45) | YES  |     | NULL    |       |
| age       | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

We want to delete the “age” column from the “employees” table in the “testdb” database.

Here are the commands to execute:

1
2
3
mysql -u root -p
USE testdb;
ALTER TABLE employees DROP COLUMN age;

After executing these commands, the “age” column will be removed from the “employees” table.

Full Code

1
2
3
mysql -u <username> -p
USE <database>;
ALTER TABLE <table_name> DROP COLUMN <column_name>;

Conclusion

We have demonstrated how to delete a column in MySQL using command line. It’s a simple task that can be achieved using the “ALTER TABLE” and “DROP COLUMN” queries.

Always be cautious when deleting columns, as data loss is irreversible. Make sure to take a backup of your database before performing any modifications.