인생을 편하게 하는 테이블 정의서 스크립트

구글에서 테이블 정의서를 생성하는 스크립트를 검색하면 아래와 같이 많이 나온다.

SELECT
 a.TABLE_NAME '테이블명',
 b.ORDINAL_POSITION '순번',
 b.COLUMN_NAME '필드명',
 b.DATA_TYPE 'DATA TYPE',
 b.COLUMN_TYPE '데이터길이',
 b.COLUMN_KEY 'KEY',
 b.IS_NULLABLE 'NULL값여부',
 b.EXTRA '자동여부',
 b.COLUMN_DEFAULT '디폴트값',
 b.COLUMN_COMMENT '필드설명'
FROM information_schema.TABLES a 
JOIN information_schema.COLUMNS b 
ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
WHERE a.TABLE_SCHEMA = 'database 명'
ORDER BY a.TABLE_NAME, b.ORDINAL_POSITION;

schema 의 정보를 담고있는 information_schema 의 TABLES 와 COLUMNS 를 join 해서 만들겠다는 건데, index 정보가 없어서 수작업을 해야하나 하다 그냥 비슷한 방식으로 다시 만들었다.

SELECT
   x.TABLE_NAME '테이블 명',
   x.ORDINAL_POSITION '순번',
   x.COLUMN_NAME '필드 명',
   x.COLUMN_TYPE '데이터 타입',
   x.COLUMN_KEY 'KEY',
   y.INDEX_NAME '인덱스 명',
   x.IS_NULLABLE 'NULL 여부',
   x.EXTRA '자동 여부',
   x.COLUMN_DEFAULT '디폴트 값',
   x.COLUMN_COMMENT '필드 설명'
FROM information_schema.COLUMNS x 
LEFT JOIN information_schema.STATISTICS y 
ON x.TABLE_NAME = y.TABLE_NAME AND x.COLUMN_NAME = y.COLUMN_NAME
WHERE x.TABLE_SCHEMA = 'database 명'
ORDER BY x.TABLE_NAME, x.ORDINAL_POSITION;

index 정보가 있는 information_schema.STATISTICS 테이블과 컬럼 정보를 가진 COLUMNS 를 left join 하여 가져온다.

해당 쿼리를 수행하면 아래와 같이 입력한 schema 모든 테이블 명이 다 붙어서 한 결과를 낸다.

+--------------------+--------+-----------------------+------------------+-----+---------------+-------------+----------------+---------------+---------------+
| 테이블 명            | 순번    | 필드 명                 | 데이터 타입         | KEY | 인덱스 명       | NULL 여부    | 자동 여부         | 디폴트 값       | 필드 설명       |
+--------------------+--------+-----------------------+------------------+-----+---------------+-------------+----------------+---------------+---------------+
| account            |      1 | id                    | bigint           | PRI | PRIMARY       | NO          | auto_increment | NULL          |               |
| account            |      2 | created               | datetime         |     | NULL          | NO          |                | NULL          |               |
| account            |      3 | login_id              | varchar(45)      |     | NULL          | NO          |                | NULL          |               |
| account            |      4 | name                  | varchar(32)      |     | NULL          | NO          |                | NULL          |               |
| account            |      5 | password              | varchar(64)      |     | NULL          | NO          |                | NULL          |               |
| account            |      6 | updated               | datetime         |     | NULL          | NO          |                | NULL          |               |
| account_log        |      1 | id                    | varchar(25)      | PRI | PRIMARY       | NO          |                | NULL          |               |
| account_log        |      2 | created               | datetime         |     | NULL          | NO          |                | NULL          |               |
| account_log        |      3 | details               | text             |     | NULL          | NO          |                | NULL          |               |
| account_log        |      4 | level                 | varchar(10)      |     | NULL          | NO          |                | NULL          |               |
...

좀 볼만한 테이블 정의서로 만들기 위해 테이블 명에 따라 excel sheet 를 만들어 주고 싶었고, 살짝 코딩이 들어갔다.

// extract.js

const mysql = require('mysql');
const excel = require('xlsx');

/**
 * 
 * dbinfo 가 유일한 사용자 수정 부분
 * db 접속정보 및 조회 할 스키마 선택
 * 
 */

const dbinfo = {
    host: 'my_host',
    port: '3306',
    user: 'root',
    password: 'my_password',
    database: 'my_database',
    multiplestatements: true
}

const db = mysql.createConnection(dbinfo);

const sql = `SELECT
x.TABLE_NAME '테이블 명',
x.ORDINAL_POSITION '순번',
x.COLUMN_NAME '필드 명',
x.COLUMN_TYPE '데이터 타입',
x.COLUMN_KEY '키',
y.INDEX_NAME '인덱스 명',
x.IS_NULLABLE 'NULL 여부',
x.EXTRA '자동 여부',
x.COLUMN_DEFAULT '디폴트 값',
x.COLUMN_COMMENT '필드 설명'
FROM information_schema.COLUMNS x LEFT JOIN information_schema.STATISTICS y ON x.TABLE_NAME = y.TABLE_NAME AND x.COLUMN_NAME = y.COLUMN_NAME
WHERE x.TABLE_SCHEMA = '${dbinfo.database}'
ORDER BY x.TABLE_NAME, x.ORDINAL_POSITION;`

const wb = excel.utils.book_new();

db.query(sql, dbinfo.database, (err, results, field) => {
    const extractedByTableName = results.reduce((acc, cur) => {
        const tableName = cur['테이블 명'];
        
        if (acc[tableName] === undefined) {
            acc[tableName] = [];
        }

        acc[tableName].push(cur);
        return acc;
    }, {});
    
    for(const [key, value] of Object.entries(extractedByTableName)) {
        const ws = excel.utils.json_to_sheet(value);
        excel.utils.book_append_sheet(wb, ws, key);
    }
    excel.writeFile(wb, "result.xlsx");
    
    db.end();
});

잘 작동한다.