MySQL 데이터 형식

시작하기 전에

데이터 형식의 활용

• 숫자형
• 문자형
• 날짜형

이러한 데이터 형식을 효율적으로 사용해야한다. 예를 들어이름을 저장하기 위해 내부적으로 100글자를 지정할 칸을 준비하는 것은 상당한 낭비가 될 수 있다. 데이터 형식에 대해서 이해하고 적절한 데이터 형식을 고르는 안목을 키우자

데이터 형식

자주 사용하는 것만 살펴보도록 하자

정수형

정수형은 소수점이 없는 숫자, 즉 인원수, 가격 ,수량 등에 많이 사용한다. 정수형의 크기와 범위는 다음과 같다.

데이터 형식	바이트 수	숫자 범위
tinyint	1	-128 ~ 127
smallint	2	-32,768 ~ 32,767
int	4	약 -21억 ~ +21억
bigint	8	약 -900경 ~ _ 900경

회원 테이블 살펴보기 - 숫자형

CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  	`````	CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);

현재 인원수 열이 int 형으로 되어있는데 숫자 범위가 너무 크다. 그래서 최대 127명까지 지정해줄 수 있는 tinyint형이 적절하다. 평균 키 열은 SMALLINT로 지정해서 -32767 ~ 32767까지 지정할 수 있다. 키 역시 30000cm를 넘을리가 없기 때문에 TINYINT를 고려할 수도 있다. 하지만 200cm를 넘는 사람도 있기 때문에 범위가 부족하다.

이를 해결하기 위해 UNSIGNED 예약어를 사용할 수 있다. TINYINT와 TINYINT UNSIGNED 모두 1바이트의 크기이다 1바이트는 256개라서 -128 ~ +127로 표현하거나 0 ~ 255 모두 256개를 표현하는 것이다. 결국 같은 크기를 표시하지만 범위가 달라서 때에 따라 알맞는 표현을 써야한다

바뀐 회원테이블

그래서 회원테이블은 이렇게 바꾸는 것이 좋다

CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    TINYINT NOT NULL,  -- 인원수
  addr	  	`````	CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	TINYINT UNSIGNED,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);

문자형

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

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

CHAR

문자를 의미하는 Character의 약자로 고정길이 문자형이라고 부른다. 예를 들어 CHAR(10)에 ‘가나다’ 세글자만 저장해도 10자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 7자리는 낭비하게 됩니다.

VARCHAR

가변길이 문자형으로 3자리를 적으면 3자리만 사용합니다.

효율성

• 공간 효율성

VARCHAR > CHAR

• 속도

CHAR > VARCHAR

예를들어 거주 지역을 서울/부산/경기 등등 과 같이 시도만 저장할 경우에는 모두 2글자로 일정합니다. 이때는 CHAR(2)라고 저장하는 것이 좋습니다. 반면 가수 그룹의 이름은 글자 길이가 다양하기 때문에 VARCHAR로 설정하는 것이 좋습니다

회원 테이블 살펴보기 - 문자형

회원테이블의 문자형을 확인해보겠습니다

CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    TINYINT NOT NULL,  -- 인원수
  addr	  	`````	CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	TINYINT UNSIGNED,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);

전화번호

전화번호 같은 경우에는 맨앞의 숫자가 0이어야 하는데 정수형으로 지정했을 때 0이 사라져버린다. 그래서 CHAR로 지정한다

또한 문자형으로 지정하는 이유는 전화번호가 숫자로써 의미가 없기 때문이다. 숫자로써 의미를 가지려면 아래 두가지 중 한가지를 충족해야한다

• 더하기, 빼기등의 연산에 의미가 있다.
• 크다, 작다 같은 순서에 의미가 있다.

이 두가지 중 어떤것에도 해당하지 않기 때문에 전화번호는 숫자가 아닌 문자로 저장한다.

대량의 데이터 형식

CHAR, VARCHAR의 범위를 넘어서는 데이터를 만들고 싶을 때는 이런 데이터 형식을 사용한다.

데이터형식		바이트 수
Text 형식	TEXT	1~65535
	LONG	1~4294967295
Blob 형식	BLOB	1~65535
	LONGBLOB	1~4294967295

이렇게 많은 데이터를 저장할 일이 있을까 싶지만 소설이나 영화 대본을 저장한다면 가능한 일이다.

또한 BLOB이라는 용어가 등장했는데 BLOB은 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상등의 데이터라고 생각하면 된다. 이것을 이진 데이터라고 부른다. 테이블에 사진이나 동영상 같은 것을 저장하고 싶다면 BLOB 이나 LONGBLOB로 데이터 형식을 지정해야 합니다.

실수형

실수형은 소수점이 잇는 숫자를 지정할 때 사용합니다.

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

과학 기술용 데이터가 아닌 이상 FLOAT면 충분하다. 예를 들어 시력같은 경우가 있을 것이다.

날짜형

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

데이터 형식 바이트 수 설명

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

인터넷 마켓 데이터베이스의 데뷔일자는 DATE로 설정했습니다. 만약 구매 테이블의 구매한 기록이 필요하면 DATETIME으로 설정하는 것이 적절합니다. 참고로 날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은 따옴표로 묶어야합니다.

변수의 사용

변수의 선언과 대입

SET @변수이름 = 변수의 값; --> 변수의 선언 및 값 대입
SELECT @변수이름;  --> 변수의 값 출력

변수는 워크벤치를 종료하면 없어진다. 그래서 임시로 사용한다고 생각하면 된다.

활용해보기

USE market_db;
SET @myVar1 = 5;
SET @myVar2 = 4.25;
--> 변수 선언하고 정수 또는 실수 대입

SELECT @myVar1;
--> 변수 내용 출력
SELECT @myVar1 + @myVar2;
--> 변수끼리 연산한 후에 출력

SET @txt = '가수이름 ==>';
SET @height = 166;
--> 변수를 선언하고 문자열 또는 정수를 대입
SET @txt, mem_name FROM member WHERE height > @height;
--> 테이블을 조회하면서 변수를 활용

LIMIT에 조건문을 사용해보자

--> 변수 준비
SET @count = 3;

--> 쿼리문을 실행하지 않고 준비만 해놓는 상태 '?'에 무언가 들어갈 것을 알려준다
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
--> '?'에 들어갈 내용을 넣어준다
EXECUTE mySQL USING @count

데이터 형 변환

형변환에서는 직접 함수를 사용해서 변환하는 명시적인 변환과 별도의 지시없이 자연스럽게 변환되는 암시적인 변환이 있습니다

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

• CAST
• CONVERT

형식만 다를 뿐 동일한 기능을 한다

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

예시

구매 테이블에서 평균 가격을 구하는 SQL

SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
-- 또는
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy;

<aside> 💡 SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 이야기 합니다.

</aside>

날짜 포멧 변경

SELECT CAST('2023$12$12' AS DATE);

쿼리문 결과를 원하는 형태로 표현할때도 사용 가능

날짜와 수량을 곱해 실제 구매액을 표시하는 쿼리문

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

암시적인 변환

암시적인 변환은 함수를 따로 사용하지 않고 자연스럽게 형이 변하는 것을 의미한다

예시

문자형끼리는 더할 수 없기 때문에 자동으로 정수형으로 바뀌어서 300으로 출력됨

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

만약에 100200으로 출력하고싶다면 CONCAT() 함수를 사용해야함

SELECT CONCAT('100','200') --> 100200

숫자와 문자를 CONCAT() 함수로 연결하면 어떻게 될까요?

SELECT CONCAT(100,'200'); --> 100200
SELECT 100 + '200'; --> 300

함수를 사용하면 숫자가 문자로 변환되지만 함수를 사용하지 않으면 문자가 숫자로 변환된다.