How does MySQL identify if a certain column exists in all tables?-Mysql Tutorial-php.cn

How does MySQL identify if a certain column exists in all tables?

王林

Release： 2023-09-01 22:57:03

forward

947 people have browsed it

How does MySQL identify if a certain column exists in all tables?

To identify column names, use INFORMATION_SCHEMA.COLUMNS in MySQL. The following is the syntax -

select table_name,column_name
from INFORMATION_SCHEMA.COLUMNS
where table_schema = SCHEMA()
andcolumn_name=&#39;anyColumnName&#39;;

Copy after login

Let us implement the above query in order to identify a column with its existence in all tables. Here, we are finding the existence of column EmployeeAge −

mysql> select table_name,column_name
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
   AND column_name=&#39;EmployeeAge&#39;;

Copy after login

This will produce the following output displaying the tables with specific column “EmployeeAge” −

+---------------+-------------+
| TABLE_NAME    | COLUMN_NAME |
+---------------+-------------+
| demotable1153 | EmployeeAge |
| demotable1297 | EmployeeAge |
| demotable1303 | EmployeeAge |
| demotable1328 | EmployeeAge |
| demotable1378 | EmployeeAge |
| demotable1530 | EmployeeAge |
| demotable1559 | EmployeeAge |
| demotable1586 | EmployeeAge |
| demotable1798 | EmployeeAge |
| demotable1901 | EmployeeAge |
| demotable511  | EmployeeAge |
| demotable912  | EmployeeAge |
+---------------+-------------+
12 rows in set (0.00 sec)

Copy after login

To prove, let us check the description of any of the above tables −

mysql> desc demotable1153;

Copy after login

The EmployeeAge column exists in −

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| EmployeeId   | int(11)     | NO   | PRI | NULL    | auto_increment |
| EmployeeName | varchar(40) | YES  | MUL | NULL    |                |
| EmployeeAge  | int(11)     | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Copy after login

The above is the detailed content of How does MySQL identify if a certain column exists in all tables?. For more information, please follow other related articles on the PHP Chinese website!