• 목록

MySQL 테이블 문자셋 utf8mb3에서 utf8mb4로 일괄 변경하기

안녕하세요, 오늘은 MySQL에서 자주 발생하는 문자셋 문제와 해결 방법을 공유합니다.

문제 상황

데이터베이스 생성 시 character set을 utf8mb4로 설정했는데, 테이블 확인 시 utf8mb3로 되어 있는 경우가 있습니다. 이는 다음과 같은 원인이 있을 수 있습니다:

• 애플리케이션 또는 설치 스크립트가 기본적으로 utf8(utf8mb3) 문자셋을 사용
• 테이블 생성 시 문자셋을 명시적으로 지정하지 않아 MySQL 기본 설정 적용
• MySQL 서버 설정의 기본 문자셋이 utf8(utf8mb3)로 설정됨

utf8mb4 사용의 장점

utf8mb4는 utf8의 상위 집합으로, 모든 UTF-8 문자를 지원합니다. 특히 다음과 같은 장점이 있습니다:

• 이모지(😊, 🎉 등) 저장 가능
• 다국어 지원 향상
• 특수 문자 처리 개선

모든 테이블 문자셋 일괄 변경 방법

방법 1: 변환 쿼리 생성 후 실행

아래 쿼리를 실행하면 모든 테이블에 대한 변환 쿼리가 생성됩니다:

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') 
FROM information_schema.tables 
WHERE table_schema = '데이터베이스명';

생성된 결과를 복사하여 실행하면 모든 테이블이 변경됩니다.

방법 2: 저장 프로시저 사용

더 편리한 방법으로, 아래 저장 프로시저를 생성하여 실행할 수 있습니다:

DELIMITER $$
CREATE PROCEDURE convert_database_to_utf8mb4()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = '데이터베이스명';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @alter_stmt = CONCAT('ALTER TABLE ', tbl_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;');
        PREPARE stmt FROM @alter_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cur;
END$$
DELIMITER ;

CALL convert_database_to_utf8mb4();
DROP PROCEDURE convert_database_to_utf8mb4;

'데이터베이스명' 부분을 실제 사용 중인 데이터베이스 이름으로 변경해야 합니다.

주의사항

1. 백업 필수: 변환 작업 전 반드시 데이터베이스를 백업하세요.
2. 서비스 중단 고려: 대용량 데이터베이스의 경우 변환 작업이 오래 걸릴 수 있습니다.
3. 애플리케이션 호환성: 애플리케이션 코드가 utf8mb4를 올바르게 처리하는지 확인하세요.
4. 인덱스 크기: utf8mb4는 문자당 최대 4바이트를 사용하므로 인덱스 크기에 영향을 줄 수 있습니다.

MySQL 서버 설정 확인 및 변경

재발 방지를 위해 MySQL 서버 설정(my.cnf 또는 my.ini)에서 기본 문자셋을 utf8mb4로 변경하는 것이 좋습니다:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

서버 설정 변경 후에는 MySQL 서비스를 재시작해야 합니다.

설정 확인 방법

변경 후 설정이 제대로 적용되었는지 확인하려면:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

이 팁이 MySQL 데이터베이스 운영에 도움이 되길 바랍니다!