[MySQL] - 데이터 형식, 형 변환

데이터베이스에는 다양한 데이터 형식이 존재합니다. 크게 숫자형, 문자형, 날짜형으로 시작하여, 세부적으로도 여러 데이터 형식으로 나눌 수 있습니다.

데이터 베이스에 저장되는 데이터의 형태는 실제로 아주 다양하기 때문에, 각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있습니다. 본 포스팅에서는 MySQL에서 자주 사용되는 데이터 형식을 위주로 정리해보겠습니다.

 

---

데이터 형식

📌 정수형

형식	바이트 수	범위	UNSIGNED
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
INT	4	약 -21억 ~ 21억	0 ~ 약 42억
BIGINT	8	약 -900경 ~ 900경	0 ~ 약 1800경

• 정수형은 소수점이 없는 숫자, 즉 인원 수, 가격, 수량 등에 많이 사용합니다. 추가로 BIT, MEDIUMINT 도 있으나 잘 사용하지 않습니다. 
• 정수형에 UNSIGNED 예약어를 붙이면 범위가 0부터 시작되는 양의 정수 범위로 데이터의 범위를 지정할 수 있습니다.

예시

CREATE TABLE member (
    mem_id INT PRIMARY KEY,
    mem_number TINYINT
    height TINYINT UNSIGNED
)

 

 

📌 실수형

형식	바이트 수	특징
FLOAT	4	소수점 아래 7자리 까지 표현
DOUBLE	8	소수점 아래 15자리 까지 표현

• 실수형은 소수점이 있는 숫자를 저장할 때 사용합니다.
• 과학 기술용 데이터가 아닌 이상 대부분 FLOAT로 지정하면 충분합니다.

 

 

📌 문자형

형식	바이트 수	특징
CHAR(개수)	1 ~ 255	고정길이 문자형
VARCHAR(개수)	1 ~ 16383	가변길이 문자형

• 문자형은 글자를 저장하기 위해 사용합니다.
• 테이블 생성시 입력할 최대 글자의 개수를 지정해야합니다.
• CHAR 은 고정길이 문자형입니다.
  • 데이터 형식을 CHAR(10)로 지정하면 해당 컬럼은 저장되는 데이터의 크기와 상관없이 무조건 10 바이트를 차지합니다.
  • CHAR(10) 에 'abc' 저장 → 10바이트 차지
• VARCHAR은 가변길이 문자형입니다.
  • 데이터 형식을 VARCHAR(10)로 지정하면 해당 컬럼은 저장되는 데이터의 크기에 따라 할당되는 바이트 수가 정해집니다. 
  • VARCHAR(10) 에 'abc' 저장 → 3바이트 차지

VARCHAR이 CHAR 보다 공간 효율면에서 우수하지만, MySQL 내부 성능에서는 CHAR이 조금 더 좋습니다.

따라서, 글자수가 고정된 경우 (연락처 등)에는 CHAR을, 고정되어있지 않은 경우(팀 이름 등) 에는 VARCHAR을 사용하는 것이 좋습니다.

 

예시

CREATE TABLE member (
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_number TINYINT NOT NULL,
	phone1 CHAR(3), -- 연락처 국번 02, 010
	phone2 CHAR(8)  -- 연락처 나머지
);

• 연락처를 정수형으로 지정할경우 010이나 02 같은 국번에서 앞에 있는 0이 사라지게 됩니다.
• 또한, 연락처는 더하기, 빼기등의 사칙 연산에 대한 필요가 없고, 대소비교에 대한 필요도 없기 때문에 문자형으로 저장합니다.

 

 

📌 대량 데이터 형식

형식		바이트 수
TEXT 형	TEXT	1 ~ 65535
	LONGTEXT	1 ~ 4294967295
BLOB 형	BLOB	1 ~ 65535
	LONGBLOB	1 ~ 4294967295

• CHAR이나 VARCHAR로는 각각 최대 255자, 16383자까지만 지정이 가능합니다. 따라서, 더 큰 데이터를 저장하기 위해 TEXT나 BLOB 형식을 사용합니다.
• TEXT은 최대 65535자까지,  LONGTEXT는 최대 42억자까지 저장이 가능합니다. 영화 대본이나 소설과 같은 대량의 텍스트를 저장할 때 지정합니다.
• BLOB은 Binary Long Objecct의 약자로, 글자가 아닌 이미지나 동영상 등의 데이터를 저장한다고 생각하면 됩니다. 마찬가지로 대량의 데이터를 저장하고 싶다면 LONGBLOB을 사용합니다.

예시

CREATE TABLE movie (
	movie_id CHAR(8) NOT NULL PRIMARY KEY,
	movie_number TINYINT NOT NULL,
	movie_script LONGTEXT,
	movie_film   LONGBLOB
);

 

 

📌 날짜형

형식	바이트 수	설명
DATE	3	날짜만 저장 'YYYY-MM-DD' 형식으로 사용
TIME	3	시간만 저장 'HH:MM:SS' 형식으로 사용
DATETIME	8	날짜와 시간을 저장 'YYYY-MM-DD HH:MM:SS' 형식으로 사용

• 날짜형은 날짜 및 시간을 저장할 때 사용합니다.
• DATE는 날짜만, TIME은 시간만, DATETIME은 시간과 날짜를 모두 저장할 때 사용합니다.
• 날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은 따옴표로 묶어주어야합니다.

예시

CREATE TABLE movie (
	movie_id CHAR(8) NOT NULL PRIMARY KEY,
	movie_number TINYINT NOT NULL,
	movie_script LONGTEXT,
	movie_film   LONGBLOB,
    create_date  DATE
);

-- DATE 삽입
INSERT INTO movie(movie_id, movie_number, create_date) VALUES('BLS', 4, '2022-02-02');

 

 

---

데이터 형 변환

💠 함수를 이용한 명시적인 변환

• CAST(), CONVERT() 함수로 직접 데이터의 형식을 바꿀 수 있습니다. 
• 이를 명시적인 변환이라고 합니다.

CAST (값 AS 데이터_형식 [(길이)])
CONVERT (값, 데이터_형식 [(길이)])

예시

-- 실수 → 정수 변경
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy;
SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;

-- 날짜형 변경 (다양한 구분자를 날짜형으로 변경 가능)
SELECT CAST('2022&02&12' AS DATE); -- 2022-02-12
SELECT CAST('2022/02/12' AS DATE); -- 2022-02-12

-- SQL 결과를 원하는 형태로도 표현 가능
SELECT num, CONCAT(CAST(price AS CHAR), 'X' CAST(amount AS CHAR), '=') AS '가격X수량'
	FROM buy;

 

💠 암시적인 반환

• CAST(), CONVERT() 함수를 사용하지 않고도 자연스럽게 형이 변환되는 것을 암시적인 변환이라고 합니다.

SELECT '100' + '200';  -- 300
SELECT 100 + '200';  -- 300

SELECT CONCAT(100, '200');  -- 100200 / 자동 문자형 형변환

주의
숫자와 문자를 더하거나, 비교할 때 MySQL에서는 숫자가 우선시 됩니다.
따라서, 문자를 저장하는 컬럼에 대해 숫자를 조건절로 걸었다면, 모든 컬럼에 대해 문자 → 숫자의 형 변환이 일어나게 되므로 성능에 엄청난 영향을 끼칠수 있습니다.