[MySQL] 데이터 형식 (숫자, 문자, 날짜형)

1. MySQL의 데이터 형식

 

1) 숫자형

- 정수형: 소수점이 없는 숫자

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

* Out of range 오류: 입력값의 범위가 벗어났음을 의미

* UNSIGNED 예약어: 정수형에 해당 예약어를 붙이면 범위가 0부터 지정 (예: TINYINT는 0~255 사이의 수가 됨)

 

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

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

* 과학 기술용 데이터가 아닌 이상 FLOAT 형이면 충분

 

2) 문자형

- 글자를 저장하기 위해 사용, 입력할 초치대 글자의 개수를 지정함

- CHAR은 고정길이 문자형, VARCHAR은 가변길이 문자형

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

* VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만, MySQL 내부적으로 성능(빠른 속도)면에서는 CHAR로 설정하는 것이 좋음 + CHAR은 핸드폰 번호처럼 글자의 개수가 고정된 경우, VARCHAR은 개수가 변동될 경우 사용 권장

* 전화번호의 경우, 연산과 순서에 의미가 없기에 문자형으로 많이 지정하는 편

 

주의 ★ 문자형 데이터 비교 시, CHAR(n)은 공백을 채운 문자열이므로 VARCHAR(n)과 동등비교가 안될 수도 있음 !!

 

※ 대량의 데이터 형식 ※

VARCHAR(16384) 이렇게 지정할 경우 Error Code에 Column length too big

이러한 오류가 나오게 되면, 열의 길이를 너무 크게 설정했다는 것

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

 

이러한 바이트 수 데이터 형식이 왜 필요한가?

소설이나 영화 대본 같은 내용을 저장한다면 LONGTEXT도 충분히 필요함. BLOB은 이진 데이터로, 이미지나 동영상의 데이터라고 생각하면 됨. LONGTEXT와 LONGBLOB으로 설정할 경우, 최대 4GB까지 입력할 수 있음

 

예) 만약 넷플릭스 데이터베이스를 만든다면

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
)

 

3) 날짜형

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

* 날짜와 시간을 입력할 때에는 문자와 마찬가지로 작은 따옴표로 묶어 주어야 함.

 

-- 테이블 생성 예시
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
);

 

 

2. 변수의 사용

 

SQL도 다른 일반 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있음

MySQL에서 변수는 @변수이름 형식으로 만들고, SET 문으로 변수에 값을 대입함

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

 

단, 변수는 MySQL 워크벤치를 재시작할 때까지는 유지하지만, 종료되면 사라짐 (임시저장의 느낌)

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

SELECT @myVar1;
SELECT @myVar2;

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

 

PREPARE와 EXECUTE

- PREPARE은 실행하지 않고 SQL 문만 준비해놓고, EXECUTE에서 실행하는 방식

- LIMIT에서는 변수를 사용할 수 없으므로 위의 명령어를 사용하여 해결함

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

* ?은 현재는 모르지만 나중에 채워짐의 의미. USING으로 물음표(?)에 @count 변수의 값을 대입

 

 

3. 데이터 형 변환

 

1) 명시적인 변환: CAST(), CONVERT() 함수를 사용

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

- 두 함수는 형식만 다를 뿐 동일한 기능을 수행함

- 함수에 들어올 수 있는 데이터 형식: CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME

(SIGNED은 부호가 있는 정수, UNSIGNED는 부호가 없는 정수)

 

-- 예시 1
SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
SELECT CONVERT(AVG(price) AS SIGNED) '평균 가격' FROM buy;

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

 

2) 암시적인 변환: 함수를 사용하지 않아도 자연스럽게 형이 변환되는 것

- 숫자와 문자를 연산할 때, CONCAT()을 사용하면 숫자가 문자로 변하고, 더하기만 사용하면 문자가 숫자로 변한 후에 연산