[SQL] MySQL의 데이터 형식

데이터 형식

 

정수형

정수형은 소수점이 없는 숫자를 나타낸다.

메모리를 효율적으로 사용하기 위해 범위에 따라 적절한 형식을 정해서 사용한다.

 

예를 들어 나이(age) 컬럼은 TINYINT나 SMALLINT를 쓰는게 적절하다.

하지만 TINYINT를 쓰기엔 127의 범위가 애매한데 이때 UNSIGNED를 사용하면 해결된다.

TINYINT UNSIGNED로 선언하면 범위가 0~255로 설정이 된다.

(범위의 크기는 그대로인데 음수 영역의 범위만큼 양수영역에 사용하는 것이다.)

나이는 음수인 경우가 없으므로 위처럼 사용하면 메모리를 효율적으로 사용할 수 있다.

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-123 ~ 127
SMALLINT	2	-32,768 ~ 32,767
INT	4	약 -21억 ~ 21억
BIGINT	8	약 -900경 ~ 900경

 

정수형 데이터 입력 예시

//여러 정수형 데이터 형식을 컬럼으로 갖는 테이블을 생성
CREATE TABLE intexample (
	tinyint_col TINYINT,
    smallint_col SMALLINT,
    int_col INT,
    bigint_col BIGINT
);

//정상적으로 데이터가 입력된다.
INSERT INTO intexample VALUES(127, 32767, 2147483647, 9000000000000000000);

//tinyint_col의 값이 TINYINT의 값을 벗어나므로 Out of range 에러가 발생한다
INSERT INTO intexample VALUES(128, 32767, 2147483647, 9000000000000000000);

+TIP)

0으로 시작하는 숫자 데이터의 경우(전화번호 등) 정수형이 아닌 문자형으로 선언하는 것이 좋다. 왜냐하면 정수형 데이터는 0으로 사직하는 숫자의 경우 앞의 0이 사라진다.

 

 

문자형

문자형은 글자를 저장하기 위해 사용하며 최대 글자의 개수를 지정해야한다.

데이터 형식	바이트 수
CHAR(개수)	1~255
VARCHAR(개수)	1~16383

 

CHAR는 고정길이 문자형이고 VARCHAR는 가변길이 문자형이다.

 

VARCHAR는 10글자로 지정해도 실제 사용하는 글자만큼의 메모리만 사용하지만 CHAR의 경우 글자 수에 상관없이 지정한 글자만큼 메모리를 사용한다.

 

하지만 CHAR가 VARCHAR보다 속도가 더 빠르기 때문에 고정길이 문자의 경우 CHAR를 사용하고 가변길이 문자의 경우 VARCHAR를 사용하면 된다.

 

 

 

대량의 데이터 형식

VARCAHR로 표현할 수 있는 대량의 데이터를 저장해야 하는 상황이 있을 수 있다.

대본이나 책처럼 수 만 글자의 문자를 다루는 경우 더 큰 데이터 형식이 필요하다.

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

BLOB은 Binary Long Object의 약자로 글자가 아닌 이미지 등의 파일을 이진 데이터로 저장하는 형식이다.\

 

 

 

실수형

소수점이 있는 숫자를 저장할 때 사용하는 데이터 형식이다.

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현

 

 

 

날짜형

날짜형은 날짜 및 시간을 저장할 때 사용한다.

이때도 날짜 또는 시간만 사용하는 경우 DATETIME보다 DATE, TIME을 사용해서 메모리를 효율적으로 사용하는 것이 좋다.

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장. YYYY-MM-DD 형식
TIME	3	시간만 저장. HH:MM:SS 형식
DATETIME	8	날짜 및 시간 저장. YYYY-MM-DD HH:MM:SS 형식

 

 

 

변수의 사용

변수를 선언하고 값을 대입할 수 있다.

//변수 선언 및 값 대입
SET @변수이름 = 변수의 값 ;

//변수의 값 출력
SELECT @변수이름 ;

 

조건절의 조건을 변수를 사용해 표현할 수 있다.

//height > 166인 데이터의 mem_name을 출력한다.
SET @height = 166;
SELECT mem_name FROM member WHERE height > @height ;

 

하지만, 문법상 LIMIT 뒤에 변수를 가질 수가 없다.

그래서 PREPARE와 EXCECUTE를 사용해 LIMIT 뒤에 원하는 변수를 지정하는 효과를 가져올 수 있다.

PREPARE은 SQL문을 준비해놓고 실행은 하지 않는다. 그리고 ?를 사용해 LIMIT 뒤에 어떤 수가 올지 명시하지 않는다.

EXECUTE를 사용해 ?에 @count를 대입해서 SQL을 실행한다.

//LIMIT 뒤에 변수가 올 수 없어 에러가 발생한다.
SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

//?에 @count를 대입해서 SQL을 실행한다.
SET @count = 3;
PREPARE tempSQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE tempSQL USING @count;

 

 

 

데이터 형 변환

문자형 ↔ 정수형으로 서로 형을 바꾸는 것을 형 변환이라고 한다. 

형 변환으로는 직접 함수를 사용해 변환하는 명시적인 변환과 별도의 지시 없이 변환되는 암시적인 변환이 있다.

 

명시적인 형 변환

데이터 형식을 바꾸는 함수는 CAST()와 CONVERT()가 있다. 둘 다 동일한 기능을 한다.

CAST 문법: CAST (값 AS 데이터_형식 [ (길이) ] )

CONVERT 문법: CONVERT (값, 데이터_형식 [ (길이) ] )

 

-- AVG(price)는 실수형 데이터이다.
SELECT AVG(price) '평균 가격' FROM buy;

-- CAST를 이용해 INT로 형 변환시킨다.
SELECT CAST(AVG(price) AS SIGNED)  '평균 가격'  FROM buy ;

-- CONVERT를 이용해 INT로 형 변환시킨다.
SELECT CONVERT(AVG(price) , SIGNED)  '평균 가격'  FROM buy ;

 

 

암시적인 형 변환

서로 다른 데이터 형식 간에 연산을 하게 되면 연산 전에 데이터 형식을 형 변환하게 되는데 이것을 암시적 형 변환이라 한다.

SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)
SELECT CONCAT('100', '200'); -- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT(100, '200'); -- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)
SELECT 1 > '2mega'; -- 정수인 2로 변환되어서 비교
SELECT 3 > '2MEGA'; -- 정수인 2로 변환되어서 비교
SELECT 0 = 'mega2'; -- 문자는 0으로 변환됨

※ CONCAT은 두개의 문자형을 붙여서 출력해주는 함수다.