[MySQL] Error 1267: Illegal mix of collations 해결 방법

에러 내용

• logger=tsdb.mysql t=2023-09-13T04:45:43.605367314Z level=error msg="Query error" error="Error 1267: Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='”

에러 설명

• MySQL에서 콜레이션(collation)은 데이터베이스에서 문자열을 저장하고 비교하는 방법을 정의하는 규칙 세트입니다.
• 즉, 문자열을 어떻게 비교할지, 정렬할지, 대소문자를 어떻게 다룰지와 같은 문자열 처리 규칙을 결정합니다.
• 간단한 예를 통해 설명해보겠습니다:
  • 문자열 비교
    • 콜레이션은 두 문자열을 비교할 때 사용되며, 예를 들어 "apple"과 "Banana"라는 두 문자열을 비교할 때 대소문자를 구분할 것인지, 구분하지 않을 것인지를 결정합니다.
  • 문자열 정렬
    • 데이터베이스에서 결과를 정렬할 때 콜레이션은 문자열의 순서를 결정합니다.
    • 예를 들어 "apple", "Banana", "cherry"를 정렬할 때, 콜레이션에 따라 정렬 순서가 달라질 수 있습니다.
  • 대소문자 구분
    • 일부 콜레이션은 대소문자를 구분하지 않고 문자열을 처리하며, 다른 콜레이션은 대소문자를 구분합니다.
    • 이것은 "apple"과 "Apple"이 동일한 문자열로 취급되는지 여부를 결정합니다.
• 따라서, 서로 다른 콜레이션을 가진 문자열을 비교하려고 할 때 MySQL은 이러한 혼합된 콜레이션을 처리하지 못하고 오류를 발생시키게 됩니다.

해결 방법

• 이 오류를 해결하려면 다음 두 가지 방법 중 하나를 선택할 수 있습니다.

  • 쿼리에서 콜레이션을 직접 지정해 일치시키기

      SELECT *
      FROM table
      WHERE name COLLATE utf8mb4_unicode_ci = 'unho'

    • "utf8mb4_unicode_ci"라는 콜레이션은 유니코드 문자열을 대소문자를 구분하지 않고 비교하고, 자연어에 따라 정렬한다는 의미입니다.
    • 이 콜레이션을 사용하면 "apple"과 "Apple"이 동일한 문자열로 취급되며, 문자열 정렬도 자연어에 맞게 이루어집니다.

  • 데이터베이스 레벨에서 콜레이션 일치시키기