04-1. MySQL의 데이터 형식
- 테이블에 열을 여러개 만들었다
- 각 열에는 데이터 형식이 지정되어있다
- 숫자, 문자, 날짜 등..
- 각 형식 안에서도 세분화됨
👉🏻 정수형
- 소수점이 없는 숫자
🧩 종류
✅ INT
- Integer
- 가장 무난 /기본적
- 내부적으로 4byte 차지
- 데이터를 정말 많은 건수를 저장함
- 예를 들어 나이를 저장한다면 0~100정도가 될것이다. 그런데 INT는 -21억 ~ 21억 까지 아주 큰 범위를 저장할 수 있다. 하지만 나이를 이렇게 많이 표현할 필요는 없다
✅ SMALLINT
- 바이트수가 INT의 절반
✅ TINYINT
- SMALLINT보다 더 작은 것
- 나이 정도는 TINYINT로 가능
🎮 테이블을 만들어 확인해보기
- 데이터 입력
- 잘 들어감
🎮 범위를 넘는 수 넣어보기
* TINYINT 범위를 넘는 수
*SMALLINT 범위를 넘는 수
*INT 범위를 넘는 수
*BIGINT 범위를 넘는 수
🧩 우리가 만들었던 테이블
✅ 인원
- 가수 그룹이다보니 많아도 100명이 넘지 않을텐데 INT로 되어 있음
- INT는 최대 21억이라 너무 많아
- 인원을 TINYINT로 하는 게 좀 더 효율적
✅ 평균 키
- INT로 하자니 키가 21억cm인 사람은 없음
- TINYINT로 하자니 127cm까지 밖에 입력 못함
- SMALLINT로 모든 키를 포함하면서도 너무 범위가 크지 않게
🧩 UNSIGNED
- 위의 예시처럼 양의 정수로 땡겨서 사용 가능하다
- 범위(용량)은 같으나 시작점이 다르다
✅ 평균키
- SMALLINT 보다도 TINYINT UNSIGNED를 사용하는 것이 훨씬 효율적이다
- 2byte로 표현할 뻔 한 것을 1byte로도 충분히 표현
👉🏻 문자형
🧩 CHAR
- 고정형 문자형
- 속도가 더 빠르다
- 글자 크기가 고정된 것
- 255자까지 가능
🧩 VARCHAR
- Variable Character
- 가변형 문자형
- 공간을 효율적으로 사용할 수 있다
- 글자 크기가 가변적인 것
- 16383자까지 가능
✅ 차이점
- 사용자 입장에서는 별 차이가 없다
- 둘 다 글자를 저장할 뿐
- 둘 다 최대 10글자까지 저장이 가능하다
- CHAR: 3글자만 넣어도 내부적으로는 10자리를 잡는다
- 7자리 낭비
- VARCHAR: 3글자만 넣으면 내부적으로 3글자에 대한 공간만 잡는다
- 더 효율적
✅ addr
- 딱 두 글자로 고정되어 있음
- CHAR로 잡는 게 좋다
- VARCHAR로 잡아도 되지만 속도가 약간 떨어진다 (체감은 안되지만)
✅ mem_name
- 매우 가변적이다 (잇지, 방탄소년단, ...)
- VARCHAR로 잡는 게 좋다
- CHAR로 잡을 경우 많은 공간이 낭비된다
✅ phone1
- CHAR로 잡음
- 앞에 0이 들어가기 때문에 (0도 의미가 있는 것)
- INT, SMALLINT 등으로 잡으면 앞의 0이 떨어져 나간다
✅ phone2
- 하이픈을 제외했기 때문에 숫자로 잡아도 되긴함 (맞고 틀리고는 아님)
- CHAR로 잡은 이유는 전화번호가 '숫자'로서의 의미/가치가 없기 때문
- 숫자는 연산이 가능하거나 크다/작다가 의미가 있어야
- 숫자로서 의미가 없다면 문자로 하는 게 일반적
🎮 VARCHAR를 16384로 잡아보기
- 16383까지만 가능하다고 나옴
🎮 16383으로 고쳐보면
- 에러남
- 한꺼번에 해서 그렇다?
"Row size too large" 오류는 각 행의 최대 크기가 데이터베이스 시스템에서 허용하는 최대 크기를 초과했을 때 발생합니다. 이 경우에는 데이터 타입 및 데이터 길이가 주요한 역할을 합니다.
데이터베이스 시스템마다 행의 최대 크기에 대한 제한이 있습니다. 예를 들어, InnoDB 스토리지 엔진을 사용하는 MySQL에서는 하나의 행의 최대 크기가 65,535 바이트로 제한되어 있습니다. CHAR 및 VARCHAR 열은 이러한 행 크기에 영향을 미칩니다.
문제의 쿼리에서는 CHAR(255)와 VARCHAR(16383) 두 열을 사용하고 있는데, CHAR는 고정 길이 문자열이므로 항상 255바이트를 차지하게 됩니다. VARCHAR는 가변 길이 문자열이며, 주어진 데이터에 따라 실제로 차지하는 공간이 달라집니다. 이런 조합으로 인해 행 크기가 너무 커져서 발생한 것으로 추측됩니다.
데이터베이스 시스템은 효율적인 메모리 사용 및 I/O 작업을 위해 각 행을 처리하는 데 일정한 크기의 메모리 블록을 사용합니다. 따라서 행이 크면 메모리 및 디스크 사용에 문제가 발생할 수 있습니다.
일반적으로 CHAR와 VARCHAR를 동시에 사용하는 것은 권장되지 않습니다. 필요한 경우, 특별한 이유 없이 CHAR보다 VARCHAR를 사용하는 것이 효율적입니다. 그리고 가능하면 필요한 최대 길이만큼 VARCHAR 길이를 지정하는 것이 좋습니다.
🎮 테이블 지우기
🎮 CHAR만 만들어보기
- 만약 256으로 하면 에러남
🎮 VARCHAR만 만들어보기
🧩 넷플릭스 DB
- id, 제목, 감독, 주연배우 등은 CHAR나 VARCHAR로 가능한데
- 문제는 자막.. 16000여자로는 불가능함
- 동영상의 경우 binary data라고 해서 이진데이터가 저장된다
- 그런걸 BLOB이라고 한다
BLOB(이진 대형 객체, Binary Large Object)은 데이터베이스에서 이진 데이터를 저장하는 데 사용되는 특별한 데이터 형식입니다. 이는 텍스트 데이터가 아닌 이진 데이터를 저장할 수 있도록 설계되었으며, 대표적으로 이미지, 동영상, 오디오 파일과 같은 다양한 멀티미디어 데이터를 포함합니다.
BLOB은 일반적으로 다음과 같은 두 가지 형태로 구분됩니다:
1. TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB:
- BLOB 형식은 크기에 따라 여러 가지로 나뉩니다. 각각은 다음과 같은 크기 범위를 가지고 있습니다:
- TINYBLOB: 최대 255 바이트
- BLOB: 최대 65,535 바이트
- MEDIUMBLOB: 최대 16,777,215 바이트
- LONGBLOB: 최대 4,294,967,295 바이트
2. BINARY 및 VARBINARY:
- BLOB과 유사하지만, 일반적으로 BLOB은 미디어 데이터와 같은 큰 이진 객체를 저장하는 데 사용되고, BINARY 및 VARBINARY는 상대적으로 작은 이진 데이터를 저장하는 데 사용됩니다.
- BINARY: 고정 길이의 이진 데이터를 저장하는 데 사용됩니다.
- VARBINARY: 가변 길이의 이진 데이터를 저장하는 데 사용됩니다.
BLOB 데이터는 주로 다음과 같은 용도로 사용됩니다:
- 이미지 및 동영상 저장: 프로필 사진, 게시물 이미지, 동영상 파일 등의 미디어 데이터를 저장합니다.
- 문서 및 파일 저장: 문서 파일, PDF 파일, 압축 파일 등을 이진 형태로 저장합니다.
- 바이너리 데이터 저장: 센서 데이터, 음성 녹음 등과 같은 이진 형태의 데이터를 저장합니다.
BLOB 데이터를 쿼리할 때는 일반적으로 해당 데이터를 읽어오는데 사용하는 특수한 함수나 API가 필요합니다. 이는 해당 데이터를 텍스트로 변환하거나, 미디어 파일로 다운로드하거나 스트리밍하는 데 사용됩니다.
🧩 LONGTEXT
- 굉장히 긴 글자
🧩 LONGBLOB
- 굉장이 큰 파일
🎮 NETFLIX DB 만들기
- DB 만들고
- 활용하기
🎮 영화 테이블 만들기
- 자막은 LONGTEXT
- 영화 파일은 LONGBLOB
- 이렇게 하면 대용량 데이터도 MySQL 상에서 입력/수정/삭제가 가능해짐
👉🏻 실수형
- 소수점이있는 숫자
- 일반적으로는 FLOAT을 사용함 (충분)
👉🏻 날짜형
- 날짜 뿐만 아니라 시간까지도 가능하다
- DATETIME이 무난하긴 하지만
- 굳이 시간이 필요 없다면 굳이 DATETIME을 선택할 필요는 없다. DATETIME을 선택하는 것이 적절하다 (바이트수 고려)
✅ debut_date
- DATE
- 몇시에 데뷔했냐는 고려하지 않으므로
🧩 TIME을 쓰면 좋은 경우
- 오늘의 '출퇴근 시간'
- PC방 '이용 시간'
🧩 DATETIME을 쓰면 좋은 경우
- 구매 내역
👉🏻 변수의 사용
- 무엇을 담을 수 있는 임시 저장소
- 임시 저장 (영구X)
- 만약 사용자가 달라지는 등의 상황에서는 내가 만든 변수는 존재하지 않는다
🧩 기본 문법
MySQL에서는 세션 변수와 사용자 변수를 만들 수 있습니다. 세션 변수는 세션 동안에만 유지되는 변수이며, 사용자 변수는 세션 종료 전까지 유지되는 변수입니다. 이 변수들은 특정 테이블과 직접적인 관련이 없습니다. 변수는 주로 쿼리 내에서 임시로 값을 보관하거나 전역 설정 값을 저장하는 데 사용됩니다.
🎮 변수 만들어보기
🎮 확인해보기
🎮 workbench 닫았다 열어보기 (임시저장)
- 변수 생성 없이 조회만 하려고 하면
- 아무것도 없다고 나온다
- 워크벤치를 닫았다 열면 유지가 되지 않는다
- 변수는 현재 상황에서만 사용되는 임시적인 것
🎮 평균 키가 166이 넘는 가수 그룹명
- 변수명 대신 해당 값을 직접 입력해준 것과 같은 효과
🎮 LIMIT에 변수 활용하기
- LIMIT으로 보고 싶은 데이터 개수 제한할 때 변수를 활용하려고 했는데
- 저렇게 작성하면 문법적으로 지원이 되지 않음
🧩 PREPARE, EXECUTE
- PREPARE에는 SQL문을 준비한다
- 변수가 들어갈 부분을 물음표(?)로 표시한다
- EXECUTE에서 변수를 활용하여 위에 준비한 SQL문을 실행한다
- 물음표로 표시한 곳에 대입할 변수를 USING을 통해 선언한다
🎮 PREPARE, EXECUTE를 이용하여 LIMIT에 변수 활용하기
- Prepare(준비한다), mySQL은 내가 지정한 이름
- 이름 (여기서는 mySQL) 안에다가 SELECT문을 준비하라
- 맨 뒤는 꼭 ?, 아직 모른다
- mySQL을 실행해라
- 변수의 값을 ? 에 대입하라
👉🏻 데이터 형 변환
- 데이터 형식을 바꿈
🧩 명시적 변환
- 직접적으로 명시해주는 것
- CAST()와 CONVERT() 가 있다
- 형식이 다르고 결과는 같다
✅ CAST()
- 기본 형식: CAST ( 값 AS 데이터 형식 [ (길이) ] )
✅ CONVERT()
- 기본 형식: CONVERT( 값, 데이터 형식 [ (길이) ] )
🎮 평균 가격
- AS는 생략 가능
- '평균 가격'은 별칭이라 생략 가능
🎮 소수점 아래는 나오지 않게
*CAST ver.
* CONVERT ver.
✅ SIGNED
- Signed INT
- 부호가 있는 정수형
🎮 날짜형으로 바꿔보기
- 모든 결과가 똑같이 나옴
🎮 CONCAT과 CAST를 이용해 가격 X 수량을 예쁘게 표현하기
- '가격X수량'컬럼에서 사람이 보기 좋게 수식으로 나타내야하는데 이건 숫자가 아니라 문자형이다
✅ CONCAT()
- 문자열을 이어줌
- 위의 경우 CHAR로 변경된 price와 X문자열과 CHAR로 변경된 amount, 마지막으로 =문자열을 이어준다
🧩 암시적 변환
- 명시해주지 않아도 자연스럽게 바뀌는 것
🎮 문자형 + 문자형
- 내부적으로 숫자로 바꿔 100+200으로 연산됨
🎮 CONCAT(문자형, 문자형)
- 그냥 문자를 이을 때
🎮 CONCAT(숫자, 문자)
- 숫자가 문자로 바뀌어 문자끼리 이어줌
🎮 숫자와 문자 비교
(설명이 끊겼다,,;)
- 문자열의 첫 번째 문자를 확인해 숫자라면 숫자로 문자라면 0으로 변환한다
- 변환 후 비교하여 값이 True라면 1, False라면 0으로 결과가 나온다
1. `SELECT 1 > '2mega';`:
- '2mega'는 문자열이지만, 비교할 때 문자열의 시작 부분이 숫자로 해석됩니다.
- 첫 번째 문자 '2'가 정수로 변환되어 1과 비교됩니다.
- 따라서 이 비교는 2 > 1로 해석되어 True가 됩니다.
2. `SELECT 3 > '2MEGA';`:
- '2MEGA'도 문자열이지만, 비교 시 첫 번째 문자 '2'가 정수로 변환되어 3과 비교됩니다.
- 이 비교는 2 < 3으로 해석되어 True가 됩니다.
3. `SELECT 0 = 'mega2';`:
- 'mega2'는 문자열이지만, 비교 시 첫 번째 문자 'm'이 숫자로 변환되지 않으므로 문자열은 0으로 변환됩니다.
- 이 비교는 0 = 0으로 해석되어 True가 됩니다.
결과가 True이면 1이 되고, False이면 0이 됩니다. 숫자와 문자열 간의 비교 시 SQL 엔진은 자동으로 변환을 시도하며, 변환이 불가능한 경우에는 오류가 발생할 수 있습니다. 위의 예제에서는 첫 번째 문자를 정수로 변환하여 비교하게 되며, 이에 따라 결과가 결정됩니다.
출처: https://youtu.be/1YmWy-7-OhQ?si=nJamsaeKgLH-Jutg
'MySQL > 혼공SQL' 카테고리의 다른 글
| [SQL 기초 강의] 11강. SQL 프로그래밍(IF문, CASE문, WHILE문, 동적SQL) (0) | 2023.11.14 |
|---|---|
| [SQL 기초 강의] 10강. 두 테이블을 묶는 JOIN(INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN) (1) | 2023.11.13 |
| [SQL 기초 강의] 8강. 데이터 변경을 위한 SQL 문(INSERT, UPDATE, DELETE) (1) | 2023.11.11 |
| [SQL 기초 강의] 7강. SQL SELECT 절의 형식(ORDER BY 절과 GROUP BY 절) (0) | 2023.11.08 |
| [SQL 기초 강의] 6강. SQL 기본 문법(SELECT ~ FROM ~ WHERE) (0) | 2023.11.07 |