<p>출처 :&nbsp;<a href="https://www.letmecompile.com/mysql-utf8-utf8mb4-migration/" target="_blank">letmecompile.com</a></p><p><br></p><h5>1. 환경변수 확인</h5><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;">SHOW&nbsp;<span style="color: #569cd6;">GLOBAL</span>&nbsp;VARIABLES&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;Variable_name&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'character\_set\_%'</span>&nbsp;<span style="color: #569cd6;">OR</span>&nbsp;Variable_name&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'collation%'</span>;</div><p><br></p><p>위 쿼리의 값이 아래와 같이 나와야 함</p><p>단,&nbsp;character_set_filesystem, character_set_system 값은 각각 binary와 utf8이 정상임</p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #569cd6;">character_set_client</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">character_set_connection</span>&nbsp;=&nbsp;utf8mb4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">character_set_database</span>&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">character_set_filesystem</span>&nbsp;=&nbsp;binary&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">character_set_results</span>&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">character_set_server</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">character_set_system</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">collation_connection</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4_unicode_ci&nbsp;</div><div><span style="color: #569cd6;">collation_database</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4_unicode_ci&nbsp;</div><div><span style="color: #569cd6;">collation_server</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;utf8mb4_unicode_ci</div></div><p><br></p><p>요는 캐릭터셋은 utf8mb4, 콜레이션은 utf8mb4_unicode_ci를 사용한다고 보면 됨</p><p><br></p><h5>2. 데이터베이스와 테이블의 캐릭터셋 변경</h5><p>아래 쿼리에서 "데이터베이스 이름" 부분을 변경하고 싶은 이름으로 수정한 뒤에 쿼리 실행 후 생성되는 쿼리를 모두 실행</p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #569cd6;">USE</span>&nbsp;information_schema;</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #dcdcaa;">concat</span>(<span style="color: #ce9178;">'ALTER&nbsp;DATABASE&nbsp;`'</span>,table_schema,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #ce9178;">'`&nbsp;CHARACTER&nbsp;SET&nbsp;=&nbsp;utf8mb4&nbsp;COLLATE&nbsp;=&nbsp;utf8mb4_unicode_ci;'</span>)&nbsp;<span style="color: #569cd6;">as</span>&nbsp;_sql</div><div><span style="color: #569cd6;">FROM</span>&nbsp;<span style="color: #ce9178;">`TABLES`</span></div><div><span style="color: #569cd6;">WHERE</span>&nbsp;table_schema&nbsp;<span style="color: #569cd6;">like</span>&nbsp;<span style="color: #ce9178;">'데이터베이스&nbsp;이름'</span></div><div><span style="color: #569cd6;">GROUP&nbsp;BY</span>&nbsp;table_schema;</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #dcdcaa;">concat</span>(<span style="color: #ce9178;">'ALTER&nbsp;TABLE&nbsp;`'</span>,table_schema,<span style="color: #ce9178;">'`.`'</span>,table_name,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #ce9178;">'`&nbsp;CONVERT&nbsp;TO&nbsp;CHARACTER&nbsp;SET&nbsp;utf8mb4&nbsp;COLLATE&nbsp;utf8mb4_unicode_ci;'</span>)&nbsp;<span style="color: #569cd6;">as</span>&nbsp;_sql</div><div><span style="color: #569cd6;">FROM</span>&nbsp;<span style="color: #ce9178;">`TABLES`</span></div><div><span style="color: #569cd6;">WHERE</span>&nbsp;table_schema&nbsp;<span style="color: #569cd6;">like</span>&nbsp;<span style="color: #ce9178;">'데이터베이스&nbsp;이름'</span></div><div><span style="color: #569cd6;">GROUP&nbsp;BY</span>&nbsp;table_schema,&nbsp;table_name;</div></div><p><br></p><h5>3. routines 재작성</h5><p>기존에 있던 프로시저, 트리거, 함수 등을 백업했다가 다시 생성함으로써 utf8mb4가 지원되도록 한다</p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #6a9955;">::&nbsp;if&nbsp;exists&nbsp;drop&nbsp;and&nbsp;create&nbsp;procedure,&nbsp;trigger,&nbsp;functions&nbsp;etc</span></div><div>mysqldump&nbsp;-u&nbsp;root&nbsp;-p&nbsp;-t&nbsp;-d&nbsp;--routines&nbsp;--triggers&nbsp;[dbname]&nbsp;|&nbsp;gzip&nbsp;&gt;&nbsp;./routines.sql.gz</div><div>mysql&nbsp;-uroot&nbsp;-p&nbsp;--max_allowed_packet=64M&nbsp;-D&nbsp;[dbname]&nbsp;&lt;&nbsp;./routines.sql.gz</div></div><p><br></p><p>위 처럼 처리를 하니 생각보다 제대로 만들어지는 느낌이 아니다.&nbsp;</p><p>나는 heidisql 11버전을 사용중인데 다음과 같이 하면 해당 쿼리를 얻을 수 있다.</p><p><img src='/api/file/11EB23157AB352E8A7EA0242AC110003' style="max-width:100%;height:auto;"><br></p><p>데이터베이스를 SQL로 내보내기를 선택한 후&nbsp;</p><p><img src='/api/file/11EB231583A195D6A7EA0242AC110003' style="max-width:100%;height:auto;"><br></p><p>위와 같은 옵션으로 다시 만드는 쿼리를 추출할 수 있다.</p><p><br></p><p><br></p><p><span style="font-size: 1.25rem;">4. functions</span><br></p><p>3번과 같이 처리했을 때 함수의 반환값이 있는 경우 제대로 처리되지 않는 문제가 있었다.</p><p>반환값에 예를 들어 아래와 같은 경우 변경하여 다시 저장하도록 한다.</p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #6a9955;">#&nbsp;아래와&nbsp;같이&nbsp;utf8을&nbsp;반환하고&nbsp;있으면</span></div><div>varchar(36)&nbsp;CHARSET&nbsp;utf8</div><div><span style="color: #6a9955;">#&nbsp;utf8mb4를&nbsp;반환하도록&nbsp;수정하여&nbsp;저장한다</span></div><div>varchar(36)&nbsp;CHARSET&nbsp;utf8mb4</div></div><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p>

[MySQL & MariaDB] utf8에서 utf8mb4로 마이그레이션 하기