MySQL에서 잘못 인코딩된 데이터를 UTF-8로 변환하는 방법

MySQL에서 latin1 등으로 잘못 저장되어 깨져 보이는 문자열 데이터를 UTF-8로 복구하려면 CONVERT() 함수와 BINARY 키워드를 함께 사용해야 합니다. 이 방식은 원본 바이트를 그대로 유지한 상태에서 문자셋만 재해석하기 때문에, 이미 깨진 데이터를 안전하게 되돌릴 수 있습니다.

기본 문법

SELECT CONVERT(BINARY CONVERT(컬럼명 USING latin1) USING utf8) AS 별칭 FROM 테이블명;

예제 테이블 생성

먼저 예제에 사용할 테이블을 만들어 보겠습니다. 아래 쿼리는 Id와 Name 컬럼을 가진 테이블을 생성합니다.

mysql> create table UtfDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Name varchar(15),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.76 sec)

데이터 삽입

INSERT 명령어를 사용하여 아포스트로피(’)가 포함된 레코드를 몇 개 삽입합니다. 이때 아포스트로피가 latin1 인코딩으로 잘못 저장되어 '’' 형태로 깨지게 됩니다.

mysql> insert into UtfDemo(Name) values('Obama’s');
Query OK, 1 row affected (0.28 sec)
mysql> insert into UtfDemo(Name) values('John’s');
Query OK, 1 row affected (0.23 sec)
mysql> insert into UtfDemo(Name) values('Carol’s');
Query OK, 1 row affected (0.15 sec)
mysql> insert into UtfDemo(Name) values('Sam’s');
Query OK, 1 row affected (0.19 sec)

저장된 데이터 확인

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select *from UtfDemo;

실행 결과는 다음과 같습니다. Name 컬럼의 값이 'Obama’s'처럼 깨져 있는 것을 확인할 수 있습니다.

+----+----------------+
| Id | Name           |
+----+----------------+
|  1 | Obama’s       |
|  2 | John’s        |
|  3 | Carol’s       |
|  4 | Sam’s         |
+----+----------------+
4 rows in set (0.00 sec)

UTF-8로 변환하는 쿼리

이제 CONVERT() 함수와 BINARY 키워드를 사용하여 잘못 인코딩된 데이터를 UTF-8로 변환합니다. 먼저 컬럼 값을 latin1로 해석한 뒤 BINARY로 감싸고, 다시 UTF-8로 변환하는 순서로 동작합니다.

mysql> select CONVERT(binary CONVERT(Name using latin1) using utf8) as ListOfName from UtfDemo;

실행 결과는 다음과 같습니다. 깨져 있던 아포스트로피가 정상적인 'Obama's' 형태로 복원된 것을 확인할 수 있습니다.

+------------+
| ListOfName |
+------------+
| Obama's    |
| John's     |
| Carol's    |
| Sam's      |
+------------+
4 rows in set, 1 warning (0.00 sec)

정리

잘못 인코딩된 MySQL 데이터를 복구할 때는 CONVERT(BINARY CONVERT(컬럼 USING latin1) USING utf8) 패턴을 활용하면 됩니다. 핵심은 중간에 BINARY 타입으로 한 번 변환하여 원본 바이트를 보존한 후, 최종적으로 UTF-8 문자셋으로 재해석하는 것입니다. 이 방법은 UPDATE 문과 결합하여 실제 테이블의 데이터를 영구적으로 수정하는 데에도 활용할 수 있습니다.