How to check if a column exist in a MySQL table?

To understand whether a column exist or not, we have the following approaches −

• With the help of DESC command
• Using SHOW command

Firstly, we will create a table with columns −

mysql> CREATE table ColumnExistDemo
-> (
-> UniqueId int,
-> UniqueName varchar(200),
-> UniqueAddress varchar(200)
-> );
Query OK, 0 rows affected (0.57 sec)

In the first approach, we will get the entire column name with meaningful information. The syntax is as follows −

DESC yourTableName;

Let us apply the above query to check whether the column name exists or not. However, this approach is not good since it display all the columns.

mysql> DESC ColumnExistDemo;

The following is the output

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| UniqueId      | int(11)      | YES  |     | NULL    |       |
| UniqueName    | varchar(200) | YES  |     | NULL    |       | 
| UniqueAddress | varchar(200) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

The second approach is better, since we can check for a single column. The syntax is as follows −

SHOW COLUMNS from `yourTableName` LIKE 'yourColumnName';

Now, I am applying the above query to check whether colum exist or not. The query is as follows −

mysql> SHOW COLUMNS from `ColumnExistDemo` LIKE 'UniqueName';

The following is the output

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| UniqueName | varchar(200) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

George John

Published on 22-Oct-2018 10:07:39

• Related Questions & Answers
• Different methods to check if a MySQL table exist?
• How do I detect if a table exist in MySQL?
• How to check if a table exists in MySQL and create if it does not already exist?
• Check if a value exists in a column in a MySQL table?
• Check if table exist without using “select from” in MySQL?
• MySQL query to check if multiple rows exist?
• How to check whether a stored procedure exist in MySQL?
• How to select from MySQL table A that does not exist in table B?
• How to check if a column exists in Pandas?
• How to add a column in a table in MySQL?
• How to delete a column from a table in MySQL?
• Check if given multiple keys exist in a dictionary in Python
• How to add a column to a MySQL table in Python?
• How to substring value in a MySQL table column?
• MySQL query to include more than one column in a table that doesn't already exist