상상플러스 :: MySQL이 지원하는 컬럼타입

http://blog.naver.com/loves0508/2941304

MySQL이 지원하는 컬럼타입
이 글은 MySQL사이트에서 제공하는 매뉴얼 중에서 컬럼관련 부분을 기초로 번역 및 정리한 내용입니다.
더욱 자세하고 내용을 원하시면 mysql의 document를 참고하십시요.

자 그럼 시작하겠습니다.

1. Column types
MySQL은 numeric types, date and time types, 그리고 string (character) types의 총 세가지 타입을 지원합니다.

아래에는 MySQL이 지원하는 컬럼 타입을 적었는데 바로 밑의 것은 컬럼타입을 설명하는데 사용한 문자에 대한 설명입니다.

M
최대 표시 크기를 나타냅니다. 최대크기는 255입니다.
D
부동소주점 타입에 적용되는 것으로 소수점아래의 자릿수를 나타냅니다.
ZEROFILL
입력한 값의 자릿수에 여유가 있으면 그 부분을 모두 0으로 채웁니다.
UNSIGNED
0과 양수만 사용합니다.
Square brackets (`[' 과 `]')은 옵션으로 특별한 사항을 나타내는데 사용합니다.

A. NUMERIC TYPES
주의! : 만일 컬럼에 ZEROFILL를 명기하면, MySQL은 자동적으로 UNSIGNED 특성을 컬럼에 적용합니다.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
매우 작은 정수. 사용범위 : -128 to 127. unsigned로 정의하면 0 to 255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
매우 작은 정수. 사용범위 : -32768 to 32767. unsigned로 정의하면 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
중간 크기의 정수. 사용범위 : -8388608 to 8388607. unsigned로 정의하면 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
보통 크기의 정수. 사용범위 : -2147483648 to 2147483647. unsigned로 정의하면 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
INT와 같음.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
큰 정수. 사용범위 : -9223372036854775808 to 9223372036854775807. unsigned로 정의하면 0 to 18446744073709551615.
Note!
모든 계산은 부호가 있는 BIGINT 또는 DOUBLE 타입의 값으로 이루어집니다.
그러므로 bit 함수를 제외한 9223372036854775807보다 큰 정수는 사용하지 마십시오.

연산 대상 둘 다 INTEGER값이면, -,+와 *는 BIGINT 연산을 사용할 것입니다.
이 의미는 만일 두 개의 BIGINT를 당신이 곱할 경우 (또는 함수의 계산 결과가 정수를 반환할 경우),
그 결과가 9223372036854775807보다 클 경우에는 원하지 않는 결과를 얻게 된다는 것입니다.
FLOAT(precision) [ZEROFILL]
부동소수점 수(일반적으로 말하는 소수). unsigned는 사용할 수 없습니다.
precision(정밀도)은 4 또는 8만 사용가능합니다.
FLOAT(4)은 single-precision의 수를 나타내고,
FLOAT(8)은 double-precision의 수를 나타냅니다.
이런 타입은 타입과 바로 밑에서 기술하는 FLOAT와 DOUBLE 타입과 같습니다.
FLOAT(4)와 FLOAT(8) 은 FLOAT 과 DOUBLE 타입과 같습니다.
그라나 소수부분의 표시 크기나 수는 정의되지 않습니다.
MySQL 3.23에서, 이것은 true floating point value입니다.
이보다 이전 버전에서는, FLOAT(precision)은 항상 2 decimals이었습니다.
이 구문은 ODBC 호환성을 위해 미리 제공되는 것입니다.
FLOAT[(M,D)] [ZEROFILL]
매우 작은(single-precision) 부동소수점 수.
unsigned 값은 사용할 수 없습니다.
사용범위 : -3.402823466E+38부터 -1.175494351E-38까지와 1.175494351E-38부터 3.402823466E+38까지, 그리고 0.
DOUBLE[(M,D)] [ZEROFILL]
보통 크기의(double-precision) 부동소수점 수.
unsigned 사용할 수 없음.
사용 범위 : -1.7976931348623157E+308부터 -2.2250738585072014E-308까지와 2.2250738585072014E-308부터 1.7976931348623157E+308까지 그리고 0.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
이것은 DOUBLE과 동일합니다.
DECIMAL(M,D) [ZEROFILL]
unpacked 소동소수점 수.
unsigned은 사용할 수 없음.
CHAR 컬럼 같이 행동합니다.
``unpacked'' 은 수를 문자처럼 저장한다는 의미입니다.
소수점, 음의 부호(-) 등을 포함하여 수 한자리를 저장하는데 각각 digit의 값을 사용하여 저장합니다.
만일 D가 0이면 소수점 또는 소수부분이 없는 값으로 지정하는 것입니다.
최대 사용 범위는 DOUBLE과 같습니다만, 실제적인 사용범위는 M 과 D의 선택 사항에 의해 결정됩니다.
MySQL 3.23에서 M은 부호나 소수점을 포함하지 않는다. (이것은 ANSI SQL에 따른 것이다.)
NUMERIC(M,D) [ZEROFILL]
DECIMAL과 같다.

B. DATE and TIME TYPES
DATE
날짜. 지원 범위는 '1000-01-01'부터 '9999-12-31'까지입니다.
MySQL 은 'YYYY-MM-DD' 형식으로 값을 표시하지만,
string 또는 number 모두를 사용하여 DATE 컬럼에 값을 할당할 수 있다.
이 내용은 date and time 타입에 거의 모두 적용되므로 앞으로는 기술하지 않습니다.
DATETIME
date와 time을 조합한 타입읻아.
이 타입은 '1000-01-01 00:00:00'부터 '9999-12-31 23:59:59'까지를 사용할 수 있다.
MySQL은 'YYYY-MM-DD HH:MM:SS' 형식으로 나타냅니다.
TIMESTAMP[(M)]
타임스탬프(편지.문서의 발송.접수. 날짜.시간을 기록).
이 범위는 '1970-01-01 00:00:00' 로부터 때때로 2037년까지 사용된다.
MySQL은 TIMESTAMP값으로 YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD 또는 YYMMDD 형식으로 나타낸다.
M이 값이 14, 12, 8 또는 6인지에 따라 달라진다.
이 컬럼타입도 마찬가지로 string과 number를 이용하여 값을 할당할 수 있다.
시간과 날짜를 insert, update하는데 유용하다.

TIME
시간.
지원 범위는 '-838:59:59' to '838:59:59'.
MySQL은 'HH:MM:SS'형식으로 time을 나타낸다.
YEAR
연도.
1901년부터 2155까지 그리고 0000년을 사용할 수 있다.
MySQL은 YEAR컬럼에 YYYY 형식으로 나타낸다.
YEAR 타입은 MySQL 3.22.에서 새로 지원하는 타입이다.

C. STRING TYPES
CHAR(M) [BINARY]
길이가 정해진 문자열.
저장할 때 길이보다 문자열의 길이가 작으면 그 길이 만큼 오른쪽에 공백문자(SPACE)를 더하여 저장한다.
값이 retrieve될 때는 덧붙혀진 공백은 제거된다.
CHAR값은 BINARY 키워드로 지정되지 않는다면 case-insensitive(대소문자 구분없이) 형태로 저장되고 비교된다.

VARCHAR(M) [BINARY]
가변길이의 문자열.
뒤에 덧붙어있는 공백문자는 저장될 때 제거된다. (이것은 ANSI SQL specification과 다르다).
M은 1부터 255문자까지 사용가능하다.
VARCHAR 값도 BINARY키워드로 설정되지 않으면 case-insensitive 형태로 저장되고 비교된다.

TINYBLOB
최대 길이가 255 (2^8 - 1)인 BLOB 컬럼.
BLOB는 그림이나 바이너리 파일 등을 저장하는데 사용된다.
TINYTEXT
최대 길이가 255 (2^8 - 1)인 TEXT 컬럼.
BLOB
최대 길이가 65535 (2^16 - 1)인 BLOB 컬럼.

TEXT
최대 길이가 65535 (2^16 - 1)인 TEXT 컬럼.

MEDIUMBLOB
최대 길이가 16777215 (2^24 - 1)인 BLOB 컬럼.

MEDIUMTEXT
최대 길이가 16777215 (2^24 - 1)인 TEXT 컬럼.
LONGBLOB
최대 길이가 4294967295 (2^32 - 1)인 BLOB 컬럼.
LONGTEXT
최대 길이가 4294967295 (2^32 - 1)인 TEXT 컬럼.
ENUM('value1','value2',...)
목록 컬럼.
목록에서 선택된 하나의 값 또는 NULL만을 가질 수 있는 문자열 객체이다.
ENUM은 구성요소의 총 문자열 길이가 최대 65535까지의 서로 다른 값만을 사용할 수 있다.
예를 들어 test란 테이블에 test란 컬럼명으로 ENUM("one", "two", "three")을 설정한 컬럼이 있다면 다음과 같이 각각에 인덱스가 붙는다. :

Value
Index

NULL
NULL

""
0

"one"
1

"two"
2

"three"
3

커리에서 where절을 사용하여 one을 선택한 경우를 select하려면 다음과 같이 한다.
mysql> select * from test where test=1;
SET('value1','value2',...)
A set.
zero 또는 리스트에서 선택한 각각의 값을 가질 수 있는 문자열 객체.
최대 64개의 member를 가질 수 있다.
예를 들어 SET("one", "two") NOT NULL 과 같이 정의된 컬럼이 있다면 다음과 같은 값을 목록에 가지고 있다.:
""
"one"
"two"
"one,two"

MySQL은 수의 형태로 값(bit값)을 저장한다.
만약 SET("a","b","c","d")으로 정의한 컬럼이 있다면 다음과 같은 bit값을 가진다. SET member
Decimal value
Binary value

a
1
0001

b
2
0010

c
4
0100

d
8
1000

만일 9 즉, 바이너리로 1001인 값을 저장했다면, "a" 와 "d"를 선택한 것으로 결과 값은 "a,d"이다.
이런 형식으로 저장하기에 "a,d"나 "d,a" 또는 "d,a,a,d,d" 등은 모두 "a,d"로 저장되고 보여진다.
NULL값은 non-NULL값으로 셋팅된 것보다 전에 sort된다.
보통, SELECT 또는 FIND_IN_SET() 함수를 set으로 정의한 컬럼에 like 연산자를 이용하여 사용한다.
다음과 같은 경우를 예로 들 수 있다.
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

그러나 이런 경우는 다음과 같이 실행된다. :
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

위 문장들 중 첫 번째는 확실히 일치하는 값을 찾는다.
그리고 두 번째는 첫 번째 멤버를 포함한 값을 찾는다.

2.Column type의 요구()사항
아래의 A,B,C는 각 category별로 컬럼 타입마다 저장에 필요한 공간(크기)을 적은 것입니다.

A. Numeric types
Column type
Storage required

TINYINT
1 byte

SMALLINT
2 bytes

MEDIUMINT
3 bytes

INT
4 bytes

INTEGER
4 bytes

BIGINT
8 bytes

FLOAT(4)
4 bytes

FLOAT(8)
8 bytes

FLOAT
4 bytes

DOUBLE
8 bytes

DOUBLE PRECISION
8 bytes

REAL
8 bytes

DECIMAL(M,D)
M bytes (D+2, if M < D)

NUMERIC(M,D)
M bytes (D+2, if M < D)

B. Date and time types
Column type
Storage required

DATETIME
8 bytes

DATE
3 bytes

TIMESTAMP
4 bytes

TIME
3 bytes

YEAR
1 byte

C. String types
Column type
Storage required

CHAR(M)
M bytes, 1 <= M <= 255

VARCHAR(M)
L+1 bytes, where L <= M and 1 <= M <= 255

TINYBLOB, TINYTEXT
L+1 bytes, where L < 2^8

BLOB, TEXT
L+2 bytes, where L < 2^16

MEDIUMBLOB, MEDIUMTEXT
L+3 bytes, where L < 2^24

LONGBLOB, LONGTEXT
L+4 bytes, where L < 2^32

ENUM('value1','value2',...)
1 or 2 bytes, 목록 값의 개수에 달려 있다.
(최대 길이 65535)

SET('value1','value2',...)
1, 2, 3, 4 or 8 bytes, 셋 멤버의 개수에 달려있다.
(최대 64개의 멤버)

VARCHAR과 BLOB and TEXT types은 가변길이를 가진다.
그러므로 저장공간은 저장하는 값의 실제 길이에 따라 정해진다.
BLOB and TEXT types은 컬럼에 값을 저장하기 위해 1, 2, 3 또는 4 바이트가 필요하다.

3. Column indexes
MySQL은 BLOB and TEXT type을 제외한 모든 column types을 색인할(indexed) 수 있습니다.
컬럼에 관계된 인덱스의 사용은 select 연산에서의 속도를 개선하는데 최선의 방법이고,
하나의 테이블은 16개의 인덱스를 사용할 수 있습니다.
인덱스의 최대길이는 256바이트이고, 이것은 MySQL을 컴파일할 때 변경할 수 있습니다.
그러나 인덱스에는 null을 사용할 수 없습니다. 그러므로 인덱스로 사용할 컬럼은 NOT NULL로 선언되어야 합니다.

char나 varchar컬럼을 인덱스할 때 전부가 아닌 일부분을 할 수도 있습니다.
이것은 하드의 공간이나 검색속도에서 좋습니다.
다음과 같이 키(인덱스키)를 정의하면 됩니다.

KEY index_name (col_name(length))
예로 name컬럼의 첫 10글자만 인덱스로 지정하여 테이블을 만드는 것을 보이면 다음과 같습니다. :

mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
MySQL이 지원하는 컬럼타입과 인덱스에 대해 알아보았습니다.

상상플러스 - 최근 공지