Character set & Collation

default_collation_for_utf8mb4

• MySQL 8.0부터 생긴 변수로, utf8mb4의 default collation을 설정할 수 있다.
• utf8mb4_0900_ai_ci: uft8mb4_unicode_ci의 상위버전으로, ai(accent insensitive) & ci(case insensitive). 공백을 다른 문자로 구분(NO PAD)
• utf8mb4_general_ci: padding을 포함한 동일 문자가 들어간 string은 동일한 문자열로 봄(PAD SPACE)

 

※ unicode_ci VS general_ci

unicode_ci: collation set으로 general_ci와 동일하게 검색시에 대소문자 구분과 전각/반각문자, 이모티콘 구분도 하지 않음

  => 고유값을 비교하는 데 유용하나 이모티콘 값끼리 비교할 수 없음

  => 컬럼에 LOWER/UPPER 가공이 필요하지 않음

  * unicode_520_ci: unicode_ci와 동일하나 이모티콘 값을 구분하여 검색할 수 있음

general_ci: collation set으로 검색 시에 대소문자 구분을 하지 않음. 전각/반각 구분 가능, 이모티콘 구분 불가

  => 대소문자 구분 없는 값 비교를 위해 LOWER/UPPER 가공이 필요하지 않음

 

 

---

주의사항

• CREATE DATABASE 시 default charset을 설정하고 default collation에 대해서는 명시하지 않는 경우, MySQL은 collation_server, collation_database, collation_connection값을 무시하고 명시한 character set의 default collation이 적용된다.
  • CREATE DATABASE 시 두 값을 모두 명시했어도, 해당 DB에서 CREATE TABLE 시에 character set만 따로 명시하는 경우에도 같은 현상 발생
• CREATE DATABASE 시 default charset, default collation을 모두 명시한 경우에는 DDL시 해당 설정값으로 적용된다.
• CREATE DATABASE 시 default charset, default collation을 모두 명시하지 않은 경우, collation_server 및 character_set_server 값으로 적용된다.
• CREATE DATABASE 시 default collation만 명시하고 default charset은 명시하지 않은 경우, 설정한 collation과 연관된 character set이 적용된다.

#1.
mysql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
 
mysql> create database test1 default character set utf8mb4;
Query OK, 1 row affected (0.03 sec)
 
mysql> show create database test1;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
#2.
mysql> set @@collation_database='utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
 
mysql> create database test2 default character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
 
mysql> show create database test2;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| test2    | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
#3.
mysql> set @@collation_connection='utf8mb4_general_ci';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
 
mysql> create database test3 default character set utf8mb4;
Query OK, 1 row affected (0.01 sec)
 
mysql> show create database test3;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| test3    | CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
#4.
mysql> set collation_server='utf8mb4_unicode_ci';
Query OK, 0 rows affected (0.01 sec)
 
mysql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)
 
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)
 
mysql> show create database test4;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| test4    | CREATE DATABASE `test4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
#5.
mysql> create database test5 default character set utf8 default collate utf8_unicode_ci;
Query OK, 1 row affected, 2 warnings (0.00 sec)
 
mysql> use test5;
Database changed
mysql> create table default_table(nm varchar(10));
Query OK, 0 rows affected (0.04 sec)
 
mysql> show create table default_table\G
*************************** 1. row ***************************
       Table: default_table
Create Table: CREATE TABLE `default_table` (
  `nm` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)
 
mysql> create table utf8mb4_table(nm varchar(10)) default character set utf8mb4;
Query OK, 0 rows affected (0.03 sec)
 
mysql> show create table utf8mb4_table\G
*************************** 1. row ***************************
       Table: utf8mb4_table
Create Table: CREATE TABLE `utf8mb4_table` (
  `nm` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

 

참고 https://dev.mysql.com/doc/refman/5.7/en/charset-database.html

MySQL :: MySQL 5.7 Reference Manual :: 10.3.3 Database Character Set and Collation

 

---

MySQL issue of default_collation_for_utf8mb4

• MySQL 도큐먼트에서는 8.0.18 버전부터 utf8mb4의 default collation이 수정 가능하도록 설정한 변수라고 설명되어 있으나, mysql client에서 실행 시 아래의 경고가 뜨는 현상 발견

mysql> set default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                |
+---------+------+--------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. |
+---------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• 관련 mysql bug report 를 올렸으나 별다른 개선사항은 아직 없다..