[MySQL] 자료형 총정리 (숫자, 문자, 날짜, JSON)

 

오늘은 MySQL 의 기본 자료형에 대해 알아보겠습니다. 

 

숫자 자료형

정수형

• 정수형 자료형은 기본적으로 SIGNED(기본, 양수/음수)과 UNSIGNED(양수) 옵션을 가짐 
• 정수형 자료형은 AUTO_INCREMENT 제약조건을 이용해 ID 를 생성하는 데 유용함

자료형	범위	저장크기	특징
TINYINT	Signed: -128 ~ 127 Unsigned:  0 ~ 255	1 byte	매우 작은 정수값 저장, 메모리 사용 최소화
SMALLINT	Signed: -32,768 ~ 32,767 Unsigned: 0 ~ 65,535	2 bytes	작은 정수값
MEDIUMINT	Signed: - 800만 ~ 800만 Unsigned: 0 ~ 1600만	3 bytes	중간 크기 정수값
INT	Signed: 약 - 20억 ~ 20억*  Unsigned: 0 ~ 40억	4 bytes	일반적으로 사용되는 정수 자료형
BIGINT	Signed: 약 - 900경 ~ 900경* Unsinged: 0 ~ 1800 경	8 bytes	매우 큰 데이터셋 혹은 식별자 다룰 때 사용

 

 

실수형

• 부동 소수점 이슈 존재 (순환소수 저장공간 한계로 오차 발생, floating point) → MySQL 에서는 자동으로 처리
• 부동소수점: 저장 효율적이지만 어느정도 정확도를 제한하겠다 할 때 사용
• 고정소수점: 저장 비효율적이지만 정확도를 추구하겠다 할 때 사용 (ex. 금융데이터, 계산)

자료형	범위	저장크기	특징
FLOAT	약 -3.4E+38 ~ 3.4E+38	4 bytes	일반적인 자료형, 소수점 7자리까 정확
DOUBLE	약 -1.7E+308 ~ 1.7E+308	8 bytes	소수점 15자리까지 정확
DECIMAL	사용자 정의	-	고정 소수점 숫자 ex DECIMAL(M,D) : M(소수 포함한 총 자릿수), D(소수 자릿수)

 

---

문자 자료형 

문자형

자료형	최대 길이	저장크기	특징
CHAR	255 Char	1 ~ 255 bytes	지정된 길이보다 짧은 문자열은 공백으로 채움 고정된 길이로 속도가 빠름 자주 수정되지 않는 자료형에 유용  ex. 국가코드, 우편번호
VARCHAR	65,535 Char	문자열 길이 + 1~2 bytes	가변길이 데이터 저장할 때 사용 자주 수정되는 자료형에 유효, CHAR보다 저장공간 절약 ex. 이름, 이메일 주소
TEXT	65, 535 Char	문자열길이 + bytes	긴 문장 저장 시 사용 ex. 기사, 설명
BLOB(Binary Large Object)	-	-	이징 데티어 저장용 ex 이미지, 동영상

 

 

정규표현식

• 문자열에서 특정 패턴을 찾기 위한 도구로, 자연어를 가장 간단하게 처리해주는 도구
• REGEXP_LIKE(): 주어진 문자열이 정규표현식과 일치하는지 여부를 반환
• REGEXP: 문자열이 정규표현식과 일치하는지 확인

메타문자	표현
.	임의의 한 문자
^	문자열 시작
$	문자열 끝
*	0개 이상의 반복
+	1개 이상의 반복
?	0 또는 1개의 반복
|	OR
[]	문자 클래스 중 하나

 

사용예시) 이메일 검증

• ^ : 문자열의 시작
• [A-Za-z0-9._%+-]+ : 하나 이상의 영문 대소문자, 숫자, 점, 밑줄(), 퍼센트(%), 더하기(+), 빼기(-) 가 가능
• \\. : 반드시 점(.)이 나옴
• [A-Za-z]{2,} : 두 개 이상의 영문 대소문자가 나올 수 있음.
• $ : 문자의 끝
•  

SELECT email
FROM users
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

 

 

자료 변환형

함수	기능
CAST(A as 자료형)	데이터를 지정된 자료로 변환 CAST(123 AS CHAR)
CONVERT(A, 자료형)	데이터를 다른 자료형으로 변환하거나 인코딩 변형 CONVERT(123, CHAR)

 

---

날짜 자료형

현재 서버 시간 조회

함수	기능	출력
CURDATE()	현재 서버 날짜	2024-08-06
CURTIME()	현재 서버 시간	19:14:11
NOW()	현재 서버 날짜 및 시간	2024-08-06 19:14:11

 

MySQL 날짜 자료형

함수	출력형식	사용예시
DATETIME()	YYYY-MM-DD HH:MM	기본 날짜와 시간
TIME()	HH:MM:SS	하루 근무시간
DATE()	YYYY-MM-DD	생일, 이벤트 날짜
YEAR()	YYYY or YY	4자리 or 2자리 연도
TIMESTAMP()	YYYY-MM_DD HH:MM	로그기록, 세계협정시(UTC기반)

 

날짜 추출 자료형

함수	기능	출력 (NOW() 입력)
YEAR()	연도 추출	2024
MONTH()	월 추출	8
DAY()	날짜 추출	6
DAYNAME()	요일의 이름 반환	Tuesday
WEEK()	주 수 반환 (1~53주)	31
DAYOFWEEK()	요일 번호 반환 (1일요일~7토요일)	3

 

날짜형으로 변환

함수	기능	출력
DATE_FORMAT(입력,형식)	날짜 형식 지정	DATE_FORMAT(NOW(), '%Y-%m-%d, %H:%i:$s') 2024-08-06, 19:27:07
STR_TO_DATE(입력,형식)	문자형을 날짜형으로 변환	STR_TO_DATE('2023-07-10', '%Y-%m-%d') 2023-07-10

 

날짜 계산

함수	기능	출력
DATEDIFF(A,B)	두 날짜 차이 (시간정보 무시)	DATEDIFF('2024-07-10', '2024-07-01')  9
TIMESTAMPDIFF(A,B)	두 시간대 차이 (지정된 단위 존재) SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR	TIMESTAMPDIFF(DAY, '2024-07-01 12:00:00', '2024-07-10 12:00:00') 9
CONVERT_TZ(입력,형식)	UTC 기반 시간대 변경	CONVERT_TZ(NOW(), '+00:00', '+09:00') 2024-08-07 04:29:16
DATE_ADD(A,더할수)	날짜 더하기	DATE_ADD('2024-07-01', INTERVAL 10 DAY) 2024-07-11
DATE_SUB(A,뺄수)	날짜 빼기	DATE_SUB('2024-07-01', INTERVAL 1 MONTH) 2024-06-01

 

 

적용예시) EDA의 창시자 존 튜키(John Turley) 아저씨는 1961년 5월 6일에 태어났습니다. 지금 몇살일까요?

#연도계산 & 일별 만나이 계산
-- 1.연도를 뺌
-- 2.생월일을 비교하여 넘지 않았으면 -1살
SELECT TIMESTAMPDIFF(YEAR, '1961-05-06', CURDATE()) -
       (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT('1961-05-06', '%m%d')) AS age;

---

JSON 자료형

• 키-쌍 구조를 이루는 자료형. Python의 Dictionary와 유사
• 빠른 조회보다 데이터 저장에 중심을 둠
• 어플리케이션을 통한 데이터 처리. DB에는 단순 저장

-- json 테이블
CREATE TABLE jsontable (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(45) NULL,
    jsoncol JSON NULL,
    PRIMARY KEY (id)
);

-- json 값 넣기
INSERT INTO jsontable(name, jsoncol) VALUES ("json_string", '{"a": "A", "b":"B"}');

-- json 조회
select id, name, jsoncol FROM jsontable;

-- json 특정 키 조회
select id, name, json_extract(jsoncol, '$.a') FROM jsontable;

 

 

오랑우탄이 영어를 하고 오랑이가 쿼리마스터가 되는 그날까지~