[SQL] 04-1 MySQL의 데이터 형식
SQL 고급 문법
MySQL의 데이터 형식
테이블을 만들 때는 데이터 형식을 설정해야 한다.
데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있다.
이렇게 다양한 데이터 형식이 존재하는 이유는 실제로 저장될 데이터의 형태가 다양하기 때문이다.
각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있다.
예를 들어, 이름을 저장하기 위해 내부적으로 100 글자를 저장할 칸을 준비하는 것은 상당히 낭비이다.
이러한 낭비가 누적되면 SQL을 아무리 잘 만들어도 비효율적일 수밖에 없다.
데이터 형식
MySQL에서 제공하는 데이터 형식의 종류는 수십 개 정도이고, 각 데이터 형식마다 크기나 표현할 수 있는 숫자의 범위가 다르다.
정수형
정수형은 소수점이 없는 숫자, 즉 인원 수, 가격, 수량 등에 많이 사용한다.
| 데이터 형식 | 바이트 수 | 숫자 범위 |
|---|---|---|
| TINYINT | 1 | -128 ~ 127 |
| SMALLINT | 2 | -32,768 ~ 32,767 |
| INT | 4 | 약 -21억 ~ +21억 |
| BIGINT | 8 | 약 -900경 ~ +900경 |
정수형을 사용하는 예제를 살펴보겠다.
앞에서 만들었던 인터넷 마켓의 회원 테이블(member)에서 인원수(mem_number) 열은 INT로, 평균 키(height) 열은 SMALLINT로 지정했다.
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
mem_number INT NOT NULL,
addr CHAR(2) NOT NULL,
phone1 CHAR(3),
phone2 CHAR(8),
height SMALLINT,
debut_date DATE
);
인원수(mem_number) 열은 INT로 지정해서 -21억 ~ +21억까지 저장할 수 있다.
하지만 인원이 이렇게 많을 필요는 없으므로 최대 127명까지 지정할 수 있는 TINYINT로 지정해도 충분하다.
평균 키(height) 열은 SAMLLINT로 지정해서 -32768 ~ 32767까지 저장할 수 있다.
키 역시 30000cm가 넘을 리는 없으므로 TINYINT를 고려할 수 있다.
하지만 TINYINT는 -128 ~ +127로 200CM가 넘는 사람도 있으므로 범위가 부족하다.
이를 해결하기 위해 값의 범위가 0부터 시작되는 UNSIGNED 예약어를 사용할 수 있다.
TINYINT와 TINYINT UNSIGNED 모두 1바이트의 크기이다.
1바이트는 256개를 표현하므로 -128 ~ +127로 표현하고나, 0 ~ 255로 표현하거나 모두 256개를 표현하는 것이다.
결국 회원 테이블은 다음과 같이 구성하는 것이 더 효율적이다.
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
);
나머지 정수형도 마찬가지로 UNSIGNED를 붙이면 0부터 범위가 지정된다.
문자형
문자형은 글자를 저장하기 위해 사용하며, 입력할 최대 글자의 개수를 지정해야 한다.
| 데이터 형식 | 바이트 수 |
|---|---|
| CHAR(개수) | 1 ~ 255 |
| VARCHAR(개수) | 1 ~ 16383 |
CHAR는 문자를 의미하는 Character의 약자로, 고정길이 문자형이라고 부른다.
즉, 자릿수가 고정되어 있다.
예를 들어 CHAR(10)에 ‘가나다’ 3글자만 저장해도 10자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 7자리는 낭비하게 된다.
이와 달리 VARCHAR(Variable Character)는 가변 길이 문자형으로, VARCHAR(10)에 ‘가나다’ 3글자를 저장할 경우 3자리만 사용한다.
VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만, MySQL 내부적으로 성능(빠른 속도)면에서는 CHAR로 설정하는 것이 조금 더 좋다.
회원 테이블의 문자형을 확인해보겠다.
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
);
회원 아이디(mem_id)는 BLK, APK, GRL 등 3글자로 입력되는데, 데이터 형식은 CHAR(8)로 설정되어 있다.
CHAR(8)을 CHAR(3)으로 줄여도 되지만 향후에 더 긴 회원 아이디를 만들 수 있다고 가정하고 CHAR(8)로 설정했다.
VARCHAR(8)로 변경해도 별 문제는 없다.
국번(phone1)은 02, 031, 055 등과 같이 제일 앞에 0이 붙어야 하는데 정수형으로 지정하면 0이 사라지므로 CHAR로 지정했다.
연락처 전화번호(phone2) 역시 모두 숫자로 이루어져서 정수형으로 지정해야 할 것 같지만, CHAR로 지정되어 있다.
이유는 전화번호가 숫자로서 의미가 없기 때문이다.
숫자로서 의미를 가지려면 다음 2가지 중 1가지는 충족해야 한다.
- 더하기/빼기 등의 연산에 의미가 있다.
- 크다/작다 또는 순서에 의미가 있다.
전화번호는 위 2가지 중 어떤 것에도 해당하지 않으므로 숫자가 아닌 문자로 지정했다.
대량의 데이터 형식
문자형인 CHAR는 최대 255자까지, VARCHAR는 최대 16383자까지 지정이 가능하다.
그래서 더 큰 데이터를 저장하려면 다음과 같은 형식을 사용한다.
| 데이터 형식 | 바이트 수 | |
|---|---|---|
| TEXT 형식 | TEXT | 1 ~ 65535 |
| TEXT 형식 | LONGTEXT | 1 ~ 4294967295 |
| BLOB 형식 | BLOB | 1 ~ 65535 |
| BLOB 형식 | LONGBLOB | 1 ~ 4294967295 |
TEXT로 지정하면 최대 65535자까지, LONGTEXT로 지정하면 최대 약 42억자까지 지정된다.
BLOB는 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터라고 생각하면 된다.
이런 것을 이진(Binary) 데이터라고 부른다.
테이블에 사진이나 동영상과 같은 것을 저장하고 싶다면 BLOB이나 LONGBLOB로 데이터 형식을 지정해야 한다.
LONGTEXT 및 LONGBLOB로 설정하면 각 데이터는 최대 4GB까지 입력할 수 있다.
실수형
실수형은 수수점이 있는 숫자를 저정할 때 사용한다.
| 데이터 형식 | 바이트 수 | 설명 |
| 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 @변수이름;
변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 없어진다.
그러므로 임시로 사용한다고 생각하면 된다.
간단한 예를 살펴보겠다.
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;
[Output]
Result 1:
5
Result 2:
9.250000000000000000000000000000
Result 3:
가수 이름 ==> 소녀시대
가수 이름 ==> 잇지
가수 이름 ==> 트와이스
SELECT 문에서 행의 개수를 제한하는 LIMIT에도 변수를 사용해보겠다.
SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;
이 SQL은 SELECT 문에서 오류가 발생한다.
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;
LIMIT 다음에 오는 물음표(?)는 ‘현재는 모르지만 나중에 채워짐’ 정도로 이해하면 된다.
데이터 형 변환
문자형을 정수형으로 바꾸거나, 반대로 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환(type conversion)이라고 부른다.
형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환(explicit conversion)과 별도의 지시 없이 자연스럽게 변환되는 암시적인 변환(implicit conversion)이 있다.
함수를 이용한 명시적인 변환
데이터 형식을 변환하는 함수는 CAST( ), CONVERT( )이다.
CAST( ), CONVERT( )는 형식만 다를 뿐 동일한 기능을 한다.
CAST (값 AS 데이터_형식 [(길이)])
CONVERT (값, 데이터_형식 [(길이)])
간단한 예를 살펴보겠다.
다음은 market_db의 구매 테이블(buy)에서 평균 가격을 구하는 SQL이다.
SELECT AVG(price) AS '평균 가격' FROM buy;
[Output] 142.9167
결과가 실수로 나왔다.
가격은 실수보다 정수로 표현하는 것이 보기 좋을 것 같으므로 다음과 같이 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;
[Output] 143
이번에는 날짜를 확인해보겠다.
다양한 구분자를 날짜형으로 변경할 수도 있다.
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);
[Output] 2020-12-12
SQL의 결과를 원하는 형태로 표현할 때도 사용할 수 있다.
가격(price)과 수량(amount)을 곱한 실제 구매액을 표시하는 SQL을 다음과 같이 작성할 수 있다.
SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=') '가격X수량', price*amount '구매액'
FROM buy;
가격(price)과 수량(amount)은 정수지만, CAST( ) 함수를 통해 문자로 바꿨다.
CONCAT( ) 함수는 문자를 이어주는 역할을 하며, 여기서는 ‘30X2=’과 같은 형태의 문자로 만들어서 출력했다.
암시적인 변환
암시적인 변환은 CAST( )나 CONVERT( ) 함수를 사용하지 않고도 자연스럽게 형이 변환되는 것을 말한다.
다음 예를 살펴보겠다.
SELECT '100' + '200';
[Output] 300
문자 ‘100’과 ‘200’을 더했는데 문자는 더할 수 없으므로 자동으로 숫자 100과 200으로 변환해서 덧셈을 수행했다.
만약에 문자 ‘100’과 ‘200’을 연결한 ‘100200’으로 만들려면 CONCAT( ) 함수를 사용해야 한다.
SELECT CONCAT('100', '200');
[Output] 100200
숫자와 문자를 CONCAT( ) 함수로 연결하면 어떻게 될까?
SELECT CONCAT(100, '200');
SELECT 100 + '200';
[Output]
Result 1: 100200
Result 2: 300
결과를 보면 숫자 100이 문자 ‘100’으로 변환되어서 연결된 것을 확인할 수 있다.
CONCAT( ) 함수를 사용하지 않고 숫자 100과 문자 ‘200’을 더하면 뒤의 문자가 숫자 200으로 자동 변환되어 300이 출력된다.