MySQL에서 문자열 다루기

개요

• MySQL에서 문자열을 다루는 방법으로는 여러가지가 존재합니다. 먼저 컬럼을 정의할 수 있는 데이터 타입으로 CHAR, VARCHAR, TEXT 등이 있고, Collation을 통해 문자열 정렬이나 비교 연산이 달라질 수 있습니다. 이번에는 MySQL에서 문자열을 어떻게 다룰 수 있는지 알아보겠습니다.

목차

• CHAR과 VARCHAR, TEXT란?
• CHAR과 VARCHAR 뒤에 붙는 숫자의 의미
• Collation이란?

CHAR과 VARCHAR, TEXT란?

• 앞서 언급했듯 컬럼을 정의하는 데이터 타입으로 CHAR, VARCHAR, TEXT가 있습니다. 이 3가지 타입을 비교해보겠습니다.

CHAR

• 고정형의 크기를 가지며, 추가적인 연산이 필요하지 않아 검색 속도 및 읽히는 속도가 VARCHAR에 비해 빠릅니다.
• 최대 255Byte까지 저장이 가능합니다.
• 글자수가 고정되는 경우에 사용하면 적합합니다.
• 크기보다 작은 문자열이 들어올 경우 나머지는 공백으로 채웁니다.

VARCHAR

• DDL에 선언한 크기보다 데이터의 크기가 작을 경우 그 크기만큼의 공간만을 차지하여 공간적으로 효율적입니다.
• 최대 65,535byte까지 길이 저장이 가능합니다. 다만 row size의 최대 크기가 스토리지 엔진이 다루는 페이지의 크기가 넘지 않도록 주의해야 합니다.

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

• 길이에 대한 정보를 저장하는 Header Byte가 추가적으로 1~2byte가 필요합니다.

TEXT

• 최대 65,535byte까지 길이 저장이 가능합니다.
• LOB 으로 취급되어, 데이터를 내부에 저장하지 않고 Off-Page라는 외부 공간에 저장합니다.

CHAR과 VARCHAR의 공통점

• 문자열을 데이터 타입으로 가집니다.
• DDL에 선언한 크기를 초과했을 경우에는 문자열을 잘라서 선언한 크기만큼만 저장합니다.

TEXT와 VARCHAR

• TEXT는 LOB 로 취급되기 때문에 데이터를 읽을 시 record라는 미리 할당해 놓은 메모리가 아닌, 메모리를 새로 할당해서 전달합니다. 이는 비용이 많이 드는 작업입니다.
• 근데 VARCHAR도 최대 약 6만 바이트까지 크기 지정이 가능하기 때문에, 큰 데이터(16KB 이상)가 VARCHAR에 들어가는 경우 Off-Page라는 외부 공간에 저장하여 LOB 와 동일한 방식으로 동작합니다.

CHAR과 VARCHAR 뒤에 붙는 숫자의 의미

• CHAR과 VARCHAR을 사용한 컬럼은 뒤에 숫자가 붙을 수 있습니다. 이 숫자는 컬럼이 담을 수 있는 데이터의 크기를 의미하는 것인데, 어떤 단위로 데이터의 크기를 지정할까요?
• MySQL 4 버젼 이하의 버젼에서는 숫자가 바이트의 크기를 의미했습니다. 따라서 CHAR(255) 는 255바이트의 문자열을 저장할 수 있음을 의미합니다.
• MySQL 5버젼 이상의 버젼은 숫자가 문자열의 길이를 의미합니다. 따라서 CHAR(255) 인 경우 255 길이의 문자열을 저장할 수 있음을 의미합니다.

Collation이란?

• MySQL에서 문자열을 다루는데 영향을 주는 요소 중 마지막으로 소개할 요소는 Collation입니다. Collation은 문자열을 비교하고 정렬하는 규칙 집합이며, LIKE, =, ORDER BY 등의 연산에 적용되는 규칙입니다.
• charset, 악센트, 대소문자 구분 등의 정보가 포함되어 있으며, 같은 charset이라도 나머지 규칙에 따라 문자의 구분 방법이 달라질 수 있습니다.

utf8mb4_0900_ai_ci - utf8mb4 : 캐릭터셋 매핑 - 0900 : version-9.0.0 UCA 표준을 따름 - ai : accent insensitive (악센트 구분) - ci : case insensitive (대소문자 구분)

Collation이 미치는 영향

• 먼저 charset을 봅시다. 한글을 포함하여 한국에서 많이 사용하는 charset은 utf8로, MySQL 8 이후의 utf8mb3에 해당합니다.
  • utf8mb3은 1~3bytes로 문자를 저장하며, 따라서 VARCHAR나 CHAR로만 컬럼을 구성한다고 했을 때, 65535/3 = 21845글자 정도를 이론적으로 사용할 수 있습니다.
  • utf8mb4는 1~4byte의 문자를 저장하며, 이론적으로 약 16383 글자 정도를 저장할 수 있습니다.
• 그 다음 collation 알고리즘을 알아보면 general, unicode 등이 존재합니다.
  • general : 일반적인 정렬 및 비교 규칙을 적용합니다.
  • unicode: Unicode 표준에 따른 정렬 및 비교 규칙을 적용합니다.
• 마지막으로 suffix(접미사)들을 알아봅시다. 접미사는 문자열 비교 및 정렬의 방식에 대한 추가적인 세부 사항을 제공합니다.
  • _ci: 대소문자 구분하지 않습니다.
  • _cs: 대소문자를 구분합니다.
  • _bin: 바이너리 값을 비교합니다.
  • _ai: 악센트를 구분하지 않습니다.
  • _as: 악센트를 구분합니다.