MYSQL 데이터 타입

/in /by

이번 포스팅에서는 MYSQL의 데이터 타입에 대해서 살펴본다. MYSQL에는 크게 4가지의 데이터 타입이 존재하며, 각각의 특징과 범위 그리고 저장 가능한 사이즈가 다르다. 데이터 타입을 살펴보고 상황에 따라 테이블을 생성할 때 어떤 타입이 필요한지 고민해보도록 하자.

숫자형 데이터 타입

타입 정의 범위 UNSIGNED 사용가능 여부
TINYINT(n) 정수형 데이터 타입 – 1 Byte(2^8)
– 128 ~ + 127 또는 0 ~ 255 수 표현 가능
– 1Byte(2^8)
– 128 ~ + 127 또는 0 ~ 255 수 표현 가능		 O
SMALLINT(n) 정수형 데이터 타입 – 2 Byte(2^16)
– 32,768 ~ 32,167 또는 0 ~ 65536수 표현 가능		 O
MEDIUMINT(n) 정수형 데이터 타입 – 3 Byte
– 8,388,608 ~ 8,388,607 또는 0 ~ 16,777,215 수 표현 가능		 O
INT(n) 정수형 데이터 타입 – 4 Byte
– 2,147,483,648 ~ 2,147,483,647 또는 0 4,294,967,295 수 표현 가능		 O
BIGINT(n) 정수형 데이터 타입(LONG) – 8 byte
– 2^64 – 1 표현 가능(무한 수 표현 가능이라고도 함)		 O
DECIMAL(m, d) – 고정 소수형 데이터 타입고정(길이+1 byte)
– 화폐 데이터와 같이 데이터의 정확도를 요하는 경우에 주로 사용
– M의 최대값은 65, D는 소수 자릿수이며 0이면 소수점 가지지 않음		 – 소수점을 사용한 형태
– Default: m ⇒ 10		 X
FLOAT(n) 부동 소수형 데이터 타입 – 4 byte
– 부동 소수점을 사용한 형태		 X
DOUBLE(n) 부동 소수형 데이터 타입 – 8 byte
– DOUBLE을 문자열로 저장		 X

UNSIGNED 속성

UNSIGNED 속성을 부여하게 되면 해당 속성을 부여받은 컬럼에는 음수값을 대입하지 못한다. 결제 내역에 대한 테이블을 예시로 들어보도록 하겠다. 결제 금액은 음수값이 될 수 없음을 누구나 알 수 있을 것이다. 이런 경우 UNSIGNED 속성을 부여하여 음수값을 대입하지 못하게 할 뿐만 아니라 대입 가능한 숫자의 범위를 늘려줄 수 있다. 예제를 살펴보도록 하자.

1
2
3
4
5
6
 
CREATE TABLE payment
(
    uid BIGINT NOT NULL AUTO_INCREMENT,
    total_consume_mount INT UNSIGNED DEFAULT 0 NOT NULL,
    PRIMARY KEY (uid)
);

작성된 payment 테이블에 음수값을 대입해보도록 하자. 아마 아래와 같이 에러가 발생하는 것을 확인할 수 있다.

1
2
3
4
 
INSERT INTO payment (uid, total_consume_mount)
VALUES (1, -100);

-- ERROR 1264 (22003): Out of range value for column 'total_consume_mount' at row 1

또한 기존 SIGNED 속성을 가진 INT 타입에는 최대 2,147,483,647까지 대입 가능한 반면 UNSIGNED 속성을 부여하면 대입 가능한 값이 4,294,967,295까지 늘어난 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
-- 대입 가능한 범위가 늘어남을 볼 수 있다.
INSERT INTO payment VALUES (1, 4294967295);

-- 하지만 그 이상의 값을 대입하려 할때면 다음과 같은 에러가 발생하는 것을 확인할 수 있다.
INSERT INTO payment VALUES (1, 4294967296);
-- ERROR 1264 (22003): Out of range value for column 'total_consume_mount' at row 1
``` 

#### Integer 타입의 N의 의미
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 공통적으로 MAXIMUM 값은 항상 N과는 무관하게 허용 가능한 범위는 동일하다. 그렇다면 이 N은 언제 사용하는 것일까? 그에 대한 답은 아래의 예제에서 찾을 수 있다.

```sql
CREATE TABLE payment
(
    uid BIGINT NOT NULL AUTO_INCREMENT,
    total_consume_mount INT(3) ZEROFILL DEFAULT 0 NOT NULL, -- ZEROFILL 속성을 부여한다.
    PRIMARY KEY (uid)
);

INSERT INTO payment VALUES (1, 11);

SELECT * FROM payment;
+-----+---------------------+
| uid | total_consume_mount |
+-----+---------------------+
|   1 |                 001 |
+-----+---------------------+

N 값은 ZEROFILL 속성과 함께 사용할 때 의미가 있다. 테이블을 생성할 때, ZEROFILL 속성을 주게되면 N 자릿수 이하의 값에 대해서 부여된 자리수를 항상 0으로 채운다. 만약 N 자릿수를 초과하게 되면 크게 의미가 없다. 또한 ZEROFILL 속성을 부여하게 되면 자동으로 UNSIGNED 속성이 붙게 된다.

1
2
3
4
5
6
7
8
 
DESC payment;

+---------------------+--------------------------+------+-----+---------+-------+
| Field               | Type                     | Null | Key | Default | Extra |
+---------------------+--------------------------+------+-----+---------+-------+
| uid                 | bigint(20)               | NO   | MUL | NULL    |       |
| total_consume_mount | int(3) unsigned zerofill | NO   |     | 000     |       |
+---------------------+--------------------------+------+-----+---------+-------+

고정 소수형 데이터 타입(DECIMAL)과 부동 소수형 데이터 타입(FLOAT, DOUBLE)

DECIMAL 타입과 FLOAT, DOUBLE의 가장 큰 차이점은 고정 소수형 데이터 타입이냐, 부동 소수형 데이터 타입이냐에 따라 결정되게 된다. DECIMAL 타입의 경우 앞에서 총 2개의 설정을 값을 부여할 수 있다. 아래의 예제를 살펴보도록 하자.

1
2
3
4
5
6
 
CREATE TABLE wallet (
    uid BIGINT NOT NULL AUTO_INCREMENT,
    dollar DECIMAL(5, 2) NOT NULL DEFAULT 0, 
    mileage FLOAT NOT NULL DEFAULT 0,
    PRIMARY KEY (uid)
);

dollar 컬럼에 DECIMAL(5,2)의 데이터 타입을 지정하였다. DECIMAL(M, D)에서 M에 해당하는 5의 경우 총 5자리의 숫자를 사용할 수 있다는 의미이고, D에 해당하는 2는 소수점을 2자리까지 지정하겠다라는 의미이다. 그렇다면 정수의 타입은 M-D(5-2)까지 저장할 수 있다는 것을 유추할 수 있다. 그렇게 DECIMAL(5,2) 데이터 타입의 범위는 -999.99 ~ 999.99이다.

각각의 DECIMAL과 FLOAT 타입에 동일한 데이터를 대입해보도록 하자.

1
2
 
INSERT INTO wallet VALUES (default, 10.91, 10.91);
INSERT INTO wallet VALUES (default, 10.91, 10.91);

값을 값을 대입한 후, 조회를 해보면 값은 값이 저장된 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
 
SELECT * FROM wallet;

+---------+--------+---------+
| user_id | dollar | mileage |
+---------+--------+---------+
|       1 |  10.91 |   10.91 |
|       2 |  10.91 |   10.91 |
+---------+--------+---------+

이렇게만 보면 큰 차이점은 없어 보인다. 하지만 아래와 같이 연산을 해보면 서로 다른 결과를 보인다.

1
2
3
4
5
6
7
 
SELECT SUM(dollar) decimal_type, SUM(mileage) float_type FROM wallet;

+--------------+-------------------+
| decimal_type | float_type        |
+--------------+-------------------+
|        21.82 | 21.81999969482422 |
+--------------+-------------------+

DECIMAL 타입은 고동 소수형 데이터기 때문에 연산을 해도 정해진 자리수로 떨어지는 것을 확인할 수 있지만 FLOAT 타입의 경우, 의도치 않은 결과를 반환하는 것을 볼 수 있다. 그렇기 때문에 앞서의 표에서 화폐 데이터와 같이 정확한 데이터를 요할 경우 주로 사용한다.

문자형 데이터 타입

타입 정의 길이
CHAR(n) – 고정 길이 데이터 타입
– 지정된 길이보다 짧은 데이터 입력 시 나머지 길이는 공백으로 채워짐
– 검색시, PAD_CHAR_TO_FULL_LENGTH 모드를 설정하지 않으면 공백은 제거됨		 0 ~ 255 (byte)
VACHAR(n) – 가변 길이 데이터 타입
– 지정된 길이보다 짧은 데이터 입력시 공백으로 채우지 않음
-저장시 1-byte 혹은 2-byte 길이 Prefix 데이터를 저장. 이 Prefix 데이터는 값의 바이트 수에 대한 정보를 담는다.(https://dev.mysql.com/doc/refman/8.0/en/char.html)		 0 ~ 65,535 (byte)
TINYTEXT(n) – 문자열 데이터 타입(최대 255 byte)
– TINYBLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)
https://dev.mysql.com/doc/refman/8.0/en/blob.html		 0 ~ 255 (byte)
TEXT(n) – 문자열 데이터 타입(최대 65,535 byte)
– BLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)		 0 ~ 65,535 (byte)
MEDIUMTEXT(n) – 문자열 데이터 타입(최대 16,777,215 byte)
– MEDIRMBLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)		 0 ~ 16,777,215 (byte)
LONGTEXT(n) – 문자열 데이터 타입(최대 4,294,967,295 byte)
– LONGBLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)		 0 ~ 4,294,967,295 (byte)

범위값 초과

먼저 예시로 유저 테이블을 생성해보도록 하자.

1
2
3
4
5
6
7
 
CREATE TABLE user (
    uid     BIGINT NOT NULL AUTO_INCREMENT,
    name    VARCHAR(10) NOT NULL,
    password CHAR(10) NOT NULL,
    join_date DATETIME NOT NULL DEFAULT NOW(),
    PRIMARY KEY (uid)
);

생성된 테이블에 샘플 데이터를 넣게 되어보도록 하자. 아래와 같이 비밀번호 컬럼의 범위 N 값 이내의 경우에는 정상적으로 데이터가 추가되는 것을 확인할 수 있다.

1
 
INSERT INTO user VALUES (default, 'Martin', '1234567890', default);

하지만 N값의 범위를 초과하는 경우, 에러를 반환하는 것을 확인할 수 있다. 만약 strict mode가 설정되어 있지 않다면 나머지 값은 길이에 맞게 짤리고 저장은 될 것이다.

1
2
 
INSERT INTO user VALUES (default, 'Martin', '12345678900', default);
-- ERROR 1406 (22001): Data too long for column 'password' at row 1

날짜형 데이터 타입

타입 정의 길이 형식 길이
DATE 날짜(년도, 월, 일) 형태의 기간 표현 데이터 3 byte 0000-00-00 (YYYY-MM-DD) 1000-01-01 ~ 9999-12-31
TIME 시간(시, 분, 초) 형태의 기간 표현 데이터 3 byte 00:00:00 .
DATETIME 날짜와 시간 형태의 기간 표현 데이터 8 byte 0000-00-00 00:00:00 (YYYY-MM-DD hh:mm:ss) 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
TIMESTAMP 날짜와 시간 형태의 기간 표현 데이터 타입 시스템 변경 시 자동으로 그 날짜와 시간이 저장 4 byte Integer .
YEAR 년도 표현 데이터 타입 1 byte 0000 .

이진 데이터 타입

타입 정의 길이
BINARY(n) & BYTE(n) CHAR 형태의 이진 데이터 타입 최대 255 byte
VARBINARY(n) VARCHAR 형태의 이진 데이터 타입 최대 65,535 byte
TINYBLOB(n) 이진 데이터 타입 최대 255 byte
BLOB(n) 이진 데이터 타입 최대 65,535 byte
MEDIUMBLOB(n) 이진 데이터 타입 최대 16,777,215 byte
LONGBLOB(n) 이진 데이터 타입 최대 4,294,967,295 byte

ENUM 타입

ENUM 타입의 경우에는 실제 실무에서 사용할 수 있는 경우가 크게 많아 보이지는 않는다. 새로운 ENUM 타입이 생겼을 경우나 혹은 정렬을 해야하는 경우 비효율적이기 때문이다. 그래도 어떠한 타입인지 알고 쓸지 말지를 결정하는 것이 더 좋기 때문에 예제를 통해 한번 살펴보도록 하자. 먼저 아래와 같이 테이블 생성해도록 하자. 여기에서 중요한 것을 size

1
2
3
4
 
CREATE TABLE enum_test (
  size ENUM('x-small', 'small', 'medium', 'large', 'x-large') NOT NULL,
  color ENUM('red', 'blue', 'black', 'white', 'pink')
);

이렇게 선언된 테이블에 총 2가지 방법은 데이터 삽입이 가능하다. 첫번째 방법은 아래와 같이 ENUM 타입 중 한가지 타입을 이용하여 넣는 방법이다.

1
2
3
4
5
6
7
 
INSERT INTO enum_test VALUES ('x-small', 'red');

+---------+-------+
| size    | color |
+---------+-------+
| x-small | red   |
+---------+-------+

두번째 방법은 아래와 같이 저장된 ENUM 타입의 ordering 숫자로 저장하는 법이다.

1
2
3
4
5
6
7
 
INSERT INTO enum_test VALUES (1, 1);

+---------+-------+
| size    | color |
+---------+-------+
| x-small | red   |
+---------+-------+

기본적으로 ENUM 값을 지정하지 않으면 NULL로 삽입되지만, 만약 NULL값을 원하지 않는다면, size 컬럼과 같이 NOT NULL 속성을 추가해주면 된다.

ENUM 타입 조회하기

ENUM 타입은 앞에서 살펴보았듯 ordering 숫자로 저장된다. 그렇기 때문에 해당 필드값을 기준으로 정렬하여 조회할 수 있다. 일단 정렬되는 데이터를 확인하기 위해 몇가지 데이터를 추가해보자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
INSERT INTO enum_test VALUES (1, 1);
INSERT INTO enum_test VALUES (3, 2);
INSERT INTO enum_test VALUES (2, 4);
INSERT INTO enum_test VALUES (4, 1);
INSERT INTO enum_test VALUES (5, 4);

+---------+-------+
| size    | color |
+---------+-------+
| x-small | red   |
| x-small | red   |
| medium  | blue  |
| small   | white |
| large   | red   |
| x-large | white |
+---------+-------+

몇가지 데이터를 추가한 후, 한번 아래와 같이 데이터 정렬을 해자.

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT * FROM enum_test ORDER BY size;

+---------+-------+
| size    | color |
+---------+-------+
| x-small | red   |
| x-small | red   |
| small   | white |
| medium  | blue  |
| large   | red   |
| x-large | white |
+---------+-------+

size를 기준으로 정렬을 했을 경우, 위와 같이 저장된 ordering 번호에 맞게 정렬되는 것을 확인할 수 있다. 그렇다면 만약 알파벳 순서대로 조회하고자 한다면 어떻게 해야할까? 제일 좋은 것은 알파벳 순서대로 저장하는 것이지만, 현실 가능성은 떨어진다. 중간에 어떠한 ENUM 타입이 생길지도 모르는데, 그걸 모두 고려해서 알파벳 순서대로 테이블을 추가하기란 쉽지 않다. 알파벳 순서대로 조회하기 위해선 다음과 같이 조회하면 된다.

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT * FROM enum_test ORDER BY CAST(size AS CHAR);

+---------+-------+
| size    | color |
+---------+-------+
| large   | red   |
| medium  | blue  |
| small   | white |
| x-large | white |
| x-small | red   |
| x-small | red   |
+---------+-------+

ENUM 타입인 size를 CHAR 형태로 타입 캐스팅 해준 후, 조회를 하는 것이다. 하지만 이또한 데이터가 많아질수록 성능이 안좋아질테니, 되도록이면 ENUM 타입보단 다른 형태의 데이터를 이용하여 저장하고 각각의 ENUM 타입에 대한 맵핑은 각각의 애플리케이션에서 정의하는 것이 좋을 것 같다.

SET 데이터 타입

마지막으로 살펴볼 타입은 SET 데이터 타입이다. SET 데이터 타입은 ENUM 타입이랑 다르게 중복값을 허용한다. 먼저 예시를 보기 위해 샘플로 아래와 같은 영화 장르 테이블을 생성하도록 하자.

1
2
3
 
CREATE TABLE genre (
   size SET('horror', 'comic', 'romance', 'documentary', 'sf')
);

모두 알다시피 하나의 영화에는 여러개의 장르를 선택할 수 있다. 코믹이면서 로맨스일 수도 있고, 공포이면서 SF일 수도 있다. 일단 하나의 영화에 하나의 장르만 존재한다고 가정하고 데이터를 추가해보자.

1
2
 
INSERT INTO genre VALUES ('horror');
INSERT INTO genre VALUES ('comic');

그렇다면 만약 여러개의 장르를 가져야할때는 어떻게 해야할까?

1
 
INSERT INTO genre VALUES ('comic,romance');

중복으로 넣어줄 데이터를 쉼표(,)와 함께 추가해주면 된다. 다만 여기에서 중요한 것은 쉼표 뒤에 띄어쓰기가 되어있으면 안되고, 무조건 붙어있어야 한다. 만약 띄어쓰기를 한 후 데이터를 추가하려고 하면 아래와 같은 에러가 발생한다.

1
2
3
 
INSERT INTO genre VALUES ('comic,romance');

-- ERROR 1265 (01000): Data truncated for column 'size' at row 1

또 다른 방법으로는 SET 데이터 타입의 주요한 특징에서 찾을 수 있다. SET 데이터의 경우 하나의 값마다 순서대로 Binary 값이 부여된다. Jenre 데이터 테이블의 SET 데이터를 바이너리로 표현하면 다음과 같이 표현할 수 있다.

VALUE DECIMAL BINARY
horror 1 00001
comic 2 00010
romance 4 00100
documentary 8 01000
sf 16 10000

이를 이용하여 다음과 같이 넣어줄 수 있다.

1
2
 
INSERT INTO genre VALUES (1); -- INSERT INTO genre VALUES ('horror'); 와 같다.
INSERT INTO genre VALUES (7); -- INSERT INTO genre VALUES ('horror,comic,romance'); 와 같다.

SET 데이터 타입 조회하기

SET 데이터를 조회하는 방법은 여러가지가 있다. 일단 첫번째로 일반적인 방법으로 조회할 수 있다.

1
2
3
4
5
6
7
8
 
-- 단일 조건을 이용하여 조회하는 경우
SELECT * FROM genre WHERE size='horror'; -- 정확하게 horror 값만 가지고 있는 데이터만 조회 가능하다.
SELECT * FROM genre WHERE size LIKE '%horror%'; -- horror 값을 포함한 데이터 모두가 조회 가능하다.

-- 여러 조건을 이용하여 조회하는 경우
SELECT * FROM genre WHERE size='horror,romance'; -- 정확하게 두가지 값을 모두 가지고 있는 데이터만 조회 가능하다.
SELECT * FROM genre WHERE size LIKE '%horror%' AND size LIKE '%romance%'; -- 'horror'와 'romance' 값 모두 가지고 있는 데이터 모두 조회 가능하다.
SELECT * FROM genre WHERE size & 1 AND size & 4; -- 위와 도잉ㄹ하다.

두번째 방법은 비트 연산자를 활용하는 방법이다.

1
2
 
SELECT * FROM genre WHERE size & 1; -- SELECT * FROM genre WHERE size LIKE '%horror%'; 와 동일하다.
SELECT * FROM genre WHERE size & 00001; -- 위와 동일하다.

마지막 방법은 FIND_IN_SET 함수를 이용하는 것이다. 이 함수는 첫번째 인자로는 SET 의 값을, 두번째 인자로는 필드 이름을 넣어주면 저장되 있다면 저장된 순서값을, 저장되어있지 않다면 0을 반환한다. 아래의 예제를 살펴보도록 하자.

1
 
SELECT FIND_IN_SET('romance', size) FROM genre;

이와 같이 조회하면 romance가 저장되어 있다면 3을 그렇지 않다면 면 0을 반환하는 것을 확인할 수 있다. 이 함수를 이용한다면 다음과 같은 조건으로 조회할 수 있다.

1
 
SELECT * FROM genre WHERE FIND_IN_SET('romance', size) > 0; -- romance 값이 저장된 모든 데이터가 조회 가능하다.

지금까지 MYSQL 의 데이터 타입에 대해서 살펴보았다. 혹여라도 이와 관련된 소스를 확인하고 싶다면 여기에서 확인할 수 있다.

카테고리

최근포스트