DATA_TYPES
MySQL은 여러 가지 데이터 유형을 지원(문자열, 숫자, 날짜, 시간)한다.
숫자 데이터 형식
- 정수 또는 실수 등의 숫자를 표현한다.
- FLOAT이나 DOUBLE형은 큰 범위의 숫자를 저장할 수 있지만 정확하지 않은 근사치를 저장한다.
따라서 실수 형을 저장하고 싶어도 DECIMAL을 사용하는 것이 바람직하다.
| 데이터 형식 | 바이트 수 | 숫자 범위 | 설명 |
|---|---|---|---|
| BIT(N) | N/B | 1~64Bit 표현, b'0000'형식으로 표현 | |
| TINYINT | 1 | -128 ~ 127 | 정수 |
| SMALLINT | 2 | -32,768 ~ 32,767 | 정수 |
| MEDIUMINT | 3 | -8,388,608 ~ 8,388,607 | 정수 |
| INT, INTEGER | 4 | 약-21억 ~ +21억 | 정수 |
| BIGINT | 8 | 약 -900경 ~ +900경 | 정수 |
| FLOAT | 4 | 3.40E+38 ~ -1.17E-38 | 소수점 아래 7자리까지 표현 |
| DOUBLE | 8 | -1.22E-308 ~ 1.79E+308 | 소수점 아래 15자리까지 표현 |
| DECIMAL(m,[d]), NUMBER(m,[d]) | 5~17 | -10^38+1 ~ 10^38-1 | 전체 자릿수(m)와 소수점 이하 자릿수(d)를 가진 |
문자 데이터 형식
- CHAR는 고정길이 문자형으로 자릿수가 불변이다.
- VARCHAR는 가변길이 문자형으로 자릿수가 가변이다.
(큰 자릿수를 설정해도 저장할 공간을 효율적으로 사용할 수 있다.) - CHAR 형식으로 설정하는 것이 INSERT/UPDATE 시에 일반적으로 더 좋은 성능을 발휘한다.
- 대용량 데이터는 TEXT계열의 데이터 형식으로 저장한다.
- BLOB(Binary Large Object)은 사진 파일, 동영상 파일, 문서 파일 등의 대용량 이진 데이터를 저장한다.
- ENUM은 열거형 데이터를 사용(요일이나 카테고리 등) 시 활용되는 방식이다.
- SET은 최대 64개를 준비한 후에 2개씩 세트로 데이터를 저장하는 방식을 사용 시 활용되는 방식이다.
- LONGTEXT는 대용량 문서 데이터, LONGBLOB은 동영상 파일과 같은 큰 바이너리 파일 저장 시에 활용할 수 있다.
| 데이터 형식 | 데이터 형식 | 바이트 수 | 설명 |
|---|---|---|---|
| CHAR(n) | 1 ~ 255 | 고정길이 문자형이며 n을 1부터 255까지 지정, 그냥 CHAR만 쓰면 CHAR(1)과 동일 | |
| VARCHAR(n) | 1 ~ 65535 | 가변길이 문자형이며 n을 사용하면 1부터 65535까지 지정 | |
| BINARY(n) | 1 ~ 255 | 고정길이의 이진 데이터 값 | |
| VARBINARY(n) | 1 ~ 255 | 가변길이의 이진 데이터 값 | |
| TEXT | TINYTEXT | 1 ~ 255 | 255 크기의 TEXT 데이터 값 |
| TEXT | TEXT | 1 ~ 65535 | N 크기의 TEXT 데이터 값 |
| TEXT | MEDIUMTEXT | 1 ~ 16777215 | 16777215 크기의 TEXT 데이터 값 |
| TEXT | LONGTEXT | 1 ~ 4294967295 | 최대 4GB 크기의 TEXT 데이터 값 |
| BLOB | TINYBLOB | 1 ~ 255 | 255 크기의 BLOB 데이터 값 |
| BLOB | BLOB | 1 ~ 65535 | N 크기의 BLOB 데이터 값 |
| BLOB | MEDIUMBLOB | 1 ~ 16777215 | 16777215 크기의 BLOB 데이터 값 |
| BLOB | LONGBLOB | 1 ~ 4294967295 | 최대 4GB 크기의 BLOB 데이터 값 |
| ENUM(값들...) | 1 또는 2 | 최대 65535개의 열거형 데이터 값 | |
| SET(값들...) | 1, 2, 3, 4, 8 | 최대 64개의 서로 다른 데이터 값 |
날짜와 시간 데이터 형식
| 데이터 형식 | 바이트 수 | 설명 | |
|---|---|---|---|
| DATE | 3 | 날짜는 1001-01-01 ~ 9999-12-31 까지 저장되며 날짜 형식만 사용 | |
| 'YYYY-MM-DD' 형식으로 사용됨 | |||
| TIME | 3 | -838:59:59.000000 ~ 838:59:59.000000 까지 저장되며 | |
| 'HH:MM:SS' 형식으로 사용 | |||
| DATETIME | 8 | 날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59 까지 저장되며 형식은 | |
| 'YYYY-MM-DD HH:MM:SS' 형식으로 사용 | |||
| TIMESTAMP | 4 | 날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59 까지 저장되며 형식은 |
'YYYY-MM-DD HH:MM:SS' 형식으로 사용
time_zone 시스템 변수와 관련이 있고 UTC 시간대 변환하여 저장 | |
| YEAR | 1 (tiny_int?) | 1901 ~ 2155까지 저장
'YYYY' 형식으로 사용 | |
기타 데이터 형식
| 데이터 형식 | 바이트 수 | 설명 |
|---|---|---|
| GEOMETRY | N/A | 공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작 |
| JSON | 8 | JSON 문서를 저장 |
Type casting (형변환) 에 대해 알아보자.
- SQL 데이터의 형변환에는 명시적 형변환과 암시적 형변환이 있다.
명시적 형변환
CAST (expression AS 데이터형식 [(길이)])CONVERT (expression, 데이터형식 [(길이)])
숫자형 -> 숫자형
간단하게 각 column의 자료형에 대해 보자
DESC tbl_menu;
형변환을 하지 않은 상태로 소수점을 나타내보았다.
SELECT AVG(menu_price) AS '가격평균'
FROM tbl_menu;
소수점 아래가 3으로 반복되는 값으로 형변환을 해본다.
UNSIGNED INTEGER
UNSIGNED INTEGER : 소수점 이하를 자른다.
SELECT CAST(AVG(menu_price) AS UNSIGNED INTEGER) AS '가격평균'
FROM tbl_menu;
FLOAT
FLOAT : 소수점 이하 2자리까지 자른다.
SELECT CAST(AVG(menu_price) AS FLOAT) AS '가격평균'
FROM tbl_menu;
데이터 형식에 따르면
FLOAT형은 "소수 7자리까지 나와야 하는거 아닌가?" 싶을 수 있어서 찾아봤는데,FLOAT유형으로 변환하는 과정에서 표시 방식의 차이가 발생할 수 있고, 그로 인해 소수점 이하 2자리까지만 나온다고 한다.
DOUBLE
DOUBLE : 소수점 12번째 자리까지 나타낼 수 있다.
SELECT CAST(AVG(menu_price) AS DOUBLE) AS '가격평균' FROM tbl_menu;
문자 -> 날짜
오늘 날짜인 2024년 6월 27일을 문자열로 나타내고 date 형으로 변환해보자.
SELECT CAST('2024$6$27' AS DATE);
SELECT CAST('2024/6/27' AS DATE);
SELECT CAST('2024#6#27' AS DATE);특수 문자를 구분자로 활용하기에 셋 다 같은 결과가 나온다.
묵시적 형변환
숫자 -> 문자
- CONCAT : 2개의 문자열을 붙여준다.
CONCAT을 활용해 묵시적 형변환을 할 수 있다.
SELECT CONCAT(1000, '원');
-- 원래 모양
-- SELECT CONCAT(CAST(1000 AS CHAR), '원');1000 은 INT형, '원'은 문자열인데 위 쿼리를 실행하면 숫자 -> 문자열로 형변환이 된다.
문자 -> 숫자
MariaDB가 연산 시 치환하기 힘든 문자열은 0으로 치환하여 계산해주는데,
- 제대로 치환되는 경우
SELECT 1 + '2';
-- SELECT 1 + CAST('2' AS INT);
- 0으로 치환하는 경우
SELECT 1 + '김'; -- 1로 계산
-- SELECT 5 > '반가워';
-- 반가워를 0으로 치환해서 TRUE 로 인해 1이 나온다.
- 문자열 속 우선순위
-> 이 경우에는 왼쪽에 있는 것이 우선순위가 된다.
-- 문자열 속 우선순위 (문자열 속에 2가지 형이 들어있다면 왼쪽에 있는게 우선순위)
SELECT 1 + '2반가워'; -- 3으로 계산
SELECT 1 + '반가워2'; -- 1로 계산