이전 포스팅에서 테이블을 생성할 때, 각 컬럼마다 저장될 값에 알맞은 데이터 타입(Data type)을 설정했는데요.
데이터 타입을 잘 설정해야 저장 용량을 효율적으로 활용할 수 있고, 나중에 row 수가 많아졌을 때 성능을 개선할 수 있습니다.
사용할 수 있는 데이터 타입은 DBMS마다 조금씩 차이가 있는데요.
MySQL의 데이터 타입 중 일반적으로 쓰이는 것들은 다음 세 가지 카테고리로 분류할 수 있습니다.
- Numeric types(숫자형 타입)
- Date and Time types(날짜 및 시간 타입)
- String types(문자열 타입)
1. Numeric types(숫자형 타입)
숫자를 나타내기 위해서 사용되는 데이터 타입입니다.
숫자형 타입은 다시 정수형 타입과 실수형 타입으로 나눌 수 있는데요. 하나씩 살펴볼게요.
(1) 정수형 타입
말 그대로 정수값을 저장하는 타입입니다. 여기에 해당하는 타입들은 그것이 나타낼 수 있는 정수값의 범위에 차이가 있습니다.
1) TINYINT
: 작은 범위의 정수들을 저장할 때 쓰는 데이터 타입입니다.
TINYINT에서 INT는 Integer(정수)의 줄임말인데요. 최소 -128 ~ 최대 127 까지의 정수를 저장할 수 있는 타입입니다.
이때 SINGED와 UNSIGNED라는 개념을 알아야하는데요. SIGNED는 ‘양수, 0, 음수’를 나타내고, UNSIGNED는 ‘0과 양수’를 나타냅니다.
TINYINT 뒤에 둘 중 무엇을 붙이느냐에 따라 아래와 같이 나타낼 수 있는 숫자의 범위가 달라집니다.
TINYINT SIGNED : -128 ~ 127
TINYINT UNSIGNED : 0 ~ 255
이런 원리는 다른 정수형 타입에도 똑같이 적용됩니다. 참고로, TINYINT라고만 썼을 때는 SIGNED가 붙은 것으로 자동 해석됩니다.
2) SMALLINT
: TINYINT 보다 좀더 큰 범위의 정수를 나타낼 때 쓰는 데이터 타입입니다. SINGED와 UNSIGNED에 따라 저장할 수 있는 수의 범위는 다음과 같습니다.
SMALLINT SIGNED : -32768 ~ 32767
SMALLINT UNSIGNED : 0 ~ 65535
3) MEDIUMINT
: 더 넓은 범위를 나타내는 데이터 타입니다.
MEDIUMINT SIGNED : -8388608 ~ 8388607
MEDIUMINT UNSIGNED : 0 ~ 16777215
4) INT
: 더 넓은 범위를 나타내는 데이터 타입입니다.
INT SIGNED : -2147483648 ~ 2147483647
INT UNSIGNED : 0 ~ 4294967295
5) BIGINT
: 아주 큰 범위의 정수를 저장하는 데이터 타입입니다.
BIGINT SIGNED : -9223372036854775808 ~ 9223372036854775807
BIGINT UNSIGNED : 0 ~ 18446744073709551615
BIGINT 데이터 타입을 사용한다면 아무리 큰 정수라도 잘 저장할 수 있겠죠?
(2) 실수형 타입
우리는 정수뿐만 아니라 소수점이 붙어있는 수를 사용하기도 하는데요, 소수점이 있는 자료형입니다.
1) DECIMAL
:일반적으로 자주 쓰이는 실수형 타입 중 하나로 보통 DECIMAL(M, D)의 형식으로 나타냅니다.
여기서 M은 최대로 쓸 수 있는 전체 숫자의 자리수이고, D는 최대로 쓸 수 있는 소수점 뒤에 있는 자리의 수를 의미합니다.
예를 들어
DECIMAL (5, 2)라면 -999.99 부터 999.99 까지의 실수를 나타낼 수 있는 거죠.
M은 최대 65, D는 30까지의 값을 가질 수 있습니다.
그리고 DECIMAL이라는 단어 대신 DEC, NUMERIC, FIXED를 써도 됩니다.
2) FLOAT
-3.402823466E+38 ~ -1.175494351E-38,
0,
1.175494351E-38 ~ 3.402823466E+38
범위의 실수들을 나타낼 수 있는 데이터 타입입니다. 참고로
-3.402823466E+38 은 (-3.402823466) X (10의 38제곱) 을 의미하고
-1.175494351E-38 은 (-1.175494351) X (10의 38제곱 분의 1) 을 의미합니다.
3) DOUBLE
-1.7976931348623157E+308 ~ -2.2250738585072014E-308,
범위의 실수들을 나타낼 수 있는 데이터 타입입니다. FLOAT에 비해 더 넓은 범위의 수를 나타낼 수 있을 뿐만 아니라,
그 정밀도 또한 더 높은 타입입니다.
2. 날짜 및 시간 타입(Date and Time Types)
데이터베이스에서는 날짜 및 시간 정보를 다뤄야하는 경우가 정말 많습니다. 이런 정보를 다루는 데이터 타입들을 알아볼게요.
(1) DATE
:날짜를 저장하는 데이터 타입입니다. 날짜는 ’2020-03-26’ 이런 형식의 연, 월, 일 순으로 값을 나타냅니다.
(2) DATETIME
날짜와 시간을 저장하는 데이터 타입입니다. ’2020-03-26 09:30:27’ 이런 식으로 연, 월, 일, 시, 분, 초를 나타냅니다.
(3) TIMESTAMP
날짜와 시간을 저장하는 데이터 타입입니다. ’2020-03-26 09:30:27’ 이런 식으로 연, 월, 일, 시, 분, 초를 나타냅니다. 그럼 DATETIME 타입과는 어떤 점이 다를까요? TIMESTAMP 타입은 타임 존(세계 시간대) 정보도 함께 저장한다는 점이 다릅니다.
각 나라별 시간을 조회할 수 있는것입니다!
UTC라는 개념을 알아야 합니다. 전세계에 여러 시간대(타임존, time-zone)가 존재한다는 사실은 알고 계시죠? 각각의 시간대는 UTC를 기준으로 나타낼 수 있는데요. UTC(Coordinated Universal Time)는 국제 사회에서 통용되는 표준 시간 체계로 '국제 표준시'라고도 합니다. UTC는 영국 런던을 기준으로 하는데요. 우리나라는 런던을 기준으로 시간이 9시간 더 빠르기 때문에, 우리나라의 시간을 표현할 때는 UTC+9라고 표현합니다.
그러니까 위에서 제가 저장한 날짜 시간 값은 이미 UTC+9에 해당하는 값인 거죠.
나중에 기회되면 이것을 이용하는 실습을 해보겠습니다.
(4) TIME
: 시간을 나타내는 데이터 타입입니다. ’09:27:31’ 형식으로 ‘시:분:초’를 나타냅니다
3. 문자열 타입(String type)
문자열을 저장하기 위한 타입입니다. 이름, 댓글, 구매후기 등 문자열 형태의 데이터는 정말 다양하죠? 아래와 같은 타입들이 있습니다.
(1) CHAR
문자열을 나타내는 기본 타입으로 Character의 줄임말입니다. CHAR(30), 이런 형식으로 나타내는데요. 괄호 안의 숫자는 문자를 최대 몇 자까지 저장할 수 있는지를 나타냅니다. 30이라고 써있으면 최대 30자의 문자열을 저장할 수 있다는 뜻입니다. CHAR 타입의 괄호 안에는 0부터 255까지의 숫자를 적을 수 있습니다.
(2) VARCHAR
VARCHAR도 VARCHAR(30) 이런 식으로 문자열의 최대 길이를 지정할 수 있는 문자열 타입입니다. 괄호 안에 최소 0부터 최대 65,535를 쓸 수 있습니다.
그런데 VARCHAR는 CHAR보다 허용되는 최대 저장 길이가 더 크다는 점 말고 다른 차이점도 있습니다.
그건 바로 CHAR는 고정 길이 타입이고, VARCHAR는 가변 길이 타입이라는 점입니다.
따라서 값의 길이가 크게 변하지 않을 컬럼에는 CHAR 타입을 사용하고, 길이가 들쑥날쑥할 컬럼에는 VARCHAR 타입을 쓰는 게 좋습니다.
(3) TEXT
문자열을 저장하는 데이터 타입으로 최대 65535 자까지 저장할 수 있습니다. 이외에도 16,777,215 자까지 저장할 수 있는 MEDIUMTEXT, 4,294,967,295자까지 저장할 수 있는 LONGTEXT 타입이 있습니다.
정말 길이가 긴 문자열을 저장하려면 TEXT 계열의 타입을 써야합니다.
MySQL 데이터 타입
MySQL에는 30여개의 데이터 타입이 존재합니다.
숫자 데이터 형식
숫자형 데이터 형식으로 정수와 실수 등의 숫자를 표현합니다.
데이터 타입 | 바이트 수 | 숫자 범위 | 설명 |
BIT(N) | N/8 | 1 ~ 64bit를 표현합니다. b'0000' 과 같이 표현 | |
TINYINT | 1 | -128 ~ 127 | 정수정수 |
SMALLINT | 2 | -32,768 ~ 32,767 | 정수 |
MEDIUMINT | 3 | -8,388,608 ~ 8,388,607 | 정수 |
INT INTEGER | 4 | 약 -21억 ~ +21억 | 정수 |
BIGINT | 8 | 약 -900경 ~ +900경 | 정수 |
FLOAT | 4 | -3.40E+38 ~ -1.17E-38 | 소수점 아래 7자리까지 표현 |
DOUBLE REAL | 8 | 1.22E-308 ~ 1.79E+308 | 소수점 아래 15자리까지 표현 |
DECIMAL(m, [d]) NUMERIC(m, [d]) | 5 ~ 17 | -1038+1 ~ +1038-1 | 전체 라릿수(m)와 소수점 이하 자릿수(d)를 가진 숫자형 예) decimal(5, 2)는 자릿수 5자리로 하되 소숫점 이하를 2자리로 합니다. |
DECIMAL은 정확한 수치를 저장하지만 FLOAT, REAL은 근사치 값을 저장합니다.
대신에 FLOAT, REAL은 더 큰 숫자를 저장할 수 있습니다.
부호없는 숫자를 저장할 때에는 UNSIGNED 예약어를 같이 사용합니다.
문자 데이터 형식
데이터 타입 | 바이트 수 | 설명 | |
CHAR(n) | 1 ~ 255 | 고정길이 문자형. n을 1부터 255까지 지정. CHARACTER의 약자이며, CHAR 이라고만 하면 CHAR(1)과 동일 CHAR(100) 인 경우 세 자리만 사용해도 나머지 97 자리를 할당. 성능은 CHAR이 VARCHAR보다 더 좋음. | |
VARCHAR(n) | 1 ~ 65535 | 가변길이 문자형. n을 사용하면 1부터 65535까지 지정 Variable Character의 약자. VARCHAR(100) 인 경우 3글자를 저장하는 경우 3자리의 데이터 공간만 사용 | |
BINARY(n) | 1 ~ 255 | 고정길이 이진 데이터 값 | |
VARBINARY(n) | 1 ~ 255 | 가변길이 이진 데이터 값 | |
TEXT | TINYTEXT | 1 ~ 255 | 255 크기의 TEXT 데이터 값 |
TEXT | 1 ~ 65535 | N 크기의 TEXT 데이터 값 | |
MEDIUMTEXT | 1 ~ 16777215 | 16777215 크기의 TEXT 데이터 값 | |
LONGTEXT | 1 ~ 4294967295 | 최대 4GB 크기의 TEXT 데이터 값 | |
BLOB | TINYBLOB | 1 ~ 255 | 255 크기의 BLOB 데이터 값 |
TEXT | 1 ~ 65535 | N 크기의 BLOB 데이터 값 | |
MEDIUMBLOB | 1 ~ 16777215 | 16777215 크기의 BLOB 데이터 값 | |
LONGBLOB | 1 ~ 4294967295 | 최대 4GB 크기의 BLOB 데이터 값 | |
ENUM(값들..) | 1 또는 2 | 최대 65535개의 열거형 데이터 값 | |
SET(값들..) | 1, 2, 3, 4, 8 | 최대 64개의 중복되지 않는 데이터 값 |
CHAR과 VARCHAR은 UTF-8의 형태를 지니므로 입력한 글자의 언어에 따라 내부적으로 크기가 달라집니다.
따라서 CHAR(100)은 한글 영어 상관 없이 100글자를 의미합니다.
mysql5.7의 경우 문자 인코딩 character set 설정은 my.ini 또는 my.cnf 파일에 설정되어 있습니다.
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
ProgramData 폴더는 숨김 폴더입니다.
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
BLOB(Binary Large Object)는 사진, 동영상, 문서 파일 등의 대용량 이진 데이터를 저장하는데 사용합니다.
날짜와 시간 데이터 형식
데이터 형식 | 바이트 수 | 설명 |
DATE | 3 | 날짜는 1001-01-01 ~ 9999-12-31까지 저장되며 날짜 형식만 사용 'YYYY-MM-DD' 형식으로 사용됨 |
TIME | 3 | -838:59:59.000000 ~ 838:59:59.000000까지 저장되며 'HH:MM:SS' 형식으로 사용 |
DATETIME | 8 | 날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장되며 형식은 'YYYY-MM-DD HH:MM:SS 형식으로 사용 |
TIMESTAMP | 4 | 날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장되며 형'YYYY-MM-DD HH:MM:SS 형식으로 사용. time_zone 시스템 변수와 관련이 있으며 UTC 시간대로 변환하여 저장. |
YEAR | 1 | 1901 ~ 2155까지 저장. 'YYYY' 형식으로 사용. |
날짜 형식별 차이점
SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE';
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME';
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME';
기타 데이터 형식
데이터 형식 | 바이트 수 | 설명 |
GEOMETRY | N/A | 공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 개체를 저장 |
JSON | 8 | JSON 문서를 저장. MySQL 5.7.8 부터 지원 |
데이터 타입 관련 공식 문서