티스토리 뷰

칼럼의 데이터 타입 선정

칼럼의 데이터 타입과 길이를 선정할 때 주의해야 할 점은 다음과 같다.

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

너무 긴 길이는 저장 공간의 낭비를 초래하고, 너무 짧은 길이는 추후 스키마 변경 작업 시 많은 부담을 가져오게 되므로 초기 스키마 설정에서 신중하게 조사 후 데이터 타입을 결정해야 한다.


문자열 (CHAR, VARCHAR)

CHAR와 VARCHAR의 차이

CHAR와 VARCHAR의 가장 큰 차이점은 고정 길이인지 가변 길이인지의 여부다.

  • 고정 길이
    • 실제 입력되는 칼럼 값의 길이에 따라 사용하는 저장 공간의 크기가 변하지 않는다.
  • 가변 길이
    • 최대로 저장할 수 있는 값의 길이는 제한돼 있지만, 그 이하 크기의 값이 저장되면 그만큼 저장 공간이 줄어든다.
    • 저장된 값의 유효 크기를 별도로 저장해둬야 하므로 1~2바이트의 저장 공간이 추가로 더 필요하다.

CHAR 타입과 VARCHAR 타입 결정 시 가장 중요한 기준은 다음과 같다.

  • 저장되는 문자열의 길이가 대개 비슷한가?
  • 칼럼의 값이 자주 변경되는가?

문자열 칼럼의 값이 변경되는 경우, CHAR 타입 칼럼은 고정 길이 칼럼이기 때문에 저장 공간의 변화 없이 값만 수정하면 된다.
하지만 VARCHAR 타입은 줄어든 저장 공간 상태에서 그보다 더 큰 길이의 값으로 변경하려면 뒤에 따라오는 레코드의 다음 컬럼들을 이동하는 작업이 필요하다.

또한 CHAR, VARCHAR 키워드 뒤에 인자로 전달하는 숫자 값은 해당 칼럼의 바이트 크기가 아니라 문자의 수를 의미한다.
따라서 해당 칼럼에서 사용하는 캐릭터 셋에 따라 실제 바이트 크키가 달라지게 된다.

문자집합 (캐릭터 셋)

문자집합은 CHAR, VARCHAR, TEXT 타입 칼럼에 지정할 수 있으며, MySQL에서는 최종적으로 칼럼 단위로 문자집합을 관리하지만 관리의 편의를 위해 MySQL 서버와 DB, 테이블 단위로 기본 문자집합을 설정할 수 있다.

  • character_set_system
    • MySQL 서버가 식별자를 저장할 때 사용하는 문자집합이다.
    • 항상 utf8로 설정되며, 사용자가 설정할 필요는 없다.
  • character-set-server
    • MySQL 서버의 기본 문자집합이다.
    • DB, 테이블에 아무런 문자집합이 설정되지 않으면 이 설정이 적용된다.
  • character_set_database
    • MySQL DB의 기본 문자집합이다.
  • character_set_client
    • MySQL 클라이언트가 보낸 SQL 문장의 인코딩 방식이다.
  • character_set_connection
    • MySQL 서버가 클라이언트로부터 전달받은 SQL 문장을 처리하기 위한 문자집합이다.

콜레이션(Collation)

콜레이션문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미한다.

latin1_bin
latin1_general_ci
latin1_general_cs
utf8_general_ci
utf8_bin
euckr_korean_ci
euckr_bin
  • 3개의 파트로 구성된 콜레이션 이름
    • 첫 번째 파트는 문자집합의 이름이다.
    • 두 번째 파트는 해당 문자집합의 하위 분류이다.
    • 세 번째 파트는 대소문자 구분 여부를 나타낸다. "ci"는 대소문자를 구분하지 않는 콜레이션, "cs"는 대소문자를 구분하는 콜레이션이다.
  • 2개의 파트로 구성된 콜레이션 이름
    • 첫 번째 파트는 문자집합의 이름이다.
    • 두 번째 파트는 항상 "bin"이다. 이는 이진 데이터를 의미하며, 별도의 콜레이션을 가지지 않음을 의미한다. 비교 및 정렬은 실제 문자 데이터의 바이트 값을 기준으로 수행한다.

"CREATE DATABASE db_test CHARACTER SET=utf8;"과 같이 콜레이션을 지정하지 않았더라도 기본적으로 utf8은 utf8_general_ci로 지정된다.

중요한 것은 타입의 이름과 문자열의 길이, 문자집합과 콜레이션까지 일치해야 똑같은 타입이라고 볼 수 있다는 점이다.
모두 일치해야만 조인이나 WHERE 조건이 인덱스를 효율적으로 사용할 수 있다.


숫자

타입에 따른 값의 정확도

  • 참값 (Exact value data type)
    • 소수점 이하 유무에 관계없이 정확히 그 값을 유지하는 것
    • INTEGER, DECIMAL
  • 근사값
    • 흔히 부동 소수점이라고 불리는 값, 처음 칼럼에 저장한 값과 조회되는 값이 정확하게 일치하지는 않고 최대한 비슷한 값을 관리하는 것
    • FLOAT, DOUBLE

보통 근사값은 유효 자리수 이상의 소수점 이하 값은 계속 바뀌기 때문에 복제 시에도 마스터와 슬레이브에서 차이가 날 수 있다.
그래서 FLOAT, DOUBLE과 같은 부동 소수점 타입은 잘 사용하지 않는다.

또한 DECIMAL 타입은 보통의 타입보다 저장 공간을 2배 이상 필요로 하기 때문에 일반적으로 INTEGER나 BIGINT를 사용한다.

정수

- TINYINT : 1바이트
- SMALLINT : 2바이트
- MEDIUMINT : 3바이트
- INTEGER : 4바이트
- BIGINT : 8바이트

정수 타입은 위의 5가지로, 저장 공간이 큰 타입일수록 더 큰 수를 저장할 수 있다.
또한 정수 타입은 UNSIGNED라는 칼럼 옵션을 사용할 수 있는데, 기본적으로 음수, 양수를 표기할 수 있는 SIGNED 옵션과 달리 0보다 큰 양의 정수만 사용하는 옵션이다.
대신 음수 만큼의 수를 더 표현할 수 있으므로 양의 범위에서는 기존보다 두배의 범위를 더 표현할 수 있다.

부동 소수점 / DECIMAL

부동은 소수점의 위치가 고정적이지 않다는 뜻으로, 숫자 값의 길이에 따라 유효 범위의 소수점 자리수가 바뀐다.
부동 소수점은 근사값을 저장하는 방식이라서 동등 비교는 할 수 없다.

복제에 참여하는 MySQL 서버에서 부동 소수점 타입의 데이터는 MySQL의 텍스트 기반 복제에서는 마스터와 슬레이브 간의 데이터가 달라질 수 있기 때문에 주의해야 한다.

소수점의 위치가 가변적이지 않은 고정 소수점 타입을 위해 DECIMAL 타입을 제공한다.
DECIMAL 타입은 숫자 1~2자리를 저장하는 데 1바이트가 필요하므로 소수가 아닌 정수값을 관리하기 위해 DECIMAL 타입을 사용하는 것은 성능, 공간 문제 상 좋지 않다.

정수 타입 칼럼 생성 시 주의사항

부동 소수점이나 DECIMAL 타입을 이용해 칼럼을 정의할 때는 타입의 이름 뒤에 괄호로 정밀도를 표기하는 것이 일반적이다.
예를 들어 DECIMAL(20, 5)라고 정의하면 정수부를 15자리, 소수부를 5자리까지 저장할 수 있는 DECIMAL을 생성한다.

하지만 정수 타입 뒤에 표시되는 괄호는 화면의 표시할 자리 수를 의미할 뿐 저장 가능한 값을 제한하는 용도가 아니다.
BIGINT(10)과 같이 지정하면 10자리를 채워서 화면에 표기하며, '0000012345'와 같이 0으로 값의 앞쪽을 패딩해서 표기하게 된다.

정수 타입 뒤의 길이 지정은 ZEROFILL 옵션이 없으면 아무런 의미가 없다.
또한 ZEROFILL 옵션이 사용되면 해당 칼럼의 타입은 자동으로 UNSIGNED 타입이 되어 버리기 때문에 주의해야 한다.


날짜와 시간

DATETIME과 TIMESTAMP

  • DATETIME
    • 타임존에 대해서 아무런 영향을 받지 않는 값이다.
  • TIMESTAMP
    • 타임존이 변경됨에 따라 그에 맞게 시간이 보정되어 조정된다.

가장 좋은 관리법은 DATETIME의 값을 항상 MySQL 서버의 타임존으로 변환해서 저장하는 것이다.
그리고 MySQL 커넥션의 타임존(커넥션의 time_zone 변수)은 그 지역에 맞게 정확하게 설정하는 것이 좋다.

TIMESTAMP 타입의 옵션

TIMESTAMP가 지닌 또 하나의 차이는 레코드가 UPDATE, INSERT 될 때 자동으로 현재 시간으로 변경된다는 것이다.
즉, 말 그대로 해당 레코드의 INSERT, UPDATE 시점의 도장을 찍는 역할을 한다.

하지만 하나의 테이블의 2개 이상의 TIMESTAMP 칼럼이 모두 아무런 옵션을 가지지 않으면 먼저 명시된 TIMESTAMP 칼럼만 타임스탬프 역할을 하고 나머지 하나는 해당 기능을 잃게 된다.
사용자가 명시적으로 특정 시간을 지정하면 시간 도장의 기능과 관계 없이 사용자가 명시한 값이 저장된다.


참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.