[MySQL] 데이터 형식, 변수, 형 변환

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

---

테이블을 만들 때는 데이터 형식을 설정해야 한다.

데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있다.

또 세부적으로는 여러 개로 나뉘기도 한다.

이렇게 다양한 데이터 형식이 존재하는 이유는 실제로 데이터 형태가 다양하기 때문이다.

각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있다.

 

데이터 형식

정수형

정수형은 소수점이 없는 숫자, 즉 인원 수, 가격, 수량 등에 많이 사용된다.

정수형의 크기와 범위는 아래와 같다.

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

 

UNSIGNED 예약어를 사용하면, 음수를 표현했던 범위를 온전히 양수로 표현할 수 있다.

UNSIGNED 예약어는 모든 정수에 사용 가능하다.

 

문자형

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

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

문자형에는 BINARY, VARBINARY도 있지만 잘 사용하지 않는다.

CHAR는 고정길이 문자형이라고 부른다.

즉, 자릿수가 고정되어 있다.

예를 들어 CHAR(10)에 '가나다' 3글자만 저장해도 10자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 7자리는 낭비하게 된다.

이와 달리 VARCHAR는 가변길이 문자형으로, VARCHAR(10)에 '가나다' 3글자를 저장할 경우 3자리만 사용한다.

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

 

대량의 데이터 형식

데이터 형식		바이트 수
TEXT 형식	TEXT	1~65,535
	LONGTEXT	1 ~ 42억 9496만 7295
BLOB 형식	BLOB	1~65,535
	LONGBLOB	1 ~ 42억 9496만 7295

TINYTEXT, MEDIUMTEXT, TINYBLOB, MEDIUMBLOB 등도 있지만 잘 사용하지 않는다.

 

실수형

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

데이터 형식	바이트 수	설명
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 @myVar1 = 5 ;
SET @myVar2 = 4.25 ;
SELECT @myVar1 + @myVar2 ;

 

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

 

PREPARE와 EXECUTE

아래의 코드는 오류가 발생한다.

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

이유는 LIMIT에는 변수를 사용할 수 없기 때문이다.

이를 해결하는 것이 PREPARE와 EXECUTE이다.

PREPARE는 실행하지 않고 SQL 문만 준비해 놓고 EXECUTE에서 실행하는 방식이다.

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

1. PREPARE에서 SELECT LIMIT ? 문을 실행하지 않고 MySQL이라는 이름으로 준비만 해놓는다.
2. EXECUTE로 mySQL에 저장된 SELECT문을 실행할 때, USING으로 물음표에 @count 변수에 값을 대입하는 것이다.

 

데이터 형 변환

문자형을 정수형으로 바꾸거나, 반대로 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환이라고 부른다.

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

 

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

데이터 형식을 변환하는 함수는 CAST(), CONVERT(), CAST(), CONVERT()는 형식만 다를 뿐 동일한 기능을 한다.

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

가격은 실수보다 정수로 표현하는 것이 좋다.

아래와 같이 CAST()나 CONVERT() 함수를 사용해서 정수로 표현할 수 있다.

CAST()나 CONVERT() 함수 안에 올 수 있는 데이터 형식은 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등이다.

SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 의미한다.

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

 

날짜는 다양한 구분자를 날짜형으로 변경할 수도 있다.

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

 

SQL의 결과를 원하는 형태로 표현할 때도 사용할 수 있다.

가격(price)과 수량(amount)을 곱한 실제 구매액을 표시하는 SQL문은 아래와 같이 작성할 수 있다.

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

가격과 수량은 정수지만, CAST() 함수를 통해 문자로 바꾸었다.

CONCAT() 함수는 문자를 이어주는 역할을 한다.

 

묵시적인 변환

SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)

 

SELECT CONCAT('100', '200'); -- 문자와 문자를 연결 (문자로 처리)

 

SELECT CONCAT(100, '200'); -- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)