MySQL information_schema로 세부 정보 확인하기


특정 어느 한 개의 테이블에 대한 스키마 정보를 알고 싶을 때,

desc 테이블명

을 실행하면 테이블의 스키마를 확인할 수 있다. 그런데, 전체 스키마 중 어떤 특징을 기준으로 조회하거나 내용을 걸러서 확인해야 한다면, 모든 테이블을 대상으로 desc 하는 것은 비효율적이다. DB 테이블 구조를 쿼리로 확인할 수 있다면 도움이 될 것이므로, 어떤 정보를 조회할 수 있는지 다음과 같이 확인해 보자.

 

테스트스키마 및 테이블 구성

테스트를 위해, 아래와 같이 스키마를 만들고 몇 개의 테스트 데이터를 넣어보자.

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
  ('1', '3', 'The earth is like a ball.');

또 다른 예도 하나 더 생성하자.

CREATE TABLE IF NOT EXISTS `students` (
    id int(6) AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    major varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO students (name, major) VALUES
    ('John', 'Music'),
    ('Sarah', 'Art'),
    ('Steve', 'Computer Science');

 

information_schema.tables

테이블 관점에서의 메타정보를 제공한다. 주요 정보는 아래와 같다.

  • TABLE_SCHEMA
  • TABLE_NAME
  • TABLE_ROWS
  • AGV_ROW_LENGTH
  • DATA_LENGTH
  • MAX_DATA_LENGTH
  • INDEX_LENGTH
  • AUTO_INCREMENT
  • CREATE_TIME
  • UPDATE_TIME
  • TABLE_COLLATION
  • CREATE_OPTIONS
  • TABLE_COMMENT

 

information_schema.columns

컬럼 관점에서의 메타정보를 제공한다. 주요 정보는 아래와 같다.

  • TABLE_SCHEMA
  • TABLE_NAME
  • COLUMN_NAME
  • IS_NULLABLE
  • DATA_TYPE
  • CHARACTER_MAXIMUM_LENGTH
  • NUMERIC_PRECISION
  • CHARACTER_SET_NAME
  • COLLATION_NAME
  • COLUMN_TYPE
  • COLUMN_KEY
  • EXTRA
  • PRIVILEGES
  • COLUMN_COMMENT

 

활용

  • PK로 선언된 컬럼 조회하기
    SELECT
         TABLE_NAME,
         COLUMN_NAME,
         IS_NULLABLE,
         DATA_TYPE,
         COLUMN_TYPE,
         COLUMN_KEY,
         EXTRA
    FROM
       information_schema.COLUMNS col
    WHERE
      col.table_schema = 'mydb'
      and column_key = 'PRI';
  • AUTO_INCREMENT 속성 부여된 컬럼 조회하기
    위의 쿼리 조건에 extra = 'auto_increment'를 추가하면 된다.
    SELECT
         TABLE_NAME,
         COLUMN_NAME,
         IS_NULLABLE,
         DATA_TYPE,
         COLUMN_TYPE,
         COLUMN_KEY,
         EXTRA
    FROM
       information_schema.COLUMNS col
    WHERE
      col.table_schema = 'mydb'
      and column_key = 'PRI' 
      and extra = 'auto_increment';
  • PK로 잡혀 있는 int 타입의 컬럼의 auto_increment 값 확인하기
    SELECT
      col.TABLE_NAME,
      col.COLUMN_NAME,
      col.COLUMN_TYPE,
      tab.AUTO_INCREMENT
    FROM
       information_schema.TABLES tab,
       information_schema.COLUMNS col
    WHERE
      col.TABLE_NAME = tab.TABLE_NAME
      and col.table_schema = 'mydb'
      and col.extra = 'auto_increment'
      and col.column_key = 'PRI'
      and col.COLUMN_TYPE = 'int';    

물론, 기본적으로 테이블 이름들만 추출하는 등의 기본적인 연산도 메타정보를 활용하면 편리하다.

 

쿼리로 테이블 이름 조회하기 - MySQL, PostgreSQL, Oracle, MS-SQL

테이블 이름을 조사할 일이 있을 때, DB Client에서 조회하는 것이 불편할 때가 있다. 쿼리로 테이블 이름을 추출하려면 어떻게 할까? 주요 DB별로 테이블 정보를 조회하는 쿼리는 다음과 같다. MySQL

luran.me

 

댓글(0)

Designed by JB FACTORY