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

MySQLMySQLi Database

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)
raja
Published on 22-Oct-2018 13:37:39
Advertisements