[DB] 데이터 타입 일괄 변경

테이블에 공통적으로 들어가는 아이피 주소를 ipv4 버전 데이터로 넣으려다가

ipv6 버전 데이터도 고려해 VARCHAR(15) 에서 VARCHAR(45)로 변경하고 싶다.

 

그럴려면 컬럼을 변경해야되는데.. 테이블도 많고 하니 일괄로 변경하고 싶다.

그래서 프로시저를 사용했다.

 

우선 조건에 맞는 컬럼들을 검색하는 CURSOR를 정의해준다.

나 같은 경우에는 컬럼명이 모두 Ip로 끝나고 데이터 타입이 VARCHAR, 그리고 길이가 15인 컬럼만 조회하는 쿼리를 짰다.

 

그 다음 조회한 결과의 테이블명과 컬럼명을 FETCH 한 뒤에 컬럼 변경 쿼리를 LOOP로 돌렸다.

 

CREATE PROCEDURE UpdateDateType()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(64);
    DECLARE columnName VARCHAR(64);

    -- 조건에 맞는 컬럼들을 검색하는 커서 정의
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = 'schema_name'                -- 스키마 이름
          AND COLUMN_NAME LIKE '%Ip'                      -- 컬럼명이 'Ip'로 끝남
          AND DATA_TYPE = 'varchar'                       -- 데이터 타입이 VARCHAR
          AND CHARACTER_MAXIMUM_LENGTH = 15;             -- 길이가 15인 컬럼만 선택

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName, columnName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 동적 SQL 생성 및 실행
        SET @sql = CONCAT('ALTER TABLE ', tableName, 
                          ' MODIFY COLUMN ', columnName, ' VARCHAR(45) COMMENT \'아이피 주소\';');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END;