데이터 타입으로 컬럼을 필터링해야 하는 이유
MySQL을 운영하다 보면 수십 개, 수백 개의 테이블에 흩어져 있는 컬럼 중에서 특정 데이터 타입(TEXT, VARCHAR, INT 등)을 가진 컬럼만 한눈에 확인하고 싶은 경우가 자주 있습니다. 예를 들어, 전체 데이터베이스에서 TEXT 타입으로 선언된 컬럼을 찾아 용량을 점검하거나 스키마를 리팩토링할 때 이런 조회 기능이 매우 유용합니다.
이럴 때는 MySQL이 기본적으로 제공하는 메타데이터 카탈로그인 INFORMATION_SCHEMA.COLUMNS 테이블을 활용하면 간단하게 해결할 수 있습니다.
기본 문법
데이터 타입을 기준으로 필터를 설정하려면 아래와 같은 쿼리 구문을 사용합니다.
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'yourDataTypeName';
DATA_TYPE 컬럼에는 해당 열의 실제 데이터 타입 이름이 소문자로 저장되어 있으므로, 조건 값도 반드시 소문자로 작성해야 정확한 결과를 얻을 수 있습니다.
TEXT 타입 컬럼 조회 실전 예제
이제 위 문법을 실제로 적용하여, 서버에 존재하는 모든 테이블 중 필드 타입이 text인 컬럼만 조회해 보겠습니다.
mysql> SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'text';
이 쿼리를 실행하면 다음과 같은 형태의 결과가 출력됩니다. (실제 출력 결과는 환경에 따라 다르며, 아래는 일부 발췌입니다.)
+---------------------------------------------+--------------------------------+
| TABLE_NAME | COLUMN_NAME |
+---------------------------------------------+--------------------------------+
| COLUMNS | COLUMN_DEFAULT |
| COLUMNS | COLUMN_COMMENT |
| FILES | FILE_NAME |
| PARTITIONS | PARTITION_DESCRIPTION |
| PARTITIONS | PARTITION_COMMENT |
| ROUTINES | ROUTINE_COMMENT |
| password_history | Password |
| component | component_urn |
| innodb_buffer_stats_by_schema | object_schema |
| innodb_buffer_stats_by_table | object_name |
| schema_redundant_indexes | redundant_index_columns |
| statement_analysis | total_latency |
| user_summary | current_memory |
| host_summary | statement_latency |
| processlist | last_wait_latency |
| help_topic | description |
| help_topic | example |
| slave_master_info | User_password |
| posts | post_content |
| textdemo | sentence |
+---------------------------------------------+--------------------------------+
240 rows in set (0.28 sec)
결과를 보면 시스템 테이블(COLUMNS, FILES, PARTITIONS 등)부터 사용자가 직접 생성한 테이블(textdemo, posts 등)까지, 데이터 타입이 text인 모든 컬럼이 테이블 이름과 함께 나열됩니다. 위 예제에서는 총 240개의 행이 반환되었습니다.
응용: 특정 데이터베이스로 검색 범위 좁히기
서버 전체가 아니라 특정 데이터베이스 내에서만 조회하고 싶다면 TABLE_SCHEMA 조건을 추가하면 됩니다.
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'text'
AND TABLE_SCHEMA = 'yourDatabaseName';
이렇게 하면 불필요한 시스템 테이블의 결과가 제외되어 원하는 데이터베이스의 TEXT 컬럼만 깔끔하게 확인할 수 있습니다.
마무리 정리
INFORMATION_SCHEMA.COLUMNS 뷰와 WHERE DATA_TYPE 조건만 활용하면 별도의 도구 없이 SQL 한 줄로 데이터 타입별 컬럼 목록을 손쉽게 추출할 수 있습니다. 스키마 분석, 대용량 텍스트 컬럼 점검, 마이그레이션 사전 조사 등 다양한 상황에서 활용해 보시기 바랍니다.