MySQL 데이터베이스에서 특정 컬럼들을 동시에 가지고 있는 테이블을 찾아야 할 때가 있습니다. 예를 들어 columnA와 columnB라는 두 컬럼을 모두 포함하는 모든 테이블 목록이 필요한 경우, information_schema.columns를 활용하면 손쉽게 조회할 수 있습니다.
information_schema.columns란?
information_schema는 MySQL 서버가 관리하는 메타데이터(데이터베이스, 테이블, 컬럼 정보 등)를 담고 있는 시스템 스키마입니다. 그중 columns 테이블에는 모든 데이터베이스의 컬럼 정보가 저장되어 있어, 이를 조회하면 원하는 컬럼을 포함한 테이블을 찾을 수 있습니다.
여기서는 설명을 위해 columnA 대신 Id, columnB 대신 Name 컬럼을 사용하겠습니다.
쿼리 작성 방법
다음 쿼리는 Id와 Name 컬럼을 모두 포함하는 테이블을 조회합니다.
mysql> select table_name as TableNameFromWebDatabase
-> from information_schema.columns
-> where column_name IN ('Id', 'Name')
-> group by table_name
-> having count(*) = 3;
쿼리 핵심 포인트
- WHERE 절:
IN ('Id', 'Name')조건으로 해당 이름을 가진 컬럼만 필터링합니다. - GROUP BY: 테이블별로 결과를 묶어줍니다.
- HAVING count(*) = 3: 조건에 맞는 컬럼 개수가 정확히 일치하는 테이블만 남깁니다. 두 컬럼만 확인하려면
= 2로 지정하면 됩니다.
참고: 위 예제에서
having count(*) = 3으로 지정한 이유는 실제 데모 환경에서 세 개의 컬럼 조건을 확인했기 때문입니다. 여러분의 상황에 맞게 개수를 조절해서 사용하세요.
실행 결과
위 쿼리를 실행하면 다음과 같이 Id와 Name 컬럼을 포함한 테이블 목록이 출력됩니다.
+--------------------------+
| TableNameFromWebDatabase |
+--------------------------+
| student |
| distinctdemo |
| secondtable |
| groupconcatenatedemo |
| indemo |
| ifnulldemo |
| demotable211 |
| demotable212 |
| demotable223 |
| demotable233 |
| demotable251 |
| demotable255 |
+--------------------------+
12 rows in set (0.25 sec)
결과 검증하기
조회된 테이블 중 하나를 선택해 실제로 해당 컬럼들이 존재하는지 확인해 보겠습니다. DESC 명령어로 테이블 구조를 살펴볼 수 있습니다.
mysql> desc demotable233;
실행 결과를 보면 Id와 Name 컬럼이 실제로 존재하는 것을 확인할 수 있습니다.
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
마무리
이처럼 information_schema.columns와 GROUP BY, HAVING 절을 조합하면 특정 컬럼 조합을 가진 테이블을 빠르게 찾을 수 있습니다. 대규모 데이터베이스에서 테이블 구조를 분석하거나 마이그레이션 작업 전 사전 조사를 할 때 매우 유용한 기법이니 꼭 기억해 두세요.