MySQL information_schema로 세부 정보 확인하기

 

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