Categories

Tags


기본 CharacterSet 의 변경 (latin1 > utf8mb4)

버젼별 비교

  • 5.7

    mysql 5.7 [localhost] {msandbox} ((none)) > show session variables like '%char%';
    +--------------------------+----------------------------------------+
    | Variable_name            | Value                                  |
    +--------------------------+----------------------------------------+
    | character_set_client     | utf8                                   |
    | character_set_connection | utf8                                   |
    | character_set_database   | latin1                                 |
    | character_set_filesystem | binary                                 |
    | character_set_results    | utf8                                   |
    | character_set_server     | latin1                                 |
    | character_set_system     | utf8                                   |
    | character_sets_dir       | /MySQL/binaries/5.7.24/share/charsets/ |
    +--------------------------+----------------------------------------+
    8 rows in set (0.01 sec)
    
    mysql 5.7 [localhost] {msandbox} ((none)) > select * from information_schema.COLLATIONS where CHARACTER_SET_NAME='latin1' and IS_DEFAULT='Yes';
    +-------------------+--------------------+----+------------+-------------+---------+
    | COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
    +-------------------+--------------------+----+------------+-------------+---------+
    | latin1_swedish_ci | latin1             |  8 | Yes        | Yes         |       1 |
    +-------------------+--------------------+----+------------+-------------+---------+
    1 row in set (0.00 sec)
    
  • 8.0

    mysql 8.0 [localhost] {msandbox} ((none)) >  show session variables like '%char%';
    +--------------------------+----------------------------------------+
    | 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                                   |
    | character_sets_dir       | /MySQL/binaries/8.0.13/share/charsets/ |
    +--------------------------+----------------------------------------+
    8 rows in set (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} ((none)) > select * from information_schema.COLLATIONS where CHARACTER_SET_NAME='utf8mb4' and IS_DEFAULT='Yes';
    +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    | COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
    +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    | utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
    +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    1 row in set (0.01 sec)
    
    
드디어 기본 character set 과 collation 이 지긋지긋한 latin1 (latin1_swedish_ci) 에서 utf8mb4 (utf8mb4_0900_ai_ci) 로 변경이 되었고, 모든 작업이 utf8mb4 character set을 기본으로 동작하게 되었다. 기존에는 my.cnf 에 character-set-server = [utf8 euckr utf8mb4] 를 넣어 해당 mysqld의 기본 character set 을 지정해야만 했었다.

기본 collation의 변경

기존의 collation 에서 ‘ci’ (case insensitive (대소문자 구분하지 않음)) 와 ‘cs 혹은 bin’ (case sensitive(대소문자 구분)) 만을 지원하였다면 추가적으로 Accent 에 관해서도 지원하게 되었다. 또한 PAD의 default 값이 변경되어, 사용 혹은 Migration후 주의깊게 사용되어져야 한다.

MySQL 8.0의 default collation은 utf8mb4_0900_ai_ci 이고, 이는 다음과 같은 내용을 포함한다

   * collation의 character set은 utf8mb4 이다. (utf8mb4)
   * Unicod 9.0의 문자를 표현한다. (0900)
   * Accent Insensitive Mode로 동작한다. (ai)
   * Case Insensitive Mode로 동작한다. (ci)

ai, as 의 생성

  • ai : Accent Insensitive

  • ac : Accent Sensitive

    mysql 8.0 [localhost] {msandbox} ((none)) > SET NAMES utf8mb4;
      Query OK, 0 rows affected (0.00 sec)
    
      mysql 8.0 [localhost] {msandbox} ((none)) > SELECT @@session.collation_connection;
      +--------------------------------+
      | @@session.collation_connection |
      +--------------------------------+
      | utf8mb4_0900_ai_ci             |
      +--------------------------------+
      1 row in set (0.00 sec)
    
    -- 기본 utf8mb4의 collation인 utf8mb4_0900_ai_ci는 Accent Insensitive 모드이다.
    
      mysql 8.0 [localhost] {msandbox} ((none)) > select if('ê' = 'e','True','False');
      +-------------------------------+
      | if('ê' = 'e','True','False')  |
      +-------------------------------+
      | True                          |
      +-------------------------------+
      1 row in set (0.00 sec)
    
    
    --^^ Accent Insensitive 모드에서는 if('ê' = 'e') 의 결과가 참이된다.
    
    
      mysql 8.0 [localhost] {msandbox} ((none)) > SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_ci;
      Query OK, 0 rows affected (0.00 sec)
    
      mysql 8.0 [localhost] {msandbox} ((none)) > SELECT @@session.collation_connection;
      +--------------------------------+
      | @@session.collation_connection |
      +--------------------------------+
      | utf8mb4_0900_as_ci             |
      +--------------------------------+
      1 row in set (0.00 sec)
    
    --  Accent sensitive 모드로 변경해보자.
    
      mysql 8.0 [localhost] {msandbox} ((none)) > select if('ê' = 'e','True','False');
      +-------------------------------+
      | if('ê' = 'e','True','False')  |
      +-------------------------------+
      | False                         |
      +-------------------------------+
      1 row in set (0.00 sec)
    
    
    --^^ Accent Sensitive 모드에서는 if('ê' = 'e') 의 결과가 거짓이된다.
    

PAD의 추가

  • MySQL은 전통적으로 데이터 값의 뒤 공백을 제거하고 비교하는 방법을 사용해왔다. 하지만 8.0부터는 Collation 에 따라, ORACLE 과 비슷하게 공백을 제거하지 않고 비교가 가능하다.

    mysql 8.0 [localhost] {msandbox} ((none)) > select * from information_schema.collations where COLLATION_NAME like 'utf8mb4_0900%' or COLLATION_NAME = 'utf8mb4_bin' or COLLATION_NAME like 'utf8mb4_general%';
    +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    | COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
    +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    | utf8mb4_general_ci | utf8mb4            |  45 |            | Yes         |       1 | PAD SPACE     |
    | utf8mb4_bin        | utf8mb4            |  46 |            | Yes         |       1 | PAD SPACE     |
    | utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
    | utf8mb4_0900_as_cs | utf8mb4            | 278 |            | Yes         |       0 | NO PAD        |
    | utf8mb4_0900_as_ci | utf8mb4            | 305 |            | Yes         |       0 | NO PAD        |
    +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    5 rows in set (0.00 sec)
    
  • 기본적으로 collation을 따로 지정하지 않고 사용하는 경우 5.7과 8.0의 동작방식이 달라질 수 있음을 확인해야한다.

    5.7의 경우, 뒤에 나오는 모든 공백을 기본적으로 제거하고 비교하지만, 8.0의 경우 utf8mb4 character의 default collation 이 ‘NO PAD’ 형식으로 동작함으로 공백을 제거하지 않고 비교함을 유의해야 한다.

    이는 WAS 의 connection string에서도 꼭 connectionCollation 을 지정해야하는 이유이기도 하다.

    • https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html
  • 버젼별 비교

    • 5.7

      -- 5.7의 경우, PADDING에 대한 옵션이 존재하지 않음으로 모두 PAD SPACE (뒤의 공백제거) 로 처리된다.
      
      
      mysql 5.7 [localhost] {msandbox} (test) > SET NAMES utf8mb4;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > SELECT @@session.collation_connection;
      +--------------------------------+
      | @@session.collation_connection |
      +--------------------------------+
      | utf8mb4_general_ci             |
      +--------------------------------+
      1 row in set (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > SELECT hex('a ' ), hex ('a'), 'a ' = 'a';
      +------------+-----------+------------+
      | hex('a ' ) | hex ('a') | 'a ' = 'a' |
      +------------+-----------+------------+
      | 6120       | 61        |          1 |
      +------------+-----------+------------+
      1 row in set (0.01 sec)
      
      --^^ PAD SPACE로 사용됨으로 'a ' = 'a' 가 같음을 유의하자.
      
      
      
      
      mysql 5.7 [localhost] {msandbox} (test) > SET NAMES utf8mb4 COLLATE utf8mb4_bin;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > SELECT @@session.collation_connection;
      +--------------------------------+
      | @@session.collation_connection |
      +--------------------------------+
      | utf8mb4_bin                    |
      +--------------------------------+
      1 row in set (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > SELECT hex('a ' ), hex ('a'), 'a ' = 'a';
      +------------+-----------+------------+
      | hex('a ' ) | hex ('a') | 'a ' = 'a' |
      +------------+-----------+------------+
      | 6120       | 61        |          1 |
      +------------+-----------+------------+
      1 row in set (0.00 sec)
      
    • 8.0

      -- 8.0의 경우, PADDING에 대한 옵션이 collation마다 다름으로 주의가 필요하다.
      
      
      mysql 8.0 [localhost] {msandbox} ((none)) > SET NAMES utf8mb4;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > SELECT @@session.collation_connection;
      +--------------------------------+
      | @@session.collation_connection |
      +--------------------------------+
      | utf8mb4_0900_ai_ci             |
      +--------------------------------+
      1 row in set (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > SELECT hex('a ' ), hex ('a'), 'a ' = 'a';
      +------------+-----------+------------+
      | hex('a ' ) | hex ('a') | 'a ' = 'a' |
      +------------+-----------+------------+
      | 6120       | 61        |          0 |
      +------------+-----------+------------+
      1 row in set (0.00 sec)
      
      --^^ NO PAD로 사용됨으로 'a ' = 'a' 가 다름을 유의하자.
      
      
      
      mysql 8.0 [localhost] {msandbox} ((none)) > SET NAMES utf8mb4 COLLATE utf8mb4_bin;
      Query OK, 0 rows affected (0.01 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > SELECT @@session.collation_connection;
      +--------------------------------+
      | @@session.collation_connection |
      +--------------------------------+
      | utf8mb4_bin                    |
      +--------------------------------+
      1 row in set (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > SELECT hex('a ' ), hex ('a'), 'a ' = 'a';
      +------------+-----------+------------+
      | hex('a ' ) | hex ('a') | 'a ' = 'a' |
      +------------+-----------+------------+
      | 6120       | 61        |          1 |
      +------------+-----------+------------+
      1 row in set (0.00 sec)
      
      

SMP (Supplementary Multilingual Plane) 의 확장

  • MySQL 을 사용함에 있어서 아주 유명한 Bug가 존재했다. (Case Insensitive collation을 사용할때, 많은 이모지들이 같은 값으로 치부되는 버그)

    • https://bugs.mysql.com/bug.php?id=87700
  • 이를 해결하기 위해서는 5.2 이상의 unicode를 지원하는 utf8mb4_unicode_520_ci collation을 사용하거나, binary 형태로 변경하여 사용을 해야하는 번거로움이 있었습니다. 하지만 8.0의 기본 collation 인 utf8mb4_0900_ai_ci 가 unicode 9.0을 지원하면서 부터 많은 추가적인 emoji의 연산이 가능해졌다.

  • 버젼별비교

    • 5.7

      mysql 5.7 [localhost] {msandbox} (test) > SET NAMES utf8mb4;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > show session variables like 'collation_connection';
      +----------------------+--------------------+
      | Variable_name        | Value              |
      +----------------------+--------------------+
      | collation_connection | utf8mb4_general_ci |
      +----------------------+--------------------+
      1 row in set (0.01 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): ��
      HEX(WEIGHT_STRING('?')): FFFD
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): ��
      HEX(WEIGHT_STRING('?')): FFFD
                    '?' = '?': 1
      1 row in set (0.00 sec)
      
      --^^ 5.7까지 utf8mb4의 기본 collation의 emoji 비교가 정상적으로 이루어지지 않았다. HEX값이 다르더라도, collation을 비교할 수 있는 WEIGHT_STRING이 값을 처리하지 못했다.
      
      
      mysql 5.7 [localhost] {msandbox} (test) > set names utf8mb4 collate utf8mb4_unicode_ci;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > show session variables like 'collation_connection';
      +----------------------+--------------------+
      | Variable_name        | Value              |
      +----------------------+--------------------+
      | collation_connection | utf8mb4_unicode_ci |
      +----------------------+--------------------+
      1 row in set (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): ��
      HEX(WEIGHT_STRING('?')): FFFD
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): ��
      HEX(WEIGHT_STRING('?')): FFFD
                    '?' = '?': 1
      1 row in set (0.00 sec)
      
      --^^ 기본으로 제공되는 unicode에서도 같은 문제가 발생되었다.
      
      
      mysql 5.7 [localhost] {msandbox} (test) > set names utf8mb4 collate utf8mb4_unicode_520_ci;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > show session variables like 'collation_connection';
      +----------------------+------------------------+
      | Variable_name        | Value                  |
      +----------------------+------------------------+
      | collation_connection | utf8mb4_unicode_520_ci |
      +----------------------+------------------------+
      1 row in set (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): ���c
      HEX(WEIGHT_STRING('?')): FBC3F363
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): ���z
      HEX(WEIGHT_STRING('?')): FBC3F37A
                    '?' = '?': 0
      1 row in set (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > set names utf8mb4 collate utf8mb4_bin;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > show session variables like 'collation_connection';
      +----------------------+-------------+
      | Variable_name        | Value       |
      +----------------------+-------------+
      | collation_connection | utf8mb4_bin |
      +----------------------+-------------+
      1 row in set (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): c
      HEX(WEIGHT_STRING('?')): 01F363
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): z
      HEX(WEIGHT_STRING('?')): 01F37A
                    '?' = '?': 0
      1 row in set (0.00 sec)
      
      --^^ 해당문제를 피하기 위해서는 unicod 5.2가 구현된 utf8mb4_unicode_520_ci 혹은 binary 형태로 사용해야 했다.
      
    • 8.0

      mysql 8.0 [localhost] {msandbox} ((none)) > SET NAMES utf8mb4;
      Query OK, 0 rows affected (0.01 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > show session variables like 'collation_connection';
      +----------------------+--------------------+
      | Variable_name        | Value              |
      +----------------------+--------------------+
      | collation_connection | utf8mb4_0900_ai_ci |
      +----------------------+--------------------+
      1 row in set (0.03 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'):
      
      HEX(WEIGHT_STRING('?')): 130C
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): #
      HEX(WEIGHT_STRING('?')): 1323
                    '?' = '?': 0
      1 row in set (0.00 sec)
      
      --^^ 8.0의 경우, 기본적으로 unicode9.0을 사용함으로, 해당문제를 해결하였다.
      
      
      
      
      mysql 8.0 [localhost] {msandbox} ((none)) > set names utf8mb4 collate utf8mb4_unicode_ci;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > show session variables like 'collation_connection';
      +----------------------+--------------------+
      | Variable_name        | Value              |
      +----------------------+--------------------+
      | collation_connection | utf8mb4_unicode_ci |
      +----------------------+--------------------+
      1 row in set (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): ��
      HEX(WEIGHT_STRING('?')): FFFD
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): ��
      HEX(WEIGHT_STRING('?')): FFFD
                    '?' = '?': 1
      1 row in set (0.00 sec)
      
      --^^ 여전히 기본으로 제공되는 unicode에서도 문제가 발생된다. 이는 하위 호환성을 위함이다.
      
      
      mysql 8.0 [localhost] {msandbox} ((none)) > set names utf8mb4 collate utf8mb4_unicode_520_ci;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > show session variables like 'collation_connection';
      +----------------------+------------------------+
      | Variable_name        | Value                  |
      +----------------------+------------------------+
      | collation_connection | utf8mb4_unicode_520_ci |
      +----------------------+------------------------+
      1 row in set (0.01 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): ���c
      HEX(WEIGHT_STRING('?')): FBC3F363
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): ���z
      HEX(WEIGHT_STRING('?')): FBC3F37A
                    '?' = '?': 0
      1 row in set (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > set names utf8mb4 collate utf8mb4_bin;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > show session variables like 'collation_connection';
      +----------------------+-------------+
      | Variable_name        | Value       |
      +----------------------+-------------+
      | collation_connection | utf8mb4_bin |
      +----------------------+-------------+
      1 row in set (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} ((none)) > select HEX('🍣'), WEIGHT_STRING('🍣'), HEX(WEIGHT_STRING('🍣')),
      HEX('🍺'), WEIGHT_STRING('🍺'), HEX(WEIGHT_STRING('🍺')), '🍣' = '🍺' \G
      *************************** 1. row ***************************
                     HEX('?'): F09F8DA3
           WEIGHT_STRING('?'): c
      HEX(WEIGHT_STRING('?')): 01F363
                     HEX('?'): F09F8DBA
           WEIGHT_STRING('?'): z
      HEX(WEIGHT_STRING('?')): 01F37A
                    '?' = '?': 0
      1 row in set (0.00 sec)