[혼공S] CH4-(1). MySQL의 데이터 형식

좀 더 효율적인 SQL을 만들고 활용하기 위해서는 데이터의 내부적인 구성을 이해하는 것이 필요하다.

 

데이터를 만들 때에는 형식을 설정해야 하는데, 크게 숫자형, 문자형, 날짜형이 있다. 

 

데이터 형식

정수형: 소수점이 없는 숫자, 예컨데 인원 수, 가격, 수량 등에 많이 사용하게 됨. 

• TINYINT: -128 ~ 127
• SMALLINT: -32,768 ~ 32,767
• INT: 약 -21억 ~ +21억
• BIGINT: 약 -900경 ~ +900경

아래와 같이 테이블을 만들고 각 열의 최댓값을 입력해본다.

USE market_db ; 
CREATE TABLE hongong4 (
	tinyint_col TINYINT,
    smallint_col SMALLINT, 
    int_col INT, 
    bigint_col BIGINT); 
INSERT INTO hongong4 VALUES(127, 32767, 2147483647, 9000000000000000000) ; 
SELECT * FROM hongong4 ;

여기에 각각 1씩 더한 값을 입력해보자. 이는 범위 밖의 값이므로, 입력하면 출력이 되지 않는다.

 

이전에 만들었던 member 테이블에서는 다음과 같이 mem_number열은 INT로, height은 SMALLINT로 지정하였다. 

하지만 그룹의 인원 수 같은 경우는 -21억과 21억까지는 필요없기 때문에 이를 TINYINT로 지정하는 것이 충분하다.

CREATE TABLE member 
(mem_id	      CHAR(8)       NOT NULL PRIMARY KEY, 
 mem_name     VARCHAR(10)   NOT NULL, 
 mem_number   TINYINT       NOT NULL,
 addr         CHAR(2)       NOT NULL, 
 phone1       CHAR(3), 
 phone2       CHAR(8), 
 height       SMALLINT, 
 debut_date   date
) ;

범위가 0부터 설정하는 것은 UNSIGNED 예약어를 통해서 할 수 있다. 예를 들어 TINYINT에 UNSIGNED를 같이 써주면, 

0부터 255까지 표현이 가능한 것이다.

CREATE TABLE member 
(mem_id	      CHAR(8)       NOT NULL PRIMARY KEY, 
 mem_name     VARCHAR(10)   NOT NULL, 
 mem_number   TINYINT       NOT NULL,
 addr         CHAR(2)       NOT NULL, 
 phone1       CHAR(3), 
 phone2       CHAR(8), 
 height       TINYINT UNSIGNED, 
 debut_date   date
 ) ;

 

문자형: 글자를 저장하기 위해 사용. 입력할 최대 글자의 개수를 지정해야 함. 

• CHAR(개수): 1~255 바이트
• VARCHAR(개수): 1~16383 바이트

CHAR는 고정길이 문자형으로 10자리로 지정하면 3글자만 사용하면 나머지 7자리는 낭비하게 된다.

VARCHAR는 가변길이 문자형으로, 10자리로 지정한 후 3자리만 저장하면 3자리만 사용하게 된다. 

이러한 특징 때문에 VARCHAR가 CHAR보다 공간을 효율적으로 사용할 수 있지만, MySQL에서는 CHAR가 내부적으로 속도가 좀 더 빠르다고 한다. 

 

대량의 데이터 형식

문자형인 CHAR는 최대 255자까지, VARCHAR는 최대 16383자까지 지정이 가능하다.

이것보다 더 큰 데이터를 저장하려면 다른 형식을 사용해야 한다. 

• TEXT: 1~65535, 텍스트형식
• LONGTEXT: 1~4294967295, 텍스트형식
• BLOB: 1~65535, BLOB형식
• LONGBLOB: 1~4294967295, BLOB형식

여기서 BLOB이라는 것은, 'Binary Long OBject'의 약자로 글이 아닌 이미지나 영상의 데이터라고 생각하면 된다. 이런 것들을 이진(binary)데이터라고 하는데, 테이블에 이미지나 영상과 같은 것을 저장하고 싶다면 BLOB형식을 써야한다. 

CREATE DATABASE netflix_db ; 
USE netflix_db ; 
CREATE TABLE movie
(movie_id        INT, 
 movie_title     VARCHAR(30), 
 movie_director  VARCHAR(20), 
 movie_star      VARCHAR(20), 
 movie_script    LONGTEXT,   -- 영화 스크립트나
 movie_film      LONGBLOB    -- 영화 영상 데이터
 ) ;

실수형: 소수점이 있는 숫자를 저장할 때 사용한다.

• FLOAT: 4바이트, 소수점 아래 7자리까지 표현.
• DOUBLE: 8바이트, 소수점 아래 15자리까지 표현.

 

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

• DATE: 3바이트, 날짜만 저장, YYYY-MM-DD형식으로 사용.
• TIME: 3바이트, 시간만 저장, HH:MM:SS 형식으로 사용.
• DATETIME: 8바이트, 날짜 및 시간을 저장, YYYY-MM-DD HH:MM:SS 형식으로 사용.

 

변수의 사용

SQL에서도 일반 프로그래밍처럼 변수를 선언하고 사용할 수 있는데 그 형식은 다음과 같다.

SET @변수이름 = 변수의 값 ; 
SELECT @변수이름 ;

USE market_db ; 
SET @myVar1 = 5 ;
SET @myVar2 = 4.25 ; 
SELECT @myVar1; 
SELECT @myVar1 + @myVar2 ;

SET @txt = '가수 이름 ====> ' ; 
SET @height = 166 ;
SELECT @txt, mem_name FROM member WHERE height > @height ;

SELECT문에서 행의 개수를 제한하는 LIMIT을, 변수를 사용해서 하면 오류가 난다.

이를 해결하기 위한 것이 PREPARE과 EXECUTE이다.

다음과 같이 쿼리문을 작성할 수 있다.

-- LIMIT에 변수 이용
SET @count = 3 ;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count ; -- 에러발생

-- PREPARE과 EXECUTE 사용
SET @count = 3 ; 
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?' ; 
EXECUTE mySQL USING @count ;

여기서 ?는 '현재는 모르지만 나중에 채워짐' 정도로 이해하면 된다.

 

데이터 형 변환

문자형을 정수형으로 바꾸는 거나 반대로 바꾸는 등의 것. 

형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환(explicit conversion)과 암시적인 변환(implicit conversion)이 있다. 

 

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

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

-- 함수를 이용한 명시적인 데이터 형 변환
SELECT AVG(price) AS '평균가격' FROM buy ; -- 소수점이 없는 정수형으로 바꾸기
SELECT CAST(AVG(price) AS SIGNED) '평균가격' FROM buy ; 
SELECT CONVERT(AVG(price), SIGNED) '평균가격' FROM buy ;

AVG(price)가 소수점이 있는 값이었기 때문에 이를 정수형으로 바꿔주고자 한다. 

SIGNED는 signed integer로 부호가 있는 정수를 의미한다.

SELECT CAST('2022$12$12' AS DATE) ; 
SELECT CAST('2022/12/12' AS DATE) ; 
SELECT CAST('2022%12%12' AS DATE) ; 
SELECT CAST('2022@12@12' AS DATE) ; 

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=') 
       '가격x수량', price * amount '구매액'  
       FROM buy ;

여기서 CONCAT은 함수를 이어주는 역할을 한다.

 

암시적인 변환

-- 암시적인 변환
SELECT '100' + '200' ; 
SELECT CONCAT('100', '200') ;

 

Reference

혼자 공부하는 SQL, 우재남 지음