MySQL 의 데이터 타입 정리 (15.1 ~ 15.6)

15. 1 문자열(CHAR 와 VARCHAR)

CHAR 타입

• 문자열 저장하는 데이터 타입
• 저장 공간 크기가 고정

VARCHAR 타입

• 가변 길이를 저장하며 데이터 길이를 저장하기 위해 1~2바이트의 저장 공간이 추가로 더 필요
  • VARCHAR(10)에 'Hello' 란 5바이트 저장시 [5][H][e][l][l][o] 로 총 6바이트로 저장
  • 데이터 길이가 255바이트 이하면 1바이트만 사용하고, 256 바이트 이상이면 2바이트를 사용한다. 그래서, VARCHAR 타입의 최대 길이는 65,536 바이트 이상으로 설정할 수 없다.

💡VARCHAR 타입 사용 시 주의점
MySQL 은 하나의 레코드에서 TEXT 와 BLOB 타입을 제외한 칼럼의 전체 크기가 64KB(65,536 바이트)를 초과할 수 없다. 이미 다른 칼럼에서 40KB 의 크기를 사용하고 있다면 VARCHAR 타입은 24KB만 사용할 수 있다.

만약, 24KB 를 초과하는 VARCHAR 타입을 생성하면 에러가 발생하거나 자동으로 VARCHAR 타입이 TEXT 타입으로 대체된다. 그래서, 칼럼을 새로 추가할 때는 VARCHAR 타입이 TEXT 타입으로 자동으로 변환되지 않는지 확인하는 것이 좋다.

 

CHAR 와 VARCHAR 타입의 선택 기준

- 특정 칼럼의 VARCHAR(10) 가 4바이트("ABCD")만을 저장할 수 있는 구조일 때, 칼럼의 값이 변경되어 5바이트("ABCDE")를 저장하려고 하면 레코드 자체를 다른 공간으로 옮겨서 저장한다. 이러한 레코드의 이동이나 분리는 CHAR타입으로 발생하는 2~3바이트 공간 낭비보다 더 큰 자원을 낭비한다.

 

그래서, 타입을 선택할 때는 '저장되는 문자열의 길이가 대게 비슷한지' 와 '칼럼의 값이 자주 변경되는지' 를 고려해야 한다.

 

주민등록번호 같이 항상 길이가 고정적이거나 값이 2~3바이트 차이가 나더라도 자주 변경될 수 있는 부서 번호나 게시물의 상태 값은 CHAR 타입을 사용하는 것이 좋다.

💡MySQL에서 CHAR(10), VARCHAR(10) 에 존재하는 '10' 의 의미
바이트 크기가 아닌 문자의 수를 의미한다.

- 영어를 포함한 서구권 언어는 각 문자는 1바이트를 사용. 즉, 10글자를 저장하고, 10바이트를 사용
- 한국어, 일본어인 아시아권 언어는 각 문자가 최대 2바이트를 사용. 즉, 10글자를 저장하고 최대 20바이트를 사용
- UTF-8 과 같은 유니코드는 최대 4바이트를 사용. 즉, 10글자를 저장하고 최대 40바이트까지 사용

 

문자열 저장 공간과 스키마 변경

Case 1. VARCHAR(62) → VARCHAR(63) 로 스키마 변경

A. 잠금 없이 매우 빠르게 스키마가 변경된다.

 

Case 2. VARCHAR(63) → VARCHAR(64) 로 스키마 변경  

읽기 잠금이 걸리며, 스키마 변경동안 쓰기 작업이 불가하다.(기본 문자 집합인 utf8mb4(UTF-8) 문자집합을 사용한다고 가정)

 

▶ 왜 다를까?

VARCHAR(63) 의 바이트는 (4 * 63 = 252바이트)로 저장된 문자열 길이를 저장하는데 1바이트를 사용한다.

하지만, VARCHAR(64) 의 바이트는 (256바이트로 문자 길이를 저장하는데 2바이트로 바꿔야 하고, 문자열 길이를 저장하는 공간의 크기가 바뀌면 MySQL 은 스키마를 변경하는 동안 읽기 잠금을 걸어 쓰기 작업을 막는 메커니즘을 가지고 있다.

 

문자집합 소개

• latin 계열 : 알파벳, 숫자, 키보드의 특수 문자로만 구성된 문자열 저장하는데 사용
• euckr : 한국어 전용, 모든 글자는 1~2바이트 사용
• utf8mb4 : 다국어 문자 포함, 한 글자는 1~4바이트 사용, 기본값이다.

사용 가능한 문자 집합 보는 명령어 → SHOW CHARACTER SET;

 

콜레이션(Collation)

개념

• 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미
• MySQL의 모든 문자열 타입의 칼럼은 독립적인 문자 집합과 콜레이션을 가진다.
• SHOW COLLATION 명령을 통해 확인하며, DEFAULT 에 Yes 가 적힌 콜레이션이 해당 문자 집합의 기본 값이다.

콜레이션 이름은 규칙에 따라 정의되어 있다.

• 3개의 파트로 구성된 콜레이션 이름
  • 첫 번쨰 파트는 문자 집합의 이름
  • 두 번째 파트는 해당 문자 집합의 하위 분류
  • 세 번째 파트는 대문자나 소문자의 구분 여부로 "ci" 이면 대소문자 구분X, "cs"면 대소문자를 구분
• 2개의 파트로 구성된 콜레이션 이름
  • 첫 번째 파트는 문자 집합의 이름
  • 두 번째 파트는 항상"bin" 키워드 사용되며, "bin" 은 이진 데이터를 의미하며 비교 및 정렬은 실제 문자 데이터의 바이트 값을 기준으로 수행된다.

자주 사용되는 콜레이션 소개

• 자주 사용하는 latin1 , euckr, utf8mb4 문자 집합의 콜레이션은 latin1_swedish_ci, euckr_korean_ci, utf8mb4_0900_ai_ci 이다.
• 이들은 모두 "ci" 라서, 대소문자를 구분해서 비교하거나 정렬 하는 칼럼에서는 "cs" 계열이나 "bin" 계열의 콜레이션을 명시적으로 지정해야 한다.

콜레이션에 따라 문자열 비교와 인덱스에 끼치는 영향이 다르다.

• 타입의 이름, 문자열의 길이, 문자 집합, 콜레이션 모든 것이 일치해야 똑같은 타입이라 할 수 있다.
• 문자 집합과 콜레이션이 모두 일치해야 JOIN 이나 WHERE 조건에서 인덱스를 효율적으로 사용할 수 있다.
• 콜레이션은 DB수준, 테이블 수준, 칼럼 수준에서 개별적으로 설정이 가능하다.

> CREATE DB db_test CHARACTER SET=utf8mb4;

> CREATE TABLE tb_member (
    member_id VARCHAR(20) NOT NULL COLLATE latin1_general_cs,
    member_name VARCHAR(20) NOT NULL COLLATE utf8_bin
    ...
);

 

각 컬럼의 문자 집합이나 콜레이션을 정확히 확인하려면 다음과 같은 쿼리를 실행한다.

> SELECT table_name, column_name, column_type, character_set_name, collation_name
  FROM information_shema.columns
  WHERE table_schema = '{DB이름}' AND table_name='{테이블이름}';

 

MySQL 5.7버전에서 8.0 버전으로 업그레이드 시 알아야 할 주의점

• utf8mb4 문자 집합의 기본 콜레이션이 MySQL 8.0 이 되면서 utf8mb4_general_ci 에서 utf8mb4_0900_ai_ci 로 바뀌었다.
• 그래서, MySQL 8.0 버전 업그레이드 시 콜레이션이 일치하지 않아 에러가 발생하거나 성능이 저하될 수 있기 때문에 문자열 집합의 콜레이션 일치를 해줘야 한다.
• default_collation_for_utf8mb4 란 시스템 변수에 "utf8mb4_general_ci" 을 설정하면 8.0 이후의 콜레이션에 general_ci 를 임시적으로 적용할 수 있다. 영구적으로 하고싶다면 설정 파일(my.cnf)에 콜레이션 시스템 변수를 genenral_ci로 고정하자.

문자열 비교

utf8mb4 문자 집합이 UCA 버전 9.0.0 (utf8mb4_0900_ac_ci) 을 지원하면서 문자열 뒤에 붙은 공백 문자들에 대한 비교 방식이 다르다.

utf8mb4 문자 집합을 사용할 경우, information_schmea DB의 COLLATIONS 뷰에서 PAD_ATTRIBUTE 칼럼의 값이 "PAD SPACE" 인지 "NO PAD" 인지 아래 쿼리를 실행하여 확인할 수 있다.

> SELECT collation_name, pad_attribute
  FROM information_schema.COLLATIONS
  WHERE collation_name LIKE 'utf8mb4%';

값이 "PAD SPACE" 라면 비교 대상 문자열의 길이가 같아지도록 문자열 뒤에 공백을 추가해서 비교를 수행하고, "NO PAD" 라면 공백을 추가하지 않고 비교를 수행한다. uft8mb4 의 기본 콜레이션인 uft8mb4_0900_ai_ci 는 "NO PAD" 이다.

 

15.2 숫자

정수 타입 소개

• TINYINT(1 바이트), SMALLINT(2바이트), MEDIUMINT(3바이트), INT(4바이트), BIGINT(8바이트) 가 있다.
• AUTO_INCREMENT 칼럼과 같이 음수가 될 수 없는 값을 저장할 때, UNSIGNED 옵션을 명시하면 더 큰 값을 저장할 수 있다. 명시하지 않으면 기본적으로 음수와 양수를 저장하는 SIGNED 가 된다.

부동 소수점

동등 비교가 불가능하다. 정밀도를 명시하지 않으면 4바이트를 사용해 유효 자릿수를 8개까지 유지한다.

 

DECIMAL

• 소수점 이하의 값까지 정확하게 관리한다.
• 십진 표기법을 사용하는 DECIMAL 타입은 이진 표기법을 사용하는 INTEGER 및 다른 INT타입보다 저장 공간을 2배 이상 필요로 한다.
• DECIMAL 타입은 65자리 숫자까지 표현가능해서 BIGINT 로도 저장할 수 없는 값을 저장할 수 있다.
• DECIMAL(20, 5) 라고 정의하면 정수부를 15자리, 소수부를 5자리 까지 저장하지만 DECIMAL(20) 은 정수부만 20자리까지 저장한다.

 

15.3 날짜와 시간

날짜와 시간 타입 소개

• YEAR, DATE, TIME, DATETIME, TIMESTAMP 가 있다.
• DATETIME(6) 에서 6은 밀리초 단위 자릿수 이다. 만약, DATETIME() 로 날짜,시간을 가져오면 밀리초 단위는 0으로 반환된다.

DATETIME 과 TIMESTAMP 의 차이 

• DATETIME 과 DATE 타입은 DBMS 커넥션의 타임존과 관계없이 입력된 값을 그대로 저장하고 조회한다.
• TIMESTAMP 는 항상 UTC 타임존으로 저장되서, 타임존이 달라지면 값이 자동으로 보정된다.

응용 프로그램과 MySQL 서버의 타임존 관계

JDBC 드라이버는 날짜 및 시간 정보를 MySQL 타임존에서 JVM의 타임존으로 변환해서 출력한다.

즉, ORM 코드 내부적으로 값을 자동으로 페치해서 응용 프로그램 코드를 반환하기 때문에 ORM 이 어떤 JDBC API 를 이용하는지, 타임존 변환이 기대하는 대로 자동 변환하는지 테스트 해봐야 한다.

 

MySQL 서버의 타임존 변수는 system_time_zone 과 time_zone 이 있다.

• system_time_zone 은 OS 의 타임존을 그대로 상속받는다
• time_zone 은 MySQL 서버로 연결하는 모든 클라이언트 커넥션의 기본 타임존이다.

MySQL 서버의 시간 관련 처리는 time_zone 변수의 타임존에 영향을 받고, 만약 time_zone 변수의 값이 SYSTEM 으로 설정돼 있다면 system_time_zone 의 영향을 받는다.

 

MySQL 5.6 버전부터는 DATETIME 과 TIMESTAMP 타입은 time_zone 변수의 타임존으로 저장할지, UTC 로 저장할지의 차이만 남고 모든 것이 같아졌다.

 

15.4 ENUM

ENUM 타입의 특징

• MySQL 서버가 실제로 값을 디스크나 메모리에 저장할 때는 사용자로부터 요청된 문자열이 아닌 그 값에 매핑된 정숫값을 사용한다.(ENUM은 정수 타입이다)
• ENUM 타입에 사용할 수 있는 최대 아이템 개수는 65,535개이며, 아이템 개수가 255개 미만이면 ENUM 타입은 저장 공간이 1바이트이고, 그 이상인 경우 2바이트까지 사용한다.

ENUM 타입의 장점

• ENUM타입은 저장해야 하는 아이템 값(문자열)이 길면 길수록 저장 공간을 더 많이 절약할 수 있다.
• 이는 인덱스 페이지 및 디스크 크기에도 영향을 주어 몇 GB 를 아낄 수 있다.

ENUM 타입의 단점

• 칼럼에 저장되는 문자열 값이 테이블의 구조(메타 정보)가 되면서 기존 ENUM 타입에 새로운 아이템을 추가한다면 테이블의 구조를 변경해야 한다.
• 5.6버전부터는 ENUM 타입 마지막에 새로운 아이템을 추가한다면 메타데이터 변경만으로 완료되지만, 기존 ENUM타입의 아이템들이 순서가 변경되거나 중간에 새로운 아이템이 추가되면 읽기 잠금까지 필요하다.

ENUM 타입의 정렬

ENUM타입은 문자열 타입이 아닌 정수 타입의 칼럼이기 때문에 정렬은 테이블이 생성될 때의 ENUM 타입의 문자열 값으로 졍렬된다.

 

15.5 TEXT 와 BLOB

공통점

• MySQL 의 대량의 데이터를 저장하는 타입에 사용

차이점

• TEXT 는 문자열을 저장하는 대용량 칼럼이라서 문자 집합이나 콜레이션을 가짐.
• BLOB 는 이진 데이터 타입이라서 별도의 문자 집합이나 콜레이션을 가지지 않는다.

그래서, 칼럼 하나에 저장되는 문자열이 이진 값의 길이가 예측할 수 없이 클 때 두 타입을 사용한다.

 

TEXT 와 BLOB 는 저장 가능한 최대 길이에 따라서 4가지 타입으로 구분된다.

데이터 타입	필요 저장 공간 (L = 저장하고자 하는 데이터 바이트 수)	저장 가능한 최대 바이트 수
TINYTEXT, TINYBLOB	L + 1 바이트	2의 8승 - 1 (255)
TEXT, BLOB	L + 2 바이트	2의 16승 - 1 (65,535)
MEDIUMTEXT, MEDIUMBLOB	L + 3 바이트	2의 24승 - 1 (16,777,215)
LONGTEXT, LONGBLOB	L + 4 바이트	2의 32승 - 1 (4,294,967,295)

 

TEXT 와 BLOB 이 디스크에 저장되는 방식

MySQL은 하나의 레코드 크기가 페이지 단위를 넘기지 않게 저장하려고 하며, 만약 이 두 타입에 의해 레코드 크기가 페이지 단위를 넘어간다면 외부페이지에 해당 타입을 저장하고, 실제 레코드의 칼럼은 외부페이지의 참조값을 가지고 있게 된다.

 

15.6 공간 데이터 타입

소개

• MySQL 서버는 OpenGIS 에서 제시하는 표준을 준수
• OpenGIS 에서 제공하는 WKT(Well Known Text) 또는 WKB(Well Known Binary) 를 이용해 공간 데이터를 관리

타입종류

• POINT - 하나의 점 정보
• LINESTRING - 하나의 라인
• POLYGON - 하나의 다각형
• GEOMETRY - POINT, LINESTRING, POLYGON 의 수퍼타입으로 3개의 타입 모두 저장 가능
• MULTIPOINT - 여러 개의 POINT 저장
• MULTILINESTRING  - 여러 개의 LINESTRING  저장
• MULTIPOLYGON  - 여러 개의 POLYGON  저장
• GEOMETRYCOLLECTION - MULTIPOINT ~ MULTIPOLYGON 의 수퍼타입으로 3개의 타입 모두 저장 가능

특징

• 공간데이터 타입은 BLOB 객체로 관리된다.
• BLOB 타입은 실제 저장하고 있는 데이터가 크지 않다면 MySQL 서버는 공간 데이터 칼럼을 별도의 외부 페이지(Off-page)로 저장하지 않아서 성능을 걱정하지 않아도 된다.
• POINT 객체 생성 예시 : ST_PointFromText('POINT(x, y)') → SRID 가 0인 객체를 생성한다.

 

 

 

 

참고 : Real MySQL 8.0