2023.03.15-개발일지
서비스 운영중 QA 분께 연락이왔습니다. 데이터 베이스에선 조회되는데 애플리케이션에서 보여지지 않는다는 내용이었습니다. DB를 보니 조회는 정상적으로 되는데 application에서 해당 코드로 group by시 key로 잡히지 않았습니다. 코드를 여러번 보았으나 문제가 없는듯하여 해당 부분의 데이터를 복사하여 실행결과… 우연히도 후행 공백 관련 이슈로 확인할 수 있었습니다.
MySQL trailing spaces problem
MySQL의 고유한 동작 방식으로 varchar 혹은 char 타입의 칼럼에서 = 비교시 후행 공백을 무시하는 현상입니다. 공백의 개수는 상관 없으며 반드시 후행에서만 무시됩니다.
#true, true, false
select 'hahava' = 'hahava', 'hahava ' = 'hahava', ' hahava' ='hahava';
그러나 해당 내용은 MySQL의 버그가 아니라 ansi sql 중 SQL-92 공식 문서에 나와있는 내용입니다. 표준 SQL문서를 참고하며 char는 데이터 저장시 할당한 크기 만큼 공백문자를 채우며 쿼리시 동등한 값으로 비교된다는 내용을 확인할 수 있습니다.
MySQL 또한 이런한 내용을 바탕으로 구현되었기에 문서를 보면 해당 내용을 자세히 설명하고 있습니다.
Solutions
Column이 Char 타입인 경우
SET PAD_CHAR_TO_FULL_LENGTH;
SQL 모드를 위와 같이 설정하면 = 시 공백을 포함하여 비교합니다.
그러나 varchar의 경우는 적용되지 않습니다.
공백 제거
UPDATE ${table_name} SET ${column_name} = trim(column_name);
trim 을 이용하여 공백을 제거할 수 있습니다.
@ColumnTransformer(write="trim(?)")
@Column(name = "name")
val name:String
jpa 사용시 ColumnTransformer 하여 강제로 trim 할 수 있습니다.
like 연산
#true, false, false
select 'kalin' LIKE 'kalin', 'kalin ' LIKE 'kalin', ' kalin' LIKE 'kalin';
LIKE 연산으로 정확한 값을 찾을 수 있습니다. 그러나 패턴매치를 하기 때문에 성능 저하가 발생할 우려가 있어 권장하지 않는 방법입니다.
MySQL 8.x Upgrade
해당 문제는 5.7에서 발생했던 내용으로 8.x 이후 부터는 동작 방식이 바뀌면서 PAD_CHAR_TO_FULL_LENGT도 deprecated 되었고 varchar의 경우도 자연스럽게 후행 공백을 포함하여 검색하게 됩니다. 세부 내용은 문자열 인코딩 및 구현 방식에 관한 내용 때문입니다.
SELECT *
FROM information_schema.collations
WHERE COLLATION_NAME RLIKE 'utf8(mb4)?_(general|0900_ai)_ci';
# mysql 5.7
+------------------+------------------+--+----------+-----------+-------+
|COLLATION_NAME |CHARACTER_SET_NAME|ID|IS_DEFAULT|IS_COMPILED|SORTLEN|
+------------------+------------------+--+----------+-----------+-------+
|utf8_general_ci |utf8 |33|Yes |Yes |1 |
|utf8mb4_general_ci|utf8mb4 |45|Yes |Yes |1 |
+------------------+------------------+--+----------+-----------+-------+
# mysql 8.x
+------------------+------------------+---+----------+-----------+-------+-------------+
|COLLATION_NAME |CHARACTER_SET_NAME|ID |IS_DEFAULT|IS_COMPILED|SORTLEN|PAD_ATTRIBUTE|
+------------------+------------------+---+----------+-----------+-------+-------------+
|utf8_general_ci |utf8 |33 |Yes |Yes |1 |PAD SPACE |
|utf8mb4_general_ci|utf8mb4 |45 | |Yes |1 |PAD SPACE |
|utf8mb4_0900_ai_ci|utf8mb4 |255|Yes |Yes |0 |NO PAD |
+------------------+------------------+---+----------+-----------+-------+-------------+
8.x 에선 utf8mb4_0900_ai_ci 타입이 새로 생겼으며 PAD_ATTRIBUTE 라는 속성을 확인할 수 있습니다. utf8mb4_0900_ai_ci 은 MySQL의 기본 설정 값으로 각각에 대한 설명은 다음과 같습니다.
utf8mb4: 각 문자가 UTF-8 인코딩 체계에서 MaxByte 4로 저장된다는것을 의미합니다.0900: Unicode Collation Algorithm 버전을 나타냅니다. Unicode Collation Algorithm은 유니 코드 표준의 요구 사항을 준수하는 두 개의 유니 코드 문자열을 비교하는 데 사용되는 방법입니다.ai: 악센트를 구분하지 않음을 나타냅니다. 즉, 정렬 할 때 e, è, é, ê 및 ë 사이에는 차이가 없습니다ci: 대소 문자를 구분하지 않습니다. 이것은 정렬 할 때 p와 P 사이에 차이가 없다는 것입니다.
그러나 utf8mb4_0900_ai_ci 에선 치명적인 문제가 있는데 그건 바로 한글을 정상적으로 인식하지 못한다는데 있습니다.
# true, true
select '카' = 'ㅋㅏ', 'ㅋㅏ린' = '카린';
한글 뿐만이 아닌 영어외 다른 문자들의 경우 위와 같이 정상적으로 인식되지 않는 경우가 있기에 서비스에선 일반적으로 utf8_general_ci 로 설정하여 사용하는 것이 권장됩니다.
따라서 문자열을 삽입시 실수를 방지하기 위해 trim 하는 습관을 갖도록 노력이 필요해 보입니다.
댓글남기기