Last Updated on 2월 1, 2024 by Jade(정현호)
안녕하세요.
이번 포스팅에서는 binary 타입의 collation 을 사용하여 문자형 컬럼 타입인 varchar 와 char 의 대소문자 구분과 관련된 내용을 확인해보도록 하겠습니다.
MySQL Collation
Collation 은 Character 간의 정렬을 의미하며 Collation 은 크게 2가지로 나뉘게 됩니다.
• binary collation
문자를 encoding 된 바이너리 스트림 값으로 문자를 비교하게 됩니다
a 와 A 는 코드가 다르기 때문에 다른 문자로 인식되며, 즉 대소문자를 구별할 수 있습니다.
• case-insensitive collation
_ci 가 붙은 문자열입니다.(ex: utf8mb4_unicode_ci)
1. 대문자 소문자를 같은 문자로 다루게 됩니다.
2. 이 후에 encoding 으로 비교합니다.
_ci 에는 몇 가지 종류가 있으며 여기서는 크게 2가지에 대해서 언급 드리도록 하겠습니다.
(CI: case-insensitive: 대소문자 구별하지 않는)
크게 general_ci 과 unicode_ci 2가지로 구분할 수 있으며 보통 둘 중 하나를 사용합니다. 각 언어셋별 default collation 정보는 show character set 에서 확인 할 수 있습니다
default collation 은 general 이 대부분이며 collation 별로 정렬의 기능의 차이와 속도의 차이가 발생하게 됩니다.
• general_ci
속도를 높이는데 중점이 되어 빠르며 일반적인 경우 사용되게 됩니다 general_ci 의 경우 ÀÁÅåāă 등 과 같이 accents 문자가 없어서 대해서 해당 단어의 대문자인 A로 치환/비교 되게 됩니다
• unicode_ci
유니코드 규칙을 기반으로 하여 정확한 정렬을 목표로 수행됩니다.
[참고] general_ci 와 unicode_ci 외 Collation 간의 속도 벤치마크
charset-and-collation-settings-impact-on-mysql-performance
해당 포스팅은 Characterset 과 관련된 이전 포스팅에서 이어지는 글입니다.
포스팅 환경
- MySQL 8.0.23
- Characterset : utf8mb4
- collation_server : 기본(default)
binary collation
문자형 컬럼인 char 와 varchar 에서는 위에서 설명한 내용과 같은 binary 형 collation 을 사용할 수 있습니다.
* 포스팅에서는 테스트의 간편을 위해서 varchar 만 사용하였으나 동일하게 하게 사용할 수 있습니다.
관련해서 어떠한 차이점이나 어떤 형태로 동작 하는지 아래에서 살펴보도록 하겠으며, 먼저 테스트 테이블 과 데이터를 입력하도록 하겠습니다.
[참고] BINARY 및 VARBINARY 와 같은 이진 문자열 타입 데이터 유형은 CHAR BINARY 및 VARCHAR BINARY 데이터 유형과 다르며, 포스팅에서는 CHAR BINARY 및 VARCHAR BINARY 데이터 유형에 대해서 설명하고 있습니다.
테스트 테이블 생성
테이블 생성
create table tb_collation_test (id int not null auto_increment primary key, col1 varchar(10), col2 varchar(10) binary ) engine=innodb; OR create table tb_collation_test (id int not null auto_increment primary key, col1 varchar(10), col2 varchar(10) character set utf8mb4 collate utf8mb4_bin ) engine=innodb;
테이블은 위와 같이 생성할 수 있으며, binary 절은 아직까지는 사용가능한 구문이나 8.0기준으로 deprecated 되었으며 향후 버전에서는 remove 될 예정입니다.
그래서 binary 절을 사용하여 생성시 아래와 같은 warning 메세지를 확인할 수 있습니다.
'BINARY as attribute of a type' is deprecated and will be removed in a future release.
Please use a CHARACTER SET clause with _bin collation instead
[참고] 현재 포스팅에서 사용중인 테스트 논리 database 는 별도의 collation 을 지정하지 않고 기본값으로 생성한 상태입니다.
데이터 입력
insert into tb_collation_test values (1,'aBc','deF'),(2,'AbC','DEf'),(3,'abC','def'),(4,'aBC','dEf');
정렬
먼저 co1 컬럼으로 정렬을 확인해보도록 하겠습니다.
select * from tb_collation_test order by col1 asc; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | aBc | deF | | 2 | AbC | DEf | | 3 | abC | def | | 4 | aBC | dEf | +----+------+------+ select * from tb_collation_test order by col1 desc; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | aBc | deF | | 2 | AbC | DEf | | 3 | abC | def | | 4 | aBC | dEf | +----+------+------+
col1 은 보통의 varchar 컬럼입니다. 위와 결과와 같이 대소문자가 섞여 있지만, ascending 이나 descending 모두 같은 정렬을 보여주고 있습니다.
이번에는 col2 로 정렬하도록 하겠습니다.
select * from tb_collation_test order by col2 desc; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 3 | abC | def | | 1 | aBc | deF | | 4 | aBC | dEf | | 2 | AbC | DEf | +----+------+------+ select col1 from tb_collation_test order by col1 asc; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | aBc | deF | | 2 | AbC | DEf | | 3 | abC | def | | 4 | aBC | dEf | +----+------+------+
col2 는 binary 형 varchar 로 collation 은 utf8mb4_bin 입니다. 위의 결과를 보듯이 대소문자 구분이 되어 정렬이 되는 것을 확인할 수 있습니다.
컬럼을 binary collation 으로 생성하지 않아도 쿼리에서 지정하여 사용할 수 있습니다.
-- 이전에 수행한 order by 쿼리
select col1
from tb_collation_test
order by col1 asc;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | aBc | deF |
| 2 | AbC | DEf |
| 3 | abC | def |
| 4 | aBC | dEf |
+----+------+------+
-- binary 절을 사용한 쿼리
select *
from tb_collation_test
order by binary col1 asc;
<!!-- order by 에서 binary 를 사용
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 2 | AbC | DEf |
| 4 | aBC | dEf |
| 1 | aBc | deF |
| 3 | abC | def |
+----+------+------+
binary collation 으로 지정하지 않은 컬럼에 대해서는 위와 같이 사용할 수 있습니다.
테스트를 위해서 인덱스를 생성하고 SQL Plan 을 확인해보도록 하겠습니다.
-- 인덱스 생성 alter table `tdb`.`tb_collation_test` add index `ix_col1` (`col1`); -- plan 확인 explain select col1 from tb_collation_test order by binary col1 asc; +----+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | tb_collation_test | index | NULL | ix_col1 | 43 | NULL | 4 | 100.00 | Using index; Using filesort | +----+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
혹시 index 를 사용하지 못하는지를 확인해보기 위해서 인덱스를 생성 후 plan 을 확인하였으며 order by 에서는 index 가 사용할 수 있음을 확인할 수 있습니다.
조회
이번에는 조회를 해보도록 하겠습니다.
먼저 col1 컬럼에 조회조건을 사용해서 조회해보도록 하겠습니다.
select * from tb_collation_test where col1='abc'; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | aBc | deF | | 2 | AbC | DEf | | 3 | abC | def | | 4 | aBC | dEf | +----+------+------+ select * from tb_collation_test where col1='ABC'; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | aBc | deF | | 2 | AbC | DEf | | 3 | abC | def | | 4 | aBC | dEf | +----+------+------+ select * from tb_collation_test where col1='ABc'; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | aBc | deF | | 2 | AbC | DEf | | 3 | abC | def | | 4 | aBC | dEf | +----+------+------+
일반 varchar 인 col1 에서는 대소문자를 구분하지 않기 때문에 대소문자가 맞지 않는 데이터도 모두 같이 출력되는 것을 확인할 수 있습니다.
이번에는 col2 에서 조회조건을 사용해서 조회해보도록 하겠습니다.
select * from tb_collation_test where col2='def'; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 3 | abC | def | +----+------+------+ select * from tb_collation_test where col2='DEf'; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 2 | AbC | DEf | +----+------+------+ select * from tb_collation_test where col1= binary 'abc'; Empty set (0.00 sec) <-- 결과 없음
binary collation 인 col2는 대소문자 구별을 하여 정확히 일치하는 데이터만 출력하는 것을 확인할 수 있습니다.
이전 단계에서 order by 시 binary 절을 사용한 것처럼 조회시에도 binary 절을 사용할 수 있습니다.
col1 컬럼에서 binary 를 사용해서 조회해보겠습니다.
select * from tb_collation_test where col1= binary 'abC'; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 3 | abC | def | +----+------+------+
SQL Plan도 살펴보도록 하겠습니다.
explain select * from tb_collation_test where col1= binary 'abC'; +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | tb_collation_test | NULL | range | ix_col1 | ix_col1 | 43 | NULL | 4 | 100.00 | Using index condition | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
* 포스팅 중간에 생성한 인덱스를 동일한 같이 생성해야지 위와 같은 결과가 나옴
위의 플랜에서 확인하였을 때 인덱스를 사용하는 것을 확인할 수 있습니다.
다만 튜닝 방법론에서 보통 많이 나오는 이야기는 "우변을 가공하고 좌변을 가공하지 말아라" 입니다.
방금 위에서 binary 절은 우변인 조건 대입부분에서 사용하였으며, 이번에는 좌변의 의미인 컬럼에 사용하고 플랜을 확인해보도록 하겠습니다.
select *
from tb_collation_test
where binary col1= 'abC';
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 3 | abC | def |
+----+------+------+
<--!! 대소문자를 구분하여 정확한 데이터를 추출함
explain
select *
from tb_collation_test
where binary col1= 'abC';
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_collation_test | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------+-------------+
인덱스 사용안됨 !!->
원하는 데로 대소문자를 구분하여 조회조건에 입력된 데이터만 추출은 되었습니다.
다만 플랜에서는 이전과는 다르게 Index가 사용하지 못하고 있는 것을 확인할 수 있습니다.
MySQL 8.0 버전의 Function Based Index 를 사용하는 것이 아닌 일반적인 형태의 인덱스의 경우 컬럼인 좌변을 함수로 가공하면 인덱스이 안될 수 있습니다.
[참고] 컬럼 타입 변경
위의 테이블 구조에서 col1 을 varchar(10) binary 로 변경 시 조건에 따라서 Onlie DDL 타입이 달라지게 됩니다.
algorithm=instant 는 불가하고 algorithm=inplace, lock=none 는 해당 컬럼에 인덱스가 없다면 가능합니다.
인덱스가 있다면 algorithm=inplace, lock=none 도 불가하며 copy 방식으로 가능 합니다.
관련 내용은 이전 포스팅을 참조하시면 됩니다.
Unique
이번에는 Unique 인덱스 관련된 내용을 확인해보도록 하겠습니다.
테스트를 위해서 이전에 생성한 인덱스를 삭제하도록 하겠습니다.
alter table `tdb`.`tb_collation_test` drop index ix_col1;
그 다음에 Unique 제약조건을 위해서 Unique 인덱스를 생성하도록 하겠습니다.
ALTER TABLE `tdb`.`tb_collation_test` ADD UNIQUE INDEX `ux_col1` (`col1`); -> ERROR 1062 (23000): Duplicate entry 'aBc' for key 'tb_collation_test.ux_col1' <!!-- 에러 발생 ALTER TABLE `tdb`.`tb_collation_test` ADD UNIQUE INDEX `ux_col2` (`col2`); -> Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
col2 컬럼에 해당하는 Unique 인덱스는 생성이 되었으며 col1 컬럼의 인덱스는 생성 에러가 발생하였습니다.
컬럼에 Unique 를 생성하는 과정에서 에러가 발생되었으며, 이유로는 데이터가 중복이 있어서 입니다.
대소문자를 구별해서 보면 중복 데이터가 없지만, col1 은 대소문자 구별이 되지 않기 때문에 중복 데이터로 인식되고 있으며 그로 인해 인덱스가 생성이 되지 않고 있습니다.
테이블의 데이터를 삭제하고 인덱스를 생성 후 데이터를 입력해보도록 하겠습니다.
-- 테이블 데이터 삭제 truncate table tb_collation_test; -- col1 에 Unique 인덱스 생성 ALTER TABLE `tdb`.`tb_collation_test` ADD UNIQUE INDEX `ux_col1` (`col1`); -> Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 - 데이터 입력 insert into tb_collation_test values(1,'aBc','deF'); insert into tb_collation_test values(2,'AbC','DEf'); ERROR 1062 (23000): Duplicate entry 'AbC' for key 'tb_collation_test.ux_col1' <-- 오류 발생
위의 내용처럼 대소문자가 다르지만 구분하지 않기 때문에 데이터 중복이 발생되고 있습니다.
공백 처리
MySQL 데이터 정렬에는 PAD SPACE 또는 NO PAD 값과 같은 pad 속성이 있습니다.
대부분의 MySQL 데이터 정렬에는 PAD SPACE의 패드 속성입니다.
이진 문자열( BINARY, VARBINARY 및 BLOB) 아닌 문자열( CHAR, VARCHAR 및 TEXT 값)의 경우 문자열 데이터 정렬 패드 속성은 문자열 끝에 있는 후행 공백 비교에서 처리를 결정합니다.
- PAD SPACE 데이터 정렬 과 비교 시 후행 공백은 비교 시 중요하지 않습니다. 문자열은 후행 공백에 관계없이 비교됩니다.
- NO PAD 데이터 정렬은 다른 문자와 마찬가지로 비교에서 후행 공백을 중요한 것으로 취급합니다.
utf8mb4 바이너리 collation 에서의 PAD_ATTRIBUTE 을 확인할 수 있으며 그 중 하나는 PAD SPACE이고 다른 하나는 NO PAD입니다.
INFORMATION_SCHEMA COLLATIONS 테이블을 사용하여 데이터 정렬에 대한 패드 속성을 결정하는 방법을 확인할 수 있으며, 확인해보도록 하겠습니다.
select collation_name, pad_attribute
from information_schema.collations
where collation_name in
('utf8mb4_bin','utf8mb4_general_ci','utf8mb4_0900_ai_ci','utf8mb4_0900_bin');
+--------------------+---------------+
| collation_name | pad_attribute |
+--------------------+---------------+
| utf8mb4_0900_ai_ci | NO PAD |
| utf8mb4_0900_bin | NO PAD |
| utf8mb4_bin | PAD SPACE |
| utf8mb4_general_ci | PAD SPACE |
+--------------------+---------------+
위와 같이 NO PAD 와 PAD SPACE 의 정보 값을 확인할 수 있으며 차이점은 아래와 같이 확인할 수 있습니다.
위에서 계속 사용하던 테이블을 이용해서 공백을 넣고 조회를 해보도록 하겠습니다.
-- col1 컬럼으로 조회 select col1,col2 from tb_collation_test where col1='AbC '; Empty set (0.00 sec) <!-- 결과 없음 -- col2 컬럼으로 조회 select col1,col2 from tb_collation_test where col2='DEf '; +------+------+ | col1 | col2 | +------+------+ | AbC | DEf | +------+------+
위의 결과 처럼 col1 은 뒤의 공백을 추가하여 조회하였을 때 공백을 인식하여 다른 값임으로 매칭되는 결과가 없어서 Empty set 이 되었지만, binary 형 varchar(utf8mb4_bin) 는 공백을 넣었음에도 where 절 조건 매칭이 되어 조회결과나 나오는 것을 확인할 수 있습니다.
PAD SPACE 와 NO PAD 차이점은 아래와 같습니다.
- PAD SPACE 데이터 정렬 과 비교 시 후행 공백은 비교 시 중요하지 않습니다. 문자열은 후행 공백에 관계없이 비교됩니다.
- NO PAD 데이터 정렬은 다른 문자와 마찬가지로 비교에서 후행 공백을 중요한 것으로 취급합니다.
추가로 set names 을 통해서 몇 가지를 더 확인해보도록 하겠습니다.
set names utf8mb4 collate utf8mb4_bin; select 'AbC ' = 'AbC'; +----------------+ | 'AbC ' = 'AbC' | +----------------+ | 1 | +----------------+ set names utf8mb4 collate utf8mb4_0900_bin; select 'AbC ' = 'AbC'; +----------------+ | 'AbC ' = 'AbC' | +----------------+ | 0 | +----------------+ set names utf8mb4 collate utf8mb4_general_ci; select 'AbC ' = 'AbC'; +----------------+ | 'AbC ' = 'AbC' | +----------------+ | 1 | +----------------+
위와 같이 collation 별로 PAD Attribute 가 다르며 그에 따라서 공백의 처리가 다르게 됩니다.
처음에 테이블에서 조회하였을 때 col1 이 공백까지 확인해서 조회결과가 없는 것으로 확인하였습니다.
그럼 데이터베이스와 테이블, 컬럼의 collation 정보를 조금 더 자세하게 살펴보겠습니다.
show create database tdb;
+----------+-------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------+
| tdb | CREATE DATABASE `tdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */|
+----------+-------------------------------------------------------------------------------------------+
show create table tb_collation_test\G
*************************** 1. row ***************************
Table: tb_collation_test
Create Table: CREATE TABLE `tb_collation_test` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar(10) DEFAULT NULL,
`col2` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show full columns from tb_collation_test;
+-------+-------------+--------------------+
| Field | Type | Collation |
+-------+-------------+--------------------+
| id | int | NULL |
| col1 | varchar(10) | utf8mb4_0900_ai_ci |
| col2 | varchar(10) | utf8mb4_bin |
+-------+-------------+--------------------+
* 위의 표는 가로 길이에 따라서 일부 내용이 편집되어 있습니다.
글 처음에 기재된 내용과 같이 MySQL 8.0 의 기본 character set 과 collation 환경입니다.
데이터베이스의 기본 collation은 utf8mb4_0900_ai_ci 이며, 그에 따라서 테이블도 utf8mb4_0900_ai_ci 이며, binary 타입 varchar가 아닌 col1 컬럼의 경우 collation이 utf8mb4_0900_ai_ci 입니다.
그래서 col1 의 collation 을 utf8mb4_0900_ai_ci 에서 utf8mb4_general_ci 로 변경하거나 생성시 부터 utf8mb4_general_ci 으로 생성하면 위의 공백을 포함한 조회결과는 달라집니다.
alter table tb_collation_test modify `col1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, algorithm=inplace,lock=none;
위의 algorithm 은 상황에 따라서 사용 가능 여부가 달라집니다.
- 컬럼에 인덱스 있음 : 불가 / 컬럼에 인덱스 없음 : 가능
관련된 Online DDL 은 이전 포스팅을 참조하시면 됩니다.
테이블 정보를 확인 후 다시 조회를 해보겠습니다.
show create table tb_collation_test\G
*************************** 1. row ***************************
Table: tb_collation_test
Create Table: CREATE TABLE `tb_collation_test` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`col2` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
--> 공백이 있으나 이번에는 조회가 가능
mysql> select col1,col2
from tb_collation_test
where col1='AbC ';
+------+------+
| col1 | col2 |
+------+------+
| aBc | deF |
| AbC | DEf |
| abC | def |
| aBC | dEf |
+------+------+
이전과 다르게 이번에는 공백이 포함이 있지만 조건절에 매칭이 되어 결과가 나온 것을 확인할 수 있습니다. 다만 utf8mb4_bin 와 달리 대소문자는 구별하지 않기 때문에 여러 로우가 출력된 것을 확인할 수 있습니다.
Note
이진 문자열(BINARY, VARBINARY 및 BLOB 값)의 경우 후행 공백을 포함하여 모든 바이트가 비교합니다.
set names binary;
select 'AbC ' = 'AbC';
+--------------+
| 'a ' = 'a' |
+--------------+
| 0 |
+--------------+
그럼 이번 포스팅은 여기서 마무리하도록 하겠습니다.
Reference
Reference URL
• mysql.com/charset-binary-collations
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
