[MySQL] 6. MySQL의 데이터 형식(정수형, 문자형, 실수형, 날짜형)과 형 변환

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

 

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

 

아래와 같이 했을 때 첫 번째로 넣은 데이터의 경우 정상적으로 데이터가 삽입되며 두 번째 데이터의 경우 오류가 발생한다.

CREATE TABLE hongong4 (
    tinyint_col TINYINT,
    smallint_col SMALLINT,
    int_col INT,
    bigint_col BIGINT );
    
INSERT INTO hongong4 VALUES (127, 32767, 2147483647, 9000000000000000000);
INSERT INTO hongong4 VALUES (128, 32768, 2147483648, 9000000000000000001);

 

Result

 

 

여기서 핵심은 인원 수, 키 등 값의 범위가 작을 것으로 예상되는 데이터의 경우 INT보다는 SMALLINT 또는 TINYINT 자료형을 사용하여 구성하면 더 효율적으로 데이터를 저장할 수 있다는 것입니다.

---

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

 

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

 

CHAR는 문자를 의미하는 Character의 약자로, 고정길이 문자형이라고 부른다. 즉, 자릿수가 고정되어 있다. 이와 달리 VARCHAR(Variable Character)는 가변길이 문자형으로 자릿수가 가변적이다.

 

따라서 VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만, MySQL 내부적으로 성능(속도) 면에서는 CHAR로 설정하는 것이 조금 더 좋다.

 

단순하게 CHAR는 글자의 개수가 고정된 경우, VARCHAR는 글자의 개수가 변동될 경우에 사용하는 것이 좋다.

 

참고로 전화번호와 같은 데이터는 보통 CHAR로 지정한다. 이유는 전화번호가 숫자로서 의미가 없기 때문이다. 즉, 더하기/빼기 등의 연산과 크다/작다 또는 순서의 의미가 없기 때문에 CHAR로 취급하는 것이 일반적이다.

 

또한 소설이나 영화 대본 등 길이가 매우 긴 텍스트를 저장해야 하는 경우 위에서 언급한 두 개의 자료형으로는 데이터를 저장할 수 없을 것이다.

이 경우 사용하는 자료형이 TEXT 또는 LONGTEXT, BLOB 또는 LONGBLOB이다.

 

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

 

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와 DOUBLE은 거의 비슷하다. 소수점 아래 어디까지 정밀하게 표현하는지의 차이인데, 과학 기술용 데이터가 아닌 이상 FLOAT면 충분하다.

 

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

---

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

 

DATE는 날짜만, TIME은 시간만 저장한다. 날짜와 시간을 둘 다 저장하고 싶다면 DATETIME을 사용한다.

 

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

---

SQL도 일반 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. 다만 변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 사라진다. 그러므로 임시로 사용한다고 생각하면 된다.

 

변수를 사용해서 다음과 같은 연산이 가능하다.

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

SELECT @myVar1;
SELECT @myVar1 + @myVar2;

 

Result

 

 

변수를 사용해서 데이터를 출력해볼 수도 있다.

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

 

Result

 

 

단 변수를 다음과 같이 사용할 경우 오류가 발생한다.

SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

 

이 경우 PREPARE ~ FROM과 EXECUTE ~ USING 문을 사용하여 처리할 수 있다.

종종 사용하는 구문이므로 참고로 알아두면 좋을 것 같다.

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

 

Result

---

데이터 형 변환이란 문자형을 정수형으로 바꾸거나, 정수형을 문자형으로 바꾸는 것을 말한다.

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

 

데이터 형식을 변환하는 함수에는 CAST, CONVERT가 있다. 이 둘은 형식만 다를 뿐 동일한 기능을 수행한다.

 

아래의 예는 각각의 함수를 사용하여 소수형을 정수형으로 바꾸어 출력하는 구문이다.

SELECT AVG(price) '평균 가격' FROM buy;

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

Result

 

 

문자형을 날짜형으로 형 변환(결과는 모두 같음)

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);

 

Result

 

 

CONCAT을 사용하면 문자를 이어 붙일 수 있다.

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

 

Result

 

 

암시적인 형 변환의 예

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