MySQL 데이터타입 (DateType)

데이터타입 선정할 때 주의할점

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

 

데이터타입을 무분별하게 칼럼의 길이가 크게 선정되면 디스크의 공간이나 메모리, CPU 자원을 낭비하게되고

길이를 적게 선정하면 서비스 도중에  스키마 변경이 필요해지게 되는데 데이터가 많이 쌓인 상태에서 스키마 변경은 더 힘들어진다.

그래서 항상 실제로 저장되는 값의 성격을 정확히 분석하고 최적의 타입과 길이를 선정하는 것이 좋다.

 

---

1. 문자 데이터 타입 

• CHAR(n) - 고정 길이 데이터 타입 (최대 255자)
• VARCHAR(n) - 가변 길이 데이터 타입 (최대 65,535자)

※ CHAR vs VARCHAR

CHAR, VARCHAR 의 차이점은 CHAR는 고정형 VARCHAR 는 가변형이다.

VARCHAR 타입은 최대로 저장할 수 있는 값의 길이는 제한돼 있지만 그이하 크기의 값이 저장되면  그만큼 저장공간이 줄어든다. 

하지만 VARCHAR 타입은 저장된 값의 유효 크기가 얼마인지를 별도로 저장해둬야 해서 1~2바이트의 저장공간이 추가로 더 필요하다.

 

문자열 값의 길이가 일정하면서 칼럼의 값이 자주 변경된다면 CHAR 그게 아니라면 VARCHAR 를 사용하는게 일반적이다.

CHAR(5), VARCHAR(10) 와 같이 뒤에 지정하는 숫자는 바이트 크기가 아니고 문자열의 길이다.

---

2. 숫자 데이터 타입

• TINYINT - 1Byte ( -128 ~ 127, 0 ~ 255)
• SMALLINT - 2Byte ( -32,768 ~ 32,767, 0 ~ 65,535 )
• MEDIUMINT - 3Byte ( -8,388,608 ~ 8,388,706, 0 ~ 16,777,215 )
• INT - 4Byte ( -2147483648 ~ 2147483647, 0 ~ 4294967295 )
• BIGINT - 8Byte
• FLOAT - 4Byte (소수점 8자리)
• DOUBLE - 8Byte (소수점 16자리)
• DECIMAL - 길이 + 1

※ 길이지정

정수 타입뒤에 명시되는 괄호는 ( INT(n) , SMALLINT(n) ) 화면에 표시할 자리 수를 의미할뿐

저장가능한 값을 제한하는 용도가 아니다. 자리수는 ZEROFILL 을 얼마나 할지를 의미하는 자리 수다.

정수 타입뒤에  길이지정은 ZEROFILL 옵션이 없으면 아무런 의미가 없다.

 

※ 정수형타입

정수 타입 ( *INT, DECIMAL) 은 UNSIGNED 라는 옵션을 사용할 수 있다.

UNSIGNED 옵션을 정의하면 0보다큰 양의 정수만 저장할 수 있고 최대값은 SIGNED 타입보다 2배가 커진다.

AUTO_INCREMENT 칼럼과 같이 음수가 될수 없는 값에 UNSIGNED 옵션을 명시하면 작은 데이터로 공간으로 더 큰 값을 저장 가능하다.

 

※ 부동소수점 (FLOAT, DOUBLE)

부동소수점이란 소수점이 고정되어 있지 않고 움직일 수 있다는 뜻을 말한다.

근사값이다. 그래서 저장해야할 때 조심해야되는데 특히 REPLICATION 사용시  더 조심해야 한다.

마스터와 슬레이브간의 데이터가 달라질 수 있다.

 

만약 정확한 소수점을 저장해야한다면 유효 수소점의 자리수만큼 10을 곱해서 정수로 만들어 그 값을

정수타입의 칼럼에 저장하는 방법이있다.

소수점까지 정확하게 관리해야할 때는 FLOAT, DOUBLE 을 사용하면 안된다.

 

정확한 금액이나 대출이자등 고정된 소수점을 구하고 싶다면 DECIMAL 타입을 사용해야한다.

---

3. 날짜 / 시간  데이터 타입

• YEAR - 1Byte  /  YEAR(2) : 70(1970)~69(2069), YEAR(4): 1901 ~ 2155
• TIME - 2Byte /  '-833:59:59' ~ '833:59:59'
• DATE - 3Byte / '1001-01-01' ~ '9999-12-31'
• TIMESTAMP - 4Byte / '1970-01-01 00:00:01' ~ '2038-01-29 30:14:07' (UTC)
• DATETIME - 8Byte / '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'

MySQL 에서는 날짜와 시간을 저장할 떄 여러가지 타입을 지원하는데 보통 DATE 와 DATETIME 타입이 많이 사용된다.

 

DATE 와 DATETIME 타입은 현재 DBMS  커넥션의 타임존에 관계없이 클라이언트로 부터 입력된 값을 그대로 저장하고 조회할 때도 변환 없이 그대로 출력한다.

하지만 TIMESTAMP는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다.

 

# UTC (Universal Coordinated Time) 

→ 국제표준시

---

4. ENUM , SET

• ENUM- 1Byte (문자열의 종류가 255개 미만), 2Byte (문자열의 종류가 255개 이상일 경우)
• SET - 1Byte (멤버수가8개이하), 2Byte (9개 ~ 16개이하), 3Byte, 8Byte

ENUM, SET은 모든 문자열 값을 MySQL 내부적으로 숫자 값으로 맵핑해서 관리하는 타입이다.

 

ENUM 타입은 반드시 하나의 값만 저장할 수 있고 가장 큰 용도는 코드화된 값을 관리하는 것이다.

문자열처럼 비교하거나 저장할 수 있지만 실제로 값을 저장할떄는 그값에 맵핑된 정수값을 사용한다.

그러다보니 정렬을 수행하면 맵핑되기전의 문자열 기준이 아니라 맵핑된 코드값으로 정렬이 수행된다.

강제로 문자열을 정렬할 수 있지만 인덱스를 이용할 수 없어서 주의해야한다.

ENUM 타입의 단점은 기존의 ENUM 타입에 새로운값을 추가해야 한다면 테이블 구조를 변경해야 하는점이다.

레코드 건수가 적으면 상관 없겠지만 많다면 오래걸리고 서비스를 중지하고 진행해야될 수 도 있다. 

 

SET 타입도 문자열 값을 정수값으로 맵핑해서 저장하는 방식은 같지만 하나의 칼럼에 1개 이상의 값을 저장할 수 있다.

---

5. TEXT , BLOB 

※ (L = 저장하고자 하는 데이터의 바이트 수 )

• TINYTEXT, TINYBLOB  (L + 1바이트) 
• TEXT, BLOB (L + 2바이트) 
• MEDIUMTEXT, MEDIUMBLOB ( L + 3바이트)
• LONGTEXT, LONGBLOB (L + 4바이트)

MySQL 에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용해야 한다.

TEXT 타입은 문자열을 저장하는 대용량 칼럼이고 BLOB 타입은 이진 데이터 타입이다.

컬럼 하나에 저장되는 문자열이나 이진값의 길이가 예측할 수 없이 클 때  사용한다.

 

# 추가

MySQL 에서는 레코드의 전체 크기를 64KB 로 제한되는데 (버전에 따라 차이 있을 수 있음)

그럴경우 TEXT나 BLOB 타입으로 전화해야 될 수도 있다.