Computer >> 컴퓨터 >  >> 프로그래밍 >> MySQL

MySQL에서 데이터베이스의 모든 테이블에서 특정 열(컬럼) 찾는 방법

데이터베이스 내 여러 테이블 중 어느 테이블에 특정 컬럼이 존재하는지 확인해야 할 때가 있습니다. 이럴 때 INFORMATION_SCHEMA.COLUMNS 뷰를 활용하면 손쉽게 해결할 수 있습니다. 핵심은 COLUMN_NAME 조건에 LIKE 연산자를 사용해 원하는 컬럼명을 지정하는 것입니다.

기본 쿼리 구문

아래 예시는 'web'이라는 스키마(데이터베이스) 안에서 'StudentName'이라는 이름을 가진 컬럼이 포함된 모든 테이블을 조회하는 방법입니다.

mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE column_name LIKE '%StudentName%'
    -> AND table_schema = 'web';

주요 컬럼 설명

  • TABLE_NAME: 해당 컬럼이 속한 테이블의 이름
  • COLUMN_NAME: 검색된 컬럼의 이름
  • DATA_TYPE: 컬럼의 데이터 타입(varchar, json 등)
  • IS_NULLABLE: NULL 값 허용 여부(YES 또는 NO)
  • COLUMN_DEFAULT: 컬럼에 설정된 기본값

실행 결과

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

+-------------------+-------------+-----------+-------------+----------------+
| TABLE_NAME        | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |COLUMN_DEFAULT  |
+-------------------+-------------+-----------+-------------+----------------+
| demotable215      | StudentName | varchar   | YES         | NULL           |
| demotable221      | StudentName | varchar   | YES         | NULL           |
| demotable224      | StudentName | varchar   | YES         | NULL           |
| demotable234      | StudentName | varchar   | YES         | NULL           |
| demotable269      | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | NO          | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | json      | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | NO          | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| view_DemoTable    | StudentName | varchar   | YES         | NULL           |
+-------------------+-------------+-----------+-------------+----------------+
19 rows in set (0.07 sec)

결과 분석

실행 결과를 보면 'web' 데이터베이스 안에는 총 19개의 위치에서 'StudentName' 컬럼을 사용하고 있음을 알 수 있습니다. 일반 테이블뿐만 아니라 뷰(view_DemoTable)까지 함께 조회되며, 대부분 varchar 타입이지만 하나는 json 타입으로 정의되어 있는 점도 확인할 수 있습니다. 또한 IS_NULLABLE 값이 YES인 경우와 NO인 경우가 섞여 있어, 같은 이름의 컬럼이라도 테이블마다 제약 조건이 다르게 설정되어 있음을 한눈에 파악할 수 있습니다.

활용 팁

  • table_schema 조건을 생략하면 서버 전체 데이터베이스를 대상으로 검색하므로, 범위를 좁히려면 반드시 지정하는 것이 좋습니다.
  • 정확한 컬럼명을 알고 있다면 LIKE '%StudentName%' 대신 = 'StudentName'을 사용하면 더 빠르고 정확하게 검색할 수 있습니다.
  • 대소문자 구분 설정(collation)에 따라 검색 결과가 달라질 수 있으므로 주의가 필요합니다.

이처럼 INFORMATION_SCHEMA.COLUMNS를 활용하면 복잡한 데이터베이스 구조 속에서도 원하는 컬럼이 어느 테이블에 있는지 빠르고 정확하게 찾아낼 수 있습니다.