데이터베이스의 문자 집합 및 콜레이션 업데이트

MySQL   |  PostgreSQL   |  SQL Server

이 페이지에서는 Cloud SQL 데이터베이스의 문자 집합과 콜레이션을 업데이트하는 방법을 설명합니다.

개요

MySQL용 Cloud SQL에서 데이터베이스를 만들 때 데이터베이스의 문자 집합 및 콜레이션 구성을 지정할 수 있습니다. 이러한 설정에 커스텀 값을 지정하지 않으면 데이터베이스에 기본값이 사용됩니다. MySQL에서는 데이터베이스, 테이블, 열을 비롯한 다양한 객체 수준에서 문자 집합과 콜레이션의 커스텀 값을 지정할 수 있습니다. MySQL에서 문자 집합 및 콜레이션 구성을 사용하는 방법에 관한 자세한 내용은 문자 집합, 콜레이션, 유니코드를 참고하세요.

이미 데이터베이스와 데이터베이스의 일부 객체를 만든 후 데이터베이스의 문자 집합이나 콜레이션을 변경하려는 경우에 이 문서의 절차를 따르면 됩니다.

이 절차는 다음 단계로 구성됩니다.

1. 현재 문자 집합 및 콜레이션 값 확인
2. 수행할 업데이트 수준 결정
3. 인스턴스 백업 만들기
4. 저장된 데이터베이스 객체의 재빌드 명령어 생성
5. 데이터베이스(필요한 경우 테이블 및 열) 업데이트
6. 업데이트 검증 및 오류 확인
7. 저장된 데이터베이스 객체 다시 만들기
8. 인스턴스의 또 다른 백업 수행

현재 문자 집합 및 콜레이션 값 확인

데이터베이스 및 데이터베이스 객체의 문자 집합 및 콜레이션에 구성된 기존 값을 확인합니다. 데이터베이스를 확인할 때는 테이블, 열, 저장된 객체의 구성도 확인해야 합니다. 다음 문을 사용하여 데이터베이스와 데이터베이스 객체를 확인할 수 있습니다.

인스턴스 구성 확인

인스턴스(서버) 수준에서 기본 전역 값을 확인하려면 MySQL 셸을 시작하고 다음 명령어를 입력합니다.

mysql> SELECT @@character_set_server,@@collation_server;

출력 예시:

+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4                | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
1 row in set (0.00 sec)

기본 전역 값이 나중에 데이터베이스에 제공할 커스텀 값과 다른지 확인할 수 있습니다.

데이터베이스 구성 확인

데이터베이스 수준에서 기존 문자 집합 및 콜레이션 값을 확인하려면 다음 문을 사용하세요. 각 문을 실행한 후 새 문자 집합 또는 콜레이션 값으로 업데이트해야 하는 모든 데이터베이스 및 데이터베이스 객체의 목록을 만듭니다. 이 목록은 변경할 데이터베이스와 구성 업데이트가 필요한 테이블, 열, 데이터베이스 객체 수에 따라 다릅니다.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name
NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');

예를 들어 다음과 같은 출력이 표시됩니다.

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | test1       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test2       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test3       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test4       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

하지만 데이터베이스를 다음 커스텀 문자 집합 값으로 변경하려고 합니다.

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | test1       | ascii                      | ascii_general_ci       |     NULL | NO                 |
| def          | test2       | latin1                     | latin1_swedish_ci      |     NULL | NO                 |
| def          | test3       | utf16                      | utf16_general_ci       |     NULL | NO                 |
| def          | test4       | ucs2                       | ucs2_general_ci        |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

그런 다음 이 경우 변경하려는 값이 있는 데이터베이스마다 데이터베이스에 있는 각 객체를 업데이트해야 합니다.

테이블 구성 확인

테이블의 기존 값을 확인하려면 다음 문을 사용하세요.

SELECT T.table_name, T.table_schema, CCSA.character_set_name, CCSA.collation_name
FROM information_schema.`tables` T,
  information_schema.`collation_character_set_applicability` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema
NOT IN ('performance_schema', 'mysql', 'sys','information_schema');

출력 예시:

+------------+--------------+--------------------+-------------------+
| TABLE_NAME | TABLE_SCHEMA | CHARACTER_SET_NAME | COLLATION_NAME    |
+------------+--------------+--------------------+-------------------+
| t1         | test1        | ascii              | ascii_general_ci  |
| t2         | test2        | latin1             | latin1_swedish_ci |
| t3         | test3        | utf16              | utf16_general_ci  |
| t4         | test4        | ucs2               | ucs2_general_ci   |
+------------+--------------+--------------------+-------------------+

테이블 열의 구성 확인

열 값을 확인하려면 다음 문을 사용합니다.

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');

출력 예시:

+--------------+------------+-------------+--------------------+------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME   |
+--------------+------------+-------------+--------------------+------------------+
| test1        | t1         | c           | ascii              | ascii_general_ci |
| test1        | t1         | i           | NULL               | NULL             |
| test2        | t2         | j           | NULL               | NULL             |
| test2        | t2         | v           | ascii              | ascii_general_ci |
| test3        | t3         | k           | NULL               | NULL             |
| test3        | t3         | summary     | utf16              | utf16_general_ci |
| test4        | t4         | col         | ucs2               | ucs2_general_ci  |
| test4        | t4         | p           | NULL               | NULL             |
+--------------+------------+-------------+--------------------+------------------+

참고: 문자 집합 및 콜레이션 구성은 문자열 기반 열에만 적용됩니다(예: char, varchar, enum, text, mediumtext, longtext, set). 다른 모든 열 유형의 경우 문자 집합과 콜레이션 구성이 NULL로 표시됩니다.

활성 스키마 또는 데이터베이스 확인

활성 스키마 또는 데이터베이스의 세부정보를 확인하려면 다음 문을 사용하세요.

SELECT TABLE_SCHEMA, TABLE_NAME, CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext', 'set' )
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

출력 예시:

+------------+
| DATABASE() |
+------------+
| test3      |
+------------+
+--------------+------------+------------------+-------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+------------+------------------+-------------+-------------+--------------------+
| test3        | t3         | utf16            | summary     | text        | utf16              |
+--------------+------------+------------------+-------------+-------------+--------------------+

참고: 출력에는 현재 활성 데이터베이스에 해당하는 문자열 기반 열의 문자 집합 및 콜레이션 구성만 포함됩니다.

저장 프러시저 구성 확인

저장 프러시저의 값을 확인하려면 다음 문을 사용하세요.

SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';

DB_NAME를 데이터베이스 이름으로 바꿉니다.

출력 예시:

+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db    | Name | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test2 | p1   | PROCEDURE | root@%  | 2024-08-09 11:47:05 | 2024-08-09 11:47:05 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| test2 | p2   | PROCEDURE | root@%  | 2024-08-09 11:48:36 | 2024-08-09 11:48:36 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

저장 프러시저의 정의를 가져오려면 다음 문을 사용합니다.

SHOW CREATE PROCEDURE DB_NAME.SP_NAME;

다음을 바꿉니다.

• DB_NAME: MySQL 데이터베이스의 이름
• SP_NAME: 저장 프러시저의 이름

저장 프러시저가 의도한 문자 집합 또는 콜레이션 값을 사용하지 않는 경우 삭제하고 새 문자 집합 또는 콜레이션으로 다시 만들어야 할 수 있습니다.

데이터베이스 콜레이션 매핑 확인

이전 예시 출력에서 저장 프러시저는 latin1_swedish_ci를 기본 콜레이션으로 사용하여 생성되고 해당 문자 집합은 latin1입니다. 즉, 프러시저의 특정 파라미터가 커스텀 문자 집합으로 태그되지 않는 한 latin1 문자 집합을 사용하여 모든 처리가 이루어집니다. 데이터베이스 수준 문자 집합과 콜레이션만 변경하면 데이터베이스 수준 업데이트 시 저장 프러시저가 자동으로 업데이트되지 않습니다. 프러시저는 latin1 문자 집합 및 각 콜레이션을 사용하여 계속 작동합니다. 저장 프러시저를 삭제하고 다시 만들면 저장 프러시저가 방금 업데이트한 데이터베이스의 새 문자 집합과 새 콜레이션을 사용하기 시작합니다.

information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 테이블을 쿼리하여 데이터베이스 콜레이션과 문자 집합 간의 매핑을 확인할 수 있습니다.

다음 문을 예시로 사용하세요.

SELECT CHARACTER_SET_NAME
FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
WHERE COLLATION_NAME = (SELECT database_collation FROM information_schema.ROUTINES
                        WHERE ROUTINE_SCHEMA = "test2" and ROUTINE_NAME = "p1");

출력 예시:

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| latin1             |
+--------------------+

트리거 구성 확인

트리거 값을 확인하려면 다음 문을 예시로 사용합니다.

SHOW TRIGGERS FROM DB_NAME;

DB_NAME을 MySQL 데이터베이스 이름으로 바꿉니다.

트리거 정의를 가져오려면 다음 예시 문을 사용합니다.

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

다음을 바꿉니다.

• DB_NAME: MySQL 데이터베이스의 이름
• TRIGGER_NAME: 트리거의 이름

트리거가 의도한 문자 집합이나 콜레이션 값을 사용하지 않는 경우 삭제하고 새 문자 집합 또는 콜레이션으로 다시 만들어야 할 수 있습니다. 트리거가 올바른 데이터베이스 콜레이션 매핑을 사용 중인지 확인합니다. 데이터베이스 콜레이션 확인에 관한 자세한 내용은 데이터베이스 콜레이션 매핑 확인을 참고하세요.

이벤트 구성 확인

이벤트 값을 확인하려면 다음 문을 예시로 사용합니다.

SHOW EVENTS FROM DB_NAME;

DB_NAME를 데이터베이스 이름으로 바꿉니다.

이벤트의 정의를 가져오려면 다음 예시 문을 사용하세요.

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

다음을 바꿉니다.

• DB_NAME: 데이터베이스의 이름
• EVENT_NAME: 이벤트의 이름

이벤트가 의도한 문자 집합 또는 콜레이션 값을 사용하지 않는 경우 삭제하고 새 문자 집합 또는 콜레이션으로 다시 만들어야 할 수 있습니다. 이벤트가 올바른 데이터베이스 콜레이션 매핑을 사용 중인지 확인합니다. 데이터베이스 콜레이션 확인에 관한 자세한 내용은 데이터베이스 콜레이션 매핑 확인을 참고하세요.

주의: 이벤트를 다시 만들면 이벤트가 예약된 차례를 놓칠 수 있습니다.

함수 구성 확인

함수 값을 확인하려면 다음 문을 예시로 사용합니다.

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

DB_NAME를 데이터베이스 이름으로 바꿉니다.

함수 정의를 가져오려면 다음 예시 문을 사용합니다.

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

다음을 바꿉니다.

• DB_NAME: MySQL 데이터베이스의 이름
• FUNCTION_NAME: 함수 이름

함수가 의도한 문자 집합이나 콜레이션 값을 사용하지 않는 경우 삭제하고 새 문자 집합 또는 콜레이션으로 다시 만들어야 할 수 있습니다. 함수가 올바른 데이터베이스 콜레이션 매핑을 사용 중인지 확인합니다. 데이터베이스 콜레이션 확인에 관한 자세한 내용은 데이터베이스 콜레이션 매핑 확인을 참고하세요.

I 문자 집합 또는 콜레이션입니다. 함수가 올바른 데이터베이스 콜레이션 매핑을 사용 중인지 확인합니다. 데이터베이스 콜레이션 확인에 관한 자세한 내용은 데이터베이스 콜레이션 매핑 확인을 참고하세요.

수행할 업데이트 수준 결정

데이터베이스의 문자 집합과 콜레이션 구성을 확인하면 데이터베이스 목록과 업데이트해야 하는 데이터베이스 객체 목록이 표시될 수 있습니다.

수행해야 하는 태스크 수는 구성 변경이 필요한 데이터베이스 객체의 유형에 따라 다릅니다.

• 데이터베이스 수준 업데이트를 수행합니다. MySQL 메타데이터만 업데이트하면 됩니다.
• 데이터베이스 및 테이블 수준 업데이트를 수행합니다. MySQL 메타데이터만 업데이트하면 됩니다.
• 데이터베이스, 테이블, 열 수준 업데이트를 수행합니다. 메타데이터를 업데이트하고 테이블과 데이터베이스 객체를 다시 빌드해야 합니다. 이 업데이트의 경우 시간이 가장 오래 걸릴 수 있습니다. 그러나 제공된 예시 스크립트 중 하나를 사용하여 특정 데이터베이스의 모든 테이블과 열을 업데이트할 수 있습니다.

인스턴스 백업 수행

중요: 이 단계를 건너뛰지 마세요.

업데이트를 수행하기 전에 인스턴스를 백업합니다. 자세한 내용은 주문형 백업 만들기를 참조하세요. 인스턴스에서 실행되는 DDL 작업 없이 백업을 수행하는 것이 좋습니다.

데이터베이스에 저장된 데이터베이스 객체의 재빌드 명령어 생성

데이터베이스에 저장된 데이터베이스 객체를 삭제하고 새 문자 집합 및 콜레이션으로 다시 만들어야 하는 경우 지금 mysqldump 도구를 사용하여 명령어를 생성합니다. 데이터베이스 및 테이블 열을 업데이트한 후 생성된 mysqldump 파일을 사용하여 데이터베이스에 저장된 데이터베이스 객체를 다시 빌드합니다. 이 명령어는 모든 저장 프러시저, 함수, 트리거, 이벤트에 대한 문을 생성합니다.

나중에 특정 데이터베이스의 모든 저장 프러시저, 함수, 이벤트를 다시 만들기 위해(저장된 데이터베이스 객체를 다시 만들 때) 출력을 사용할 수 있도록 다음 명령어를 실행합니다.

$ mysqldump -uDBAuser -p -h IP_ADDRESS \
   -P 3306 DB_NAME --no-data --no-create-db \
   --no-create-info --routines --triggers --events \
   --set-gtid-purged=OFF --events > dump_objects.sql

다음을 바꿉니다.

• IP_ADDRESS: MySQL용 Cloud SQL 인스턴스의 IP 주소
• DB_NAME: MySQL 데이터베이스의 이름

데이터베이스(필요한 경우 테이블 및 열) 업데이트

이 단계에서는 데이터베이스의 문자 집합 및 콜레이션 값을 업데이트합니다. 또한 테이블과 열을 다시 빌드해야 할 수도 있습니다.

데이터베이스 수준 업데이트 수행

데이터베이스 수준 업데이트만 수행하는 경우 다음 문을 실행하여 데이터베이스를 업데이트합니다.

ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

다음을 바꿉니다.

• DB_NAME: 업데이트할 데이터베이스의 이름
• NEW_CHARSET: 데이터베이스에 적용할 새 문자 집합
• NEW_COLLATION: 데이터베이스에 적용할 새 콜레이션

데이터베이스 및 테이블 수준 업데이트 수행

데이터베이스 및 테이블 수준 업데이트를 수행하는 경우 다음을 실행합니다.

1. 다음 문을 실행하여 데이터베이스를 업데이트합니다.

   ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   다음을 바꿉니다.

   • DB_NAME: 업데이트할 데이터베이스의 이름
   • NEW_CHARSET: 데이터베이스에 적용할 새 문자 집합
   • NEW_COLLATION: 데이터베이스에 적용할 새 콜레이션

2. 업데이트하려는 테이블마다 다음 문을 실행합니다.

   ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   다음을 바꿉니다.

   • TABLE_NAME: 업데이트할 테이블의 이름
   • NEW_CHARSET: 테이블에 적용할 새 문자 집합
   • NEW_COLLATION: 테이블에 적용할 새 콜레이션

데이터베이스, 테이블, 열 수준 업데이트 수행

이 수준에서는 새로운 문자 집합 및 콜레이션 설정을 준수하도록 기존 데이터를 다시 인코딩해야 합니다. 데이터베이스, 테이블, 열 수준 업데이트를 수행하는 경우 다음을 실행합니다.

1. 테이블 열 업데이트를 진행하기 전에 다음 고려사항을 검토하세요.

   • ALTER TABLE 문자 집합 및 콜레이션 변환으로 인해 테이블이 다시 빌드되고 테이블이 잠깁니다. 그로 인해 테이블에 액세스하려는 활성 쿼리가 차단됩니다.
   • ALTER TABLE 문자 집합과 콜레이션 변환은 메모리에 전체 테이블을 가져오기 때문에 시간이 걸립니다. 이 작업을 수행하면 버퍼 풀 페이지가 업데이트된 테이블에 사용되므로 동시에 실행되는 워크로드의 지연 시간이 증가할 수 있습니다.
   • 여러 테이블에 액세스하는 쿼리가 있고 일부 테이블이 아직 업데이트되지 않은 상태에서 일부 테이블만 업데이트되는 경우 이 트랜잭션 시간 동안 쿼리에서 일관되지 않은 결과를 생성할 수 있습니다.
   • ALTER TABLE 문자 집합 및 콜레이션 변환을 실행하는 프로세스에서는 섀도 테이블이 생성되므로 그에 따라 디스크 사용량을 계획하세요.
   • ALTER TABLE 문자 집합 및 콜레이션 변환으로 인해 테이블의 색인이 다시 빌드됩니다.
   • 복제본 인스턴스는 별도로 업데이트할 필요가 없습니다. ALTER, DROP, CREATE 명령어는 자동으로 복제됩니다. 그러나 DDL 명령어를 실행하면 작업 중에 복제 지연 시간이 늘어날 수 있습니다.
   • 이러한 업데이트를 적용하기 전에 가져온 백업에서 데이터베이스를 복원하는 경우 업데이트를 다시 적용해야 합니다.

2. 테이블에서 새 문자 집합과 호환되지 않을 수 있는 데이터 튜플이 있는지 확인합니다. 비호환성이 있는 경우 문자 집합을 업데이트하기 전에 수정해야 합니다. 그렇지 않으면 변환 프로세스 중에 오류가 발생합니다. 다음 SELECT CONVERT 문을 사용하여 새 문자 집합으로 데이터를 검증할 수 있습니다.

   SELECT COLUMN_NAME,CONVERT(COLUMN_NAME USING NEW_CHARSET)
   FROM TABLE_NAME
   WHERE COLUMN_NAME != CONVERT(COLUMN_NAME USING NEW_CHARSET);

   참고: 데이터 세트가 매우 큰 경우 SELECT CONVERT 쿼리를 실행하는 데 시간이 더 걸릴 수 있습니다. 예를 들어 char 열 c가 있는 테이블 t가 있는 경우 다음 SELECT CONVERT 문을 실행하여 ASCII 문자 집합으로 변환하기 전에 데이터 튜플을 확인할 수 있습니다.

   SELECT c,CONVERT(c USING 'ascii')
   FROM t WHERE c != CONVERT(c USING ascii);
   

   출력 예시:

   +------+--------------------------+
   | c    | convert(c using 'ascii') |
   +------+--------------------------+
   | é    | ?                        |
   | é    | ?                        |
   | £    | ?                        |
   +------+--------------------------+
   

   이 예시에서는 출력이 이러한 튜플 3개를 반환하므로 일치하지 않는 값 3개를 수정해야 합니다. 다음 단계로 넘어가기 전에 쿼리의 출력을 검사하고 쿼리가 변환이 필요한 모든 열에 대해 0 튜플을 반환할 때까지 데이터를 수정합니다. 동일한 SELECT 쿼리에서 여러 열을 결합할 수도 있습니다.

3. 열 수준에서 테이블의 문자 집합 및 콜레이션을 업데이트하는 옵션을 선택합니다.

4. 매우 구체적인 테이블 열의 문자 집합을 업데이트하려면 선택한 열만 다시 인코딩하면 되는 열 수준 탭의 단계를 따릅니다. 그렇지 않으면 테이블 수준 탭의 단계를 따라 전체 테이블을 다시 인코딩합니다.

   테이블 수준

   팁: 테이블이 많은 경우 스크립트를 사용하여 여러 테이블을 업데이트할 수도 있습니다.

   1. 다음 문을 실행하여 데이터베이스를 업데이트합니다.

      ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      다음을 바꿉니다.

      • DB_NAME: 업데이트할 데이터베이스의 이름
      • NEW_CHARSET: 데이터베이스에 적용할 새 문자 집합
      • NEW_COLLATION: 데이터베이스에 적용할 새 콜레이션

   2. 영향을 받는 각 테이블에 대해 다음 문을 실행하여 테이블을 업데이트합니다.

      ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      다음을 바꿉니다.

      • TABLE_NAME: 업데이트할 테이블의 이름
      • NEW_CHARSET: 테이블에 적용할 새 문자 집합
      • NEW_COLLATION: 테이블에 적용할 새 콜레이션

   3. 테이블의 일부인 모든 열이 새 문자 집합 및 콜레이션으로 업데이트되도록 테이블 수준에서 업데이트합니다. 이 옵션은 테이블을 완전히 다시 빌드합니다. 이 옵션을 사용하려면 다음 문을 실행합니다.

      ALTER TABLE DB_NAME.TABLE_NAME
      CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      다음을 바꿉니다.

      • TABLE_NAME: 업데이트할 테이블의 이름
      • NEW_CHARSET: 테이블에 적용할 새 문자 집합
      • NEW_COLLATION: 테이블에 적용할 새 콜레이션
      참고: 이 옵션을 사용하면 MySQL이 새 문자 집합에 데이터를 맞출 수 없는 경우 MySQL에서 데이터 유형을 변경할 수 있습니다. 이러한 변경은 거의 발생하지 않지만, 변환 업데이트 후에 테이블을 확인하는 것이 좋습니다.

   열 수준

   열 수준에서 업데이트합니다. 이 옵션도 테이블을 완전히 다시 빌드합니다. 여러 열이 업데이트되는 경우 이 옵션으로 인해 여러 차례 다시 빌드될 수 있습니다.

   도움말: 열이 많은 경우 스크립트를 사용하여 테이블의 여러 열을 업데이트할 수도 있습니다.

   이 옵션을 사용하려면 다음 문을 실행합니다.

   1. 다음 문을 실행하여 데이터베이스를 업데이트합니다.

   ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   다음을 바꿉니다.

   • DB_NAME: 업데이트할 데이터베이스의 이름
   • NEW_CHARSET: 데이터베이스에 적용할 새 문자 집합
   • NEW_COLLATION: 데이터베이스에 적용할 새 콜레이션
   2. 영향을 받는 각 테이블에 대해 다음 문을 실행하여 테이블을 업데이트합니다.

   ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
    

   다음을 바꿉니다.

   • TABLE_NAME: 업데이트할 테이블의 이름
   • NEW_CHARSET: 테이블에 적용할 새 문자 집합
   • NEW_COLLATION: 테이블에 적용할 새 콜레이션
   3. 다음 문을 실행하여 열 수준에서 업데이트합니다.

   ALTER TABLE DB_NAME.TABLE_NAME
   MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   참고: 정확한 열 정의를 가져와야 합니다. 정확한 열 정의를 가져오려면 SHOW CREATE TABLE TABLE_NAME을 사용하세요.

   다음을 바꿉니다.

   • DB_NAME: 업데이트할 데이터베이스의 이름
   • TABLE_NAME: 업데이트할 테이블의 이름
   • COLUMN_DEFINITION: 제약조건을 포함하는 정확한 열 정의를 지정합니다. 열 정의에는 이전 문자 집합과 콜레이션 값이 포함될 수 있습니다. 열 정의를 새 값으로 업데이트해야 합니다.
   • NEW_CHARSET: 테이블 열에 적용할 새 문자 집합
   • NEW_COLLATION: 테이블 열에 적용할 새 콜레이션

   문자 집합 또는 콜레이션 업데이트가 필요한 테이블에 있는 모든 문자열 열에 이 문을 반복합니다.

업데이트 검증 및 오류 확인

ALTER TABLE 작업 중에 테이블의 기존 데이터가 새 문자 집합과 호환되지 않음을 나타내는 오류가 있는지 확인합니다. 예를 들면 다음과 같습니다.

ERROR 1366 (HY000): Incorrect string value: '\xC3\xA9' for column 'c' at row 1

데이터 호환성 오류가 발생하면 ALTER TABLE 문을 다시 실행하여 데이터를 다시 인코딩하기 전에 테이블에 데이터 호환성 오류가 있는지 다시 확인하고 오류를 수정하는 것이 좋습니다.

또한 ALTER TABLE 작업 중에 테이블(외래 키 테이블 및 참조된 테이블)에서 열이 다시 인코딩되는 동안 외래 키 확인에 실패할 수 있습니다. 이 경우 외래 키 확인(SET FOREIGN_KEY_CHECKS=0;)을 사용 중지하고 업데이트를 다시 실행하면 됩니다.

저장된 데이터베이스 객체 다시 만들기

데이터베이스를 업데이트한 후 생성된 mysqldump 파일을 사용하여 문자 집합 및 콜레이션 값으로 저장된 데이터베이스 객체를 다시 만들 수 있습니다.

저장된 데이터베이스 객체를 다시 만들려면 다음 안내를 따르세요.

1. 저장된 데이터베이스 객체가 데이터베이스에 있는지 확인합니다.
2. 삭제하고 다시 만드는 동안 저장된 데이터베이스 객체가 있을 것으로 예상하는 병렬 부하가 없어야 합니다.

3. mysqldump 도구로 생성한 mysqldump 파일에는 트리거 삭제를 위한 문이 포함되지 않습니다. 트리거를 삭제해야 하는 경우 MySQL 터미널을 열어 트리거의 DROP 문을 생성합니다.

   mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') AS
   drop_trigger_statements FROM information_schema.TRIGGERS
   WHERE TRIGGER_SCHEMA = DB_NAME;

4. 출력을 복사합니다.

5. MySQL 셸 또는 스크립트에서 데이터베이스의 트리거를 삭제하기 위한 문을 실행합니다.

6. 트리거를 삭제한 후 mysqldump 도구로 생성한 덤프 파일을 적용합니다. 덤프 파일을 적용하고 객체를 다시 만들려면 다음을 입력합니다.

   $ mysql -uDBAuser -p -h IP_ADDRESS \
     -P 3306 DB_NAME < dump_objects.sql

   다음을 바꿉니다.

   • IP_ADDRESS: MySQL용 Cloud SQL 인스턴스의 IP 주소
   • DB_NAME: 데이터베이스의 이름

인스턴스의 또 다른 백업 수행

업데이트를 완료한 후 인스턴스를 다시 백업합니다. 자세한 내용은 주문형 백업 만들기를 참조하세요.

예시 스크립트

데이터베이스, 테이블, 열을 업데이트해야 하는 경우 이 섹션에서 제공하는 데이터를 다시 인코딩하는 데 도움이 되는 스크립트 예시를 사용합니다.

팁: 프로덕션에서 데이터를 다시 인코딩하기 전에 클론 인스턴스에서 데이터 변환을 테스트해 볼 수 있습니다.

여러 테이블을 업데이트하는 스크립트

다음 스크립트는 지정된 데이터베이스의 모든 테이블에 대한 문을 생성합니다. 이 문을 사용하면 새 문자 집합 및 콜레이션 설정을 준수하도록 기존 데이터를 다시 인코딩할 수 있습니다.

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;  ",
"ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION; ")
AS alter_sql FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';

다음을 바꿉니다.

• DB_NAME: 업데이트할 데이터베이스의 이름
• NEW_CHARSET: 테이블에 적용할 새 문자 집합
• NEW_COLLATION: 테이블에 적용할 새 콜레이션

출력 예시:

+--------------------------------------------------------------------------------------------+
|alter_sql
+--------------------------------------------------------------------------------------------+
| ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;
  ALTER TABLE test1.t1 CONVERT TO CHARACTER SET <new-charset> COLLATE <new-collation>;       |
+--------------------------------------------------------------------------------------------+

테이블의 여러 열을 업데이트하는 스크립트

다음 스크립트는 지정된 테이블의 모든 열에 관한 문을 생성합니다. 이 문을 사용하면 새 문자 집합 및 콜레이션 설정을 준수하도록 기존 데이터를 다시 인코딩할 수 있습니다.

1. 데이터베이스의 모든 테이블에 대해 ALTER TABLE 문을 생성합니다.

   SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;  ")
   AS alter_table_statements
   FROM information_schema.TABLES
   WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';

   다음을 바꿉니다.

   • DB_NAME: 업데이트할 데이터베이스의 이름
   • NEW_CHARSET: 테이블에 적용할 새 문자 집합
   • NEW_COLLATION: 테이블에 적용할 새 콜레이션

   출력 예시:

   +------------------------------------------------------------------------------+
   | alter_table_statements                                                       |
   +------------------------------------------------------------------------------+
   | ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;    |
   +------------------------------------------------------------------------------+
   

2. 모든 테이블에 대해 ALTER TABLE MODIFY 명령어를 생성합니다.

   SELECT CONCAT( "ALTER TABLE ", table_name, " MODIFY ", column_name, 'COLUMN_DEFINITION' , " CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION ")
   AS alter_table_column_statements FROM information_schema.columns
   WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'
   AND data_type IN ('char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext','set','enum');

   다음을 바꿉니다.

   • DB_NAME: 업데이트할 데이터베이스의 이름
   • TABLE_NAME: 업데이트할 테이블의 이름
   • COLUMN_DEFINITION: 각 쿼리에서 결과를 가져온 후 각 열의 열 정의로 바꿉니다(열 정의는 열마다 다를 수 있음).
   • NEW_CHARSET: 테이블 열에 적용할 새 문자 집합
   • NEW_COLLATION: 테이블 열에 적용할 새 콜레이션

   출력 예시:

   +-------------------------------------------------------------------------------------------------------------------------------------+
   |alter_table_column_statements                                                                                                        |
   +-------------------------------------------------------------------------------------------------------------------------------------+
   | ALTER TABLE t1 MODIFY c <column-definition-replaced-after-obtaining-result-set> CHARACTER SET <new-charset> COLLATE <new-collation>  |
   +-------------------------------------------------------------------------------------------------------------------------------------+
   

다음 단계

• Cloud SQL 인스턴스 모니터링
• 인스턴스 로그 보기