데이터 타입: 문자열, 숫자, 날짜

테이블 컬럼의 각 데이터 타입을 결정할 때는 아래의 사항을 유의해야 한다.

• 저장되는 값의 성격에 맞는 최적의 타입을 선정
• 가변 길이 컬럼은 최적의 길이를 지정
• 조인 조건으로 사용되는 컬럼은 똑같은 데이터 타입으로 선정

컬럼의 데이터 타입을 결정할 때 실제 저장되는 값의 특성을 고려하기 보다는 저장되는 값의 최대 길이를 기준으로 컬럼의 길이를 선택하는 것이 일반적이다. 하지만 컬럼의 크기가 너무 무분별하게 커진다면 디스크 공간을 많이 차지할 것이고 해당 컬럼이 인덱스라면 검색 효율도 떨어질 것이다.

 

☁️ 문자열 (CHAR, VARCHAR)

두 타입 모두 문자열을 저장한다는 공통점을 가진다. CHAR 타입은 고정길이이며, VARCHAR 타입은 가변길이로 관리된다. CHAR(1), VARCHAR(1) 은 모두 1바이트의 데이터 저장 공간이 아닌 1개의 문자를 의미한다. 즉, 각 문자열 컬럼의 캐릭터 셋에 따라 1바이트 ~ 4바이트의 저장공간이 할당될 수 있다는 의미이다.

• CHAR: 고정 길이, 실제 저장되는 컬럼 값에 따라 저장 공간의 크기가 변하지 않는다.
• VARCHAR: 가변 길이, 최대 길이를 선언하고 그 이하의 길이를 가지는 데이터가 저장되는 경우 실제 저장 공간이 줄어든다. 저장된 데이터의 크기가 얼마인지 별도로 저장해두는데 1~2바이트가 추가로 필요하다. VARCHAR의 크기를 변경할 때 저장된 데이터 크기가 255바이트 이하에서 256바이트 이상으로 변경되는 경우 저장된 데이터의 크기를 표시하기 위해 사용되는 저장 공간을 1바이트에서 2바이트로 늘려야 하므로 잠금이 필요하고 이는 성능에 영향을 끼칠 수 있다.

CHAR혹은 VARCHAR 타입을 선택할 때는 데이터의 저장과 변경을 유의하며 선택해야 한다. CHAR 타입의 경우 고정 길이이기에 값을 변경하는 작업은 간단하다. 하지만 VARCHAR 타입의 경우 데이터를 변경하기 위해 추가적인 작업이 필요하다.

 

만약 VARCHAR 타입에 “ABC” 라는 문자열이 저장되어 있는데 “ABCD”와 같이 길이가 더 큰 값으로 변경하려는 경우에 레코드 자체를 다른 공간으로 옮겨서 저장하는 Row migration 작업이 필요하다. 이는 성능에 영향을 미필 수 있으므로 데이터 변경을 잘 고려하여 문자열 타입을 결정해야 한다.

MySQL에서는 하나의 레코드에서 TEXT와 BLOB 타입을 제외한 칼럼의 전체 크기가 64KB를 초과할 수 없다. 테이블에 VARCHAR 타입의 칼럼 하나만 있다면 이 VARCHAR 타입은 최대 64KB 크기의 데이터를 저장할 수 있다. 하지만 이미 다른 칼럼에서 40KB의 크기를 사용하고 있다면 VARCHAR 타입은 24KB만 사용할 수 있다. 이때 24KB를 초과하는 크기의 VARCHAR 타입을 생성하려고 하면 에러가 발생하거나 자동으로 VARCHAR 타입이 TEXT 타입으로 대체된다. 그래서 칼럼을 새로 추가할 때는 VARCHAR 타입이 TEXT 타입으로 자동으로 변환되지 않았는지 확인해 보는 것이 좋다.

<Real MySQL 8.0 2> (백은빈.이성욱 지음) 중에서

 

문자열 집합과 환경 변수

MySQL 에서 사용할 수 있는 문자열 셋은 아래와 같이 확인할 수 있다.

보통 한글을 저장할 때는 euckr, 다국어 지원을 위해서는 utf8mb4를 사용한다.

MySQL에서 문자 집합을 설정할 수 있는 환경 변수값은 다음과 같다.

 

콜레이션

문자열 컬럼의 값에 대한 비교나 정렬 순서를 위한 규칙이다. 각 컬럼은 독자적인 문자열 집합과 콜레이션을 가질 수 있다. 각 컬럼에 문자열 집합과 콜레이션을 명시하지 않으면 환경 변수에 따라 정해지고, 문자열 집합의 디폴트 콜레이션이 적용된다.

콜레이션은 show collation 명령어로 확인할 수 있고 엄청 많다. Collation 컬럼 값의 마지막에 명시되어 있는 ci와 cs는 각각, ci(Case Insensitive; 대소문자를 구분하지 않음), cs(Case Sensitive; 대소문자를 구분함)을 의미한다. 콜레이션이 ci인 경우 모든 문자열이 대문자 혹은 소문자로 변경되어 저장되는 것이 아니고 비교나 정렬 작업을 수행 할 때만 대소문자를 무시한다는 의미이다.

 

비교방식

CHAR, VARCHAR 타입 모두 문자열을 비교할 때, 길이가 짧은 문자열 뒤에 공백을 추가하는 방식으로 문자열 길이를 통일 시킨 뒤 비교를 수행한다. 따라서 아래와 같은 결과를 반환한다.

SELECT 'ABC'='ABC   ' AS is_equal; -- true
SELECT 'ABC'='   ABC' AS is_equal; -- false

하지만 콜레이션의 pad_attribute 값이 NO PAD인 경우에는 두 경우 모두 다르 false를 반환한다.

 

 

☁️ 숫자

숫자랑 저장하는 타입은 참값(INTEGER, ~INT, DECIMAL)과 근삿값(FLOAT, DOUBLE) 타입으로 나눌 수 있다. 또한 DECIMAL 은 십진 표기법으로 저장되고 나머지 타입은 이진 표기법으로 저장된다.

정수

MySQL에서는 INT 와 BIGINT를 주로 사용한다. 각 숫자형 데이터 타입은 아래와 같다.

TINYINT    |   1 Byte
SMALLINT   |   2 Byte
MEDIUMINT  |   3 Byte
INT        |   4 Byte
BIGINT     |   8 Byte

만약 컬럼에 음수를 저장하지 않아도 되는 경우(AUTO_INCREMENT 등)에는 UNSIGNED 옵션을 붙여 양수만 저장하게 설정하는 것이 더 많은 수를 저장할 수 있어 효율적이다.

 

부동 소수점

부동 소수점은 근사 값을 저장한다. 따라서 동등 비교는 사용할 수 없다. 만약 부동 소수점 값을 저장해야 한다면 유료 소수점의 자릿수만큼 10을 곱해서 정수 값으로 만든 다음, 정수 타입의 컬럼에 저장하는 방식을 사용할 수도 있다. 해당 값을 조회할 때는 10을 곱한 만큼 나눈 값을 사용하면 된다.

 

AUTO_INCREMENT

테이블의 프라이머리 키를 구성하는 컬럼의 크기가 너무 크거나, 프라이머리 키로 사용할 만한 컬럼이 마땅치 않은 경우 숫자 타입에 자동 증가 옵션을 추가하여 인조 키를 생성할 수 있다.

InnoDB에서 AUTO_INCREMENT 옵션을 사용한 컬럼은 반드시 프라이머리 키나 유니크 인덱스의 시작 컬럼으로 정의해야 한다. 또한 하나의 테이블에 하나의 AUTO_INCREMENT 컬럼을 가질 수 있다. 효율적인 검색을 위해 제약사항을 걸어둔 것 같다.

 

 

☁️ 날짜와 시간

MySQL에서는 날짜만 혹은 시간만 저장하거나 합쳐서 하나의 컬럼에 저장할 수도 있다. 또한 TIME, DATETIME, TIMESTAMP 타입은 밀리초 단위를 저장할 수 있다.

-- MySQL 5.6.4 이후 버전 기준
YEAR       | 1 Byte
DATE       | 3 Byte
TIME       | 3 Byte + 밀리초 단위 저장 공간
DATETIME   | 5 Byte + 밀리초 단위 저장 공간
TIMESTAMP  | 4 Byte + 밀리초 단위 저장 공간

-- 밀리초 단위 저장 공간
없음        | 0 Byte
1, 2       | 1 Byte
3, 4       | 2 Byte
5, 6       | 3 Byte

예를 들어 DATETIME(6) 타입은 밀리초를 6자리까지 저장한다는 의미이고, 5바이트(DATETIME 타입) + 3바이트(밀리초 저장 공간) = 8바이트를 사용한다. 괄호에 아무런 숫자를 명시하지 않는다면 자동 으로 0이다.

 

날짜 타입의 컬럼은 DBMS 타임존과 관계없이 클라이언트로부터 입력된 값을 그대로 저장하고 그대로 반환한다. 하지만 TIMESTAMP는 항상 UTC(전세계 동일 시간) 로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다.

 

자바 애플리케이션에서 JDBC를 사용하여 DBMS의 타임존을 명시하고 JVM의 타임존을 명시하면 DATETIME, TIMESTAMP 모두 JVM의 타임존에 맞게 변경하여 반환해준다. ORM을 사용할 때는 해당 방식이 어떻게 적용되는지 확인할 필요가 있다.

서버의 타임존을 확인하거나 변경하는 방법은 다음과 같다.

show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | KST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

SET time_zone='America/Los_Angeles';

 

시간 정보 자동 변경

DEFAULT CURRENT_TIMESTAMP 옵션은 레코드가 INSERT될 때의 시점을 자동으로 업데이트하며, ON UPDATE CURRENT_TIMESTAMP 옵션은 해당 레코드가 UPDATE될 때의 시점을 자동으로 업데이트하게 해준다.

CREATE TABLE tb_autoupdate (         
    id BIGINT NOT NULL AUTO_INCREMENT,
    title VARCHAR(20),
    created_at_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at_dt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

 

☁️ 결론

데이터 타입 선정:

데이터 타입은 무분별하게 길어지는 경우 디스크 공간, 인덱스 크기 증가로 이어질 수 있기에 실제 저장되는 데이터에 대해 문제가 발생하지 않을 정도의 적당한 크기로 지정하는 것이 좋다. 조인 조건으로 사용되는 컬럼은 똑같은 데이터 타입으로 선정해야 한다.

 

문자열:

VARCHAR 타입의 경우 이미 저장되어 있는 값보다 더 큰 값으로 데이터를 변경하려는 경우 레코드를 다른 공간으로 옮겨서 저장하는 Row migration 작업을 수행하여야 하므로 변경에 유의하여 문자열 타입을 선택해야 한다.

 

콜레이션을 확인하여 비교와 정렬에 있어 대소문자를 구분하는지 아닌지를 파악하고 상황에 맞게 변경해야 한다. 그렇지 않으면 예상하지 못한 쿼리 결과가 발생할 수 있다.

 

문자열 타입은 문자 집합과 콜레이션이 모두 동일해야 같은 타입이라고 볼 수 있다. 즉, 문자 집합 혹은 콜레이션이 다른 경우 비교작업에서 문자 집합 혹은 콜레이션의 변경 작업이 필요하므로 인덱스를 효율적으로 사용할 수 없을 때가 많으니 주의해야 한다.

 

숫자:

음수 값을 저장하지 않는 컬럼의 경우 UNSIGNED 옵션을 통해 저장할 수 있는 값의 범위를 증가시키자.

부동 소수점을 저장하는 컬럼은 근사 값을 저장하기에 동등 비교를 수행할 수 없다. 따라서 유효한 소수점 까지 자른 뒤 소수점 길이 만큼의 10을 곱해 정수로 관리하는 방법도 존재한다.

 

날짜와 시간:

MySQL 5.6 부터는 DATETIME 타입과 TIMESTAMP 타입은 time_zone 시스템 변수의 타임존에 따라 시간 정보를 저장할지(DATETIME), 타임존에 의존하지 않는 UTC(TIMESTAMP)로 저장할지의 차이를 제외하면 모두 동일하게 동작한다.