출처: https://mathiasbynens.be/notes/mysql-utf8mb4

MySQL 데이터베이스에서 전체 유니 코드를 지원하는 방법
2012 년 7 월 30 일 게시, MySQL, 보안, 유니 코드 태그

얼터너티브 타이틀 : U + 1F4A9 PLE OF POO (💩)를 올바르게 저장하는 작업.

데이터베이스에 MySQL의 utf8 charset을 사용하고 있습니까? 이 글에서 utf8mb4로 전환해야하는 이유와 그 방법을 설명합니다.

UTF-8
UTF-8 인코딩은 U + 000000에서 U + 10FFFF까지의 유니 코드 문자 세트의 모든 심볼을 나타낼 수 있습니다. 그것은 1,114,112 개의 가능한 심볼입니다. (모든 유니 코드 코드 포인트에 아직 문자가 할당되지는 않았지만 UTF-8이 인코딩 할 수 없게됩니다.)

UTF-8은 가변 폭 인코딩입니다. 1 ~ 4 개의 8 비트 바이트를 사용하여 각 심볼을 인코딩합니다. 숫자 코드 포인트 값이 낮은 기호는 더 적은 바이트를 사용하여 인코딩됩니다. UTF-8은 U + 000000에서 U + 00FFFF 범위의 ASCII 문자 및 기타 BMP 기호가 사용되는 일반적인 경우에 최적화되어 있지만 아스트랄 기호 (U + 010000 ~ U + 10FFFF)를 저장할 수 있습니다.

MySQL의 utf8
오랜 시간 동안 위에서 설명한 UTF-8 인코딩에 매핑되었다고 가정하고 MySQL의 utf8 charset을 데이터베이스, 테이블 및 열용으로 사용하고있었습니다. utf8을 사용하면 데이터베이스에 원하는 기호를 저장할 수 있습니다. 또는 그렇게 생각했습니다.

자바 스크립트의 내부 문자 인코딩에 대해 쓰는 동안이 사이트 뒤에있는 MySQL 데이터베이스에 U + 1D306 TETRAGRAM FOR CENTER (𝌆) 기호를 삽입 할 수있는 방법이 없다는 것을 알았습니다. 업데이트하려고 시도했던 열은 utf8_unicode_ci 데이터 정렬을 가지며 연결 charset은 utf8로 설정되었습니다.

mysql> SET NAMES utf8; # 연결 문자셋이`utf8`으로 설정되었음을 강조하기 위해서입니다.
쿼리 OK, 영향을받은 행 0 개 (0.00 초)

mysql> UPDATE database_name.table_name SET column_name = 'foo𝌆bar' WHERE id = 9001;
쿼리 OK, 영향을받은 행 1 개, 경고 1 개 (0.00 초)
일치하는 행 : 1 변경됨 : 1 경고 : 1

mysql> SELECT column_name FROM database_name.table_name WHERE id = 9001;
+-------------+
|column_name  |
+-------------+
| foo         |
+-------------+
1 행 세트 (0.00 초)
내용은 첫 번째 아스트랄 유니 코드 심볼에서 잘 렸습니다.이 경우 𝌆 - 그래서 foo𝌆bar를 실제로 삽입하면 foo가 실제로 삽입되어 데이터 손실이 발생합니다 (보안 문제가 발생할 가능성이 있음, 아래 참조). MySQL도 경고 메시지를 반환했습니다.

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| 레벨    | 코드 | 메시지                                                                       |
+---------+------+------------------------------------------------------------------------------+
| 경고    | 1366 | 잘못된 문자열 값 : 컬럼 'column_name'의 '\ xF0 \ x9D \ x8C \ x86'            |
+---------+------+------------------------------------------------------------------------------+
1 행 세트 (0.00 초)
MySQL의 utf8 charset은 적절한 UTF-8 인코딩을 부분적으로 만 구현합니다. 1에서 3 바이트로 구성된 UTF-8 인코딩 심볼 만 저장할 수 있습니다. 4 바이트를 차지하는 인코딩 된 기호는 지원되지 않습니다.

아스트랄 심볼 (코드 포인트의 범위는 U + 010000에서 U + 10FFFF까지)은 UTF-8로 4 바이트로 구성되므로 MySQL의 utf8 구현을 사용하여 저장할 수 없습니다.

이것은 𝌆 문자에 영향을주는 것이 아니라 U + 01F4A9 PILE OF POO (💩)와 같은 중요한 기호에도 영향을 미칩니다. 총 1,048,575 개의 가능한 코드 포인트를 사용할 수 있습니다. 실제로, MySQL의 utf8은 가능한 모든 유니 코드 코드 포인트 중 5.88 % ((0x00FFFF + 1) / (0x10FFFF + 1)) 만 저장할 수 있습니다. 적절한 UTF-8은 모든 유니 코드 코드 포인트의 100 %를 인코딩 할 수 있습니다.

위에 표시된 것처럼이 동작으로 인해 데이터가 손실 될 수는 있지만 보안 취약점이 발생할 수 있습니다. 다음은 몇 가지 예입니다.이 모든 사례는이 글을 게시 한 후에 발견되었습니다.

WordPress의 PHP 객체 주입 취약점 <3.6.1, 특정 WordPress 플러그인과 함께 원격 코드 실행
Phabricator의 이메일 인증 우회
WordPress 4.1.2에 저장된 XSS
Joomla!에서 원격 명령 실행! CMS
TL, DR MySQL의 utf8 인코딩은 적절한 UTF-8 인코딩과 다르기 때문에 어색하게 명명됩니다. 데이터 손실이나 보안 취약성을 초래할 수있는 완전한 유니 코드 지원을 제공하지 않습니다.

MySQL의 utf8mb4
다행히도 MySQL 5.5.3 (2010 년 초에 출시)에서는 utf8mb4라는 새로운 인코딩을 도입하여 적절한 UTF-8로 매핑하므로 별표를 포함하여 유니 코드를 완벽하게 지원합니다.

MySQL의 utf8에서 utf8mb4로 전환
1 단계 : 백업 만들기
업그레이드 할 서버의 모든 데이터베이스 백업을 만듭니다. 안전 제일!

2 단계 : MySQL 서버 업그레이드
MySQL 서버를 v5.5.3 +로 업그레이드하거나 서버 관리자에게 문의하십시오.

3 단계 : 데이터베이스, 테이블 및 열 수정
utf8 대신 utf8mb4를 사용하도록 데이터베이스, 테이블 및 열의 문자 집합과 데이터 정렬 속성을 변경합니다.

# 각 데이터베이스에 대해 :
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 각 테이블에 대해 :
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 각 열에 대해 :
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (맹목적으로 복사해서 붙여 넣지 마라!) 정확한 문장은 컬럼 타입, 최대 길이 및 다른 속성에 의존한다. 위의 라인은 단지 'VARCHAR` 컬럼의 예제이다.
utf8mb4는 utf8과 완벽하게 하위 호환이 가능하기 때문에 모기 바인 또는 다른 형태의 데이터 손실이 발생하지 않아야합니다. (하지만 백업이 있습니다.)

4 단계 : 열 및 인덱스 키의 최대 길이 확인
이것은 아마도 전체 업그레이드 프로세스에서 가장 지루한 부분 일 것입니다.

utf8에서 utf8mb4로 변환 할 때 열 또는 인덱스 키의 최대 길이는 바이트로 변경되지 않습니다. 따라서 문자의 최대 길이가 이제 3 바이트가 아닌 4 바이트이기 때문에 문자 측면에서 더 작습니다.

예를 들어, TINYTEXT 컬럼은 255 바이트까지 보유 할 수 있으며, 이는 85 개의 3 바이트 또는 63 개의 4 바이트 문자와 연관됩니다. utf8을 사용하지만 63자를 초과 할 수 있어야하는 TINYTEXT 열이 있다고 가정 해 보겠습니다. 이 요구 사항을 감안할 때 데이터 유형을 TEXT와 같이 더 긴 유형으로 변경하지 않으면이 열을 utf8mb4로 변환 할 수 없습니다. 4 바이트 문자로 채우려는 경우에만 63 자 이상을 입력하십시오.

색인 키도 마찬가지입니다. InnoDB 스토리지 엔진은 최대 인덱스 길이가 767 바이트이므로 utf8 또는 utf8mb4 열의 경우 최대 255 또는 191 자까지 색인을 생성 할 수 있습니다. 현재 utf8 열에 191 자보다 긴 색인이있는 경우 utf8mb4를 사용할 때 더 적은 수의 색인을 작성해야합니다. (이 때문에 인덱스 된 VARCHAR (255) 열을 VARCHAR (191)로 변경해야했습니다.

MySQL 5.5 Reference Manual의 Section 10.1.11에는 이에 관한 더 많은 정보가있다.

5 단계 : 연결, 클라이언트 및 서버 문자 집합 수정
응용 프로그램 코드에서 연결 문자 집합을 utf8mb4로 설정합니다. 이것은 단순히 SET NAMES utf8의 변형을 SET NAMES utf8mb4로 대체하여 수행 할 수 있습니다. 이전 SET NAMES 문에서 데이터 정렬을 지정한 경우 데이터 정렬도 변경해야합니다. SET NAMES utf8 COLLATE utf8_unicode_ci는 다음과 같이 설정됩니다. SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci.

클라이언트와 서버 문자 세트도 설정해야합니다. MySQL 구성 파일 (/etc/my.cnf)에 다음 내용이 있습니다.

[고객]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 줄 세트 (0.00 초)
보시다시피 모든 관련 옵션은 utf8mb4로 설정됩니다. 단, character_set_filesystem은 파일 이름에 멀티 바이트 UTF-8 인코딩 문자를 지원하는 파일 시스템이 아니면 binary이어야하며 character_set_system은 항상 utf8입니다. 재정의 할 수 없습니다.

참고 : 기본 문자 집합과 데이터 정렬은 다른 여러 수준에서도 구성 할 수 있습니다.

6 단계 : 모든 테이블 복구 및 최적화
위에서 설명한 MySQL 서버를 업그레이드하고 필요한 변경을 한 후에는 모든 데이터베이스와 테이블을 복구하고 최적화하십시오. 나는 (모든 것이 처음에는 잘 작동하는 것처럼 보였기 때문에) 업그레이드 후에 바로 이것을하지는 않았다 (UPDATE 문이 아무 효과가없는 이상한 버그에 부딪쳤다. 오류가 발생했습니다.

복구 및 최적화하려는 각 테이블에 대해 다음 MySQL 쿼리를 실행할 수 있습니다.

# 각 테이블에 대해
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;
다행히 명령 행의 mysqlcheck 유틸리티를 사용하면 한 번에 쉽게 수행 할 수 있습니다.

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
이렇게하면 루트 사용자의 암호를 묻는 메시지가 나타나고 모든 데이터베이스의 모든 테이블이 복구되고 최적화됩니다.

개요
MySQL에서는 utf8을 사용하지 마십시오. 항상 utf8mb4를 대신 사용하십시오. 데이터베이스와 코드를 업데이트하는 데는 시간이 걸릴 수 있지만 그럴 가치가 있습니다. 왜 당신은 당신의 데이터베이스에서 사용될 수있는 심볼들의 집합을 임의적으로 제한하겠습니까? 사용자가 메모 나 메시지의 일부로 아스트랄 기호를 입력 할 때마다 또는 데이터베이스에 저장 한 내용을 모두 잃어버린 이유는 무엇입니까? 어디서나 유니 코드를 완벽하게 지원하지 않을 이유가 없습니다. 올바른 일을하고 utf8mb4를 사용하십시오. 🍻