sql에서 collation이 왜 중요할까요?

 테이블에서 collation은 어떤 것일까요? 사실, 저는 별 게 아니겠구나. 라고만 생각하였습니다. 그냥 인코딩에 대한 것만 중요하겠거니, 싶었습니다. 결론부터 말하자면, 그런 제 생각은 오만했다는 것이였습니다.

 

---

 testVC 테이블에 있는 데이터를 모두 조회해 보겠습니다. 이것을 str 오름차순으로 정렬해 보겠습니다.

 

 

 heroes, HEROES, kiwoom, KIWOOM, kIWOOM, Lotte giant, ... 이렇게 8개의 레코드가 있습니다. 여기서 의문점이 들 수 있습니다. 왜 대문자 소문자 구분없이 sorting이 되어 있지?

 

 

 이는 테이블의 Collation을 보면 알 수 있습니다. utf8mb4_*_ai_ci. 이것은 Accent와 Case 둘 다 INSensitive하다는 것입니다. 앞에 IN이 붙었으니까, Sensitive와는 반대의 뜻을 가진다는 것을 쉽게 유추해 볼 수 있습니다. 그리고 sensitive는 영어 사전에서 찾아보면 여러 뜻이 있는데, 여기에서는 '민감한'이라는 뜻으로 보시면 됩니다.

 

 즉, Accent와 Case (대소문자)에 민감하지 않으니, 구별하지 않는다. 정도로 보시면 됩니다. 그러면, k 다음에 왜 L이나 l이 왔는지도 쉽게 구분을 하실 수 있을 겁니다.

 

---

 이제 testVC2라는 테이블을 만들어 보겠습니다. 서버에 접속을 해서 root 계정으로 만들겠습니다.

 

 여기서 중요한 것은 collate가 bin이라는 것입니다. bin이라고 하면, binary의 줄임말인데요. 흔히 아시는 binary search가 이진 탐색입니다. 즉, 이진 데이터를 기준으로 뭔가를 판단하겠다는 이야기입니다.

 

 

 testVC에 있는 데이터를 모조리 다 testVC2에 넣겠습니다. 이것은 그냥, insert select 문으로 간단하게 처리할 수 있습니다. 여기에 where 절이 연결되는 서브쿼리는 상당히 많이 쓰는 패턴이니 익숙해지시면 좋을 듯 싶습니다.

 

 

 이제 똑같은 방법으로, testVC2에 있는 내용을 모두 출력할 건데요. str 오름차순으로 보도록 하겠습니다.

 

 

 그러면, 아까와는 다르게, 대문자로 시작하는 것들이 먼저 출력이 되고, 다음에 소문자로 시작하는 heroes, kIWOOM, lotte giant가 출력이 됨을 알 수 있는데요. 이는 왜 그럴까요? 판단 기준이 binary이기 때문입니다. HEX 함수를 이용해서 보도록 하겠습니다.

 

 

 이 쿼리를 실행시켜 보겠습니다.

 

 

 그러면, 이런 식으로 나오는데요. HEX 값을 보니 더 명확하게 어떤 것이 정렬 기준인지를 볼 수 있습니다. HEX를 기준으로 보면 48보다는 4C가 뒤에 있고, 4C보다는 68이, 68보다는 6B가 뒤에 있음을 알 수 있습니다. 그러니, 저렇게 출력되는 것도 이상한 일이 아닙니다. 더 정확하게 말하면 비교 기준이 binary이기 때문에 벌어진 현상입니다.

 

 

---

 이제 testVC3 테이블을 만들어 보겠습니다.

 

 당연하게도 다른 것은 모두 같지만, collate만 다릅니다. 이번에는 as_cs인데요. Accent와 Case가 모두 sensitive합니다. 즉, 대소문자와 악센트를 구분한다. 정도로 해석하시면 됩니다.

 

 

 마찬가지로, testVC2에 있는 내용을 모두 testVC3에 옮길려면, 이런 식으로 insert select 쿼리를 쓰면 됩니다.

 

 

 다음에 testVC3에 있는 내용을 str 오름차순으로 출력해 보도록 하겠습니다.

 

 

 이것만 봐서는 ai_ci랑 별 차이도 없어 보입니다만. l과 L이 나온 순서의 차이가 있었습니다. 이건 대소문자를 구별하느냐, 그렇지 않냐의 차이가 있기 때문입니다. 그런데, 이것을 가지고는 와닿을 거 같지는 않습니다. 다른 상황을 들어보겠습니다. where 절에서 동등 비교를, 그러니까 equal 비교를 하는 건 어떨까요?

 

 

 str이 Lotte giant와 같은 것을 testVC에서 뽑아보겠습니다.

 

 

 그러면 이렇게 3개가 나옵니다. 대소문자를 구별하지 않는다면 Lotte giant나, LOTTE GIANT나 같기 때문입니다.

 

 

 그런데, testVC3은 어떨까요? where 절은 같습니다.

 

 

 그런데, 하나만 출력되었습니다. 그 이유는, LOTTE GIANT와 Lotte giant는 대소문자의 구별이 있다면 다르기 때문입니다. 문자열을 비교하는 기준을 알려주는 collation은 어떻게 설정이 되어 있느냐에 따라서, 같은 쿼리라도 다르게 결과가 나올 수 있다는 의미입니다.

 

 제가 설명한 3개의 collation 말고도 많은 것들이 있으니, 그에 대해서도 조사를 해 보는 것도 괜찮은 듯 싶네요.