char(20) /* fixed-length */
varchar(20) /* variable-length */
char 열의 최대 길이는 현재 255바이트인 반면, varchar 열은 최대 65,535바이트까지 사용할 수 있다.
영어와 같이 라틴 알파벳을 사용하는 언어는 각 문자를 저장하는 데 단 1바이트만 필요한 반면, 한국어나 일본어를 비롯한 다른 언어들은 많은 수의 문자를 포함하므로 각 문자마다 여러 바이트의 저장 공간이 필요하다. 이러한 문자 집합을 멀티바이트캐릭터셋이라고 한다.
열을 정의할 때 기본값(mysql 서버 기준 utf8mb4
)이 아닌 캐릭터셋을 선택하려면 다음과 같이 자료형 정의 뒤에 지원되는 캐릭터셋 중 하나를 지정해주면 된다.
varchar(20) character set latin1
또한 MySQL을 사용하면 전체 데이터베이스(DB)에 대한 기본 캐릭터셋 설정도 가능하다.
create database european_sales character set latin1;
varchar
열에 64KB 제한을 초과하는 데이터를 저장하려면 텍스트 자료형 중 하나를 사용해야 한다.
자료형 | 최대 바이트 크기 |
---|---|
tinytext | 255 |
text | 65,535 |
mediumtext | 16,777,215 |
longtext | 4,294,967,295 |
MySQL 텍스트 자료형
텍스트 자료형을 선택할 때는 다음의 사항을 고려해야 한다.
varchar
열에 쵀대 65,535바이트를 허용하므로 tinytext
나 text
자료형을 사용할 필요가 없다.몇가지 예시를 통해 숫자 자료형이 사용되는 데이터 유형을 살펴보자.
고객 주문의 배송 여부를 나타내는 열
불리언(boolean)이라는 이 자료형의 열에는 false
를 나타내는 0
과 true
를 나타내는 1
이 포함된다.
트랜잭션 테이블의 시스템 생성 기본 키
이 데이터는 보통 1에서 시작하여 잠재적으로 매우 큰 수까지 1씩 증가한다.
고객의 온라인 장바구니 품목 번호
이러한 유형의 열에 대한 값은 1과 200 사이의 양의 정수이다.
회로 기판 드릴 기계의 위치 데이터
고정밀 과학 또는 제조 데이터는 종종 소수점 8자리까지 정확도를 요구한다.
자료형 | 부호 있는 정수 저장값의 범위 | 부호 없는 정수 저장값의 범위 |
---|---|---|
tinyint | -128부터 127 | 0부터 255 |
smallint | -32,768부터 32,767 | 0부터 65,535 |
mediumint | -8,388,608부터 8,388,607 | 0부터 16,777,215 |
int | -2,147,483,848부터 2,147,483,847 | 0부터 4,294,967,295 |
bigint | 부터 | 0부터 |
MySQL 정수 자료형
불필요하게 스토리지 공간을 낭비하지 않으면서 열에 저장할 수 있는 가장 큰 수를 저장할 수 있을 만큼 충분히 큰 자료형을 선택해야 한다.
이 외에도 부동소수점 자료형이 있다.
부동소수점 자료형은 위 두가지를 지정할 수 있지만 필수는 아니다.
timestamp
자료형time
자료형date
자료형years_sales
팩트 테이블 행의 연도year
자료형자료형 | 기본 형식 |
---|---|
date | YYYY-MM-DD |
datetime | YYYY-MM-DD HH:MI:SS |
timestamp | YYYY-MM-DD HH:MI:SS |
year | YYYY |
time | HHH:MI:SS |
MySQL 시간 자료형
datetime
, timestamp
, time
자료형에서는 소수점 이하 6자리까지 사용할 수 있다. 또한 위 표에서 HHH
는 -838부터 838까지의 시간(경과)을 의미한다.
설계(design)
브레인 스토밍을 통해 어떤 종류의 정보를 포함해야 도움이 될지 알아본 뒤, 열 이름과 자료형을 지정한다. 추가적으로 허용값까지 제한할 수 있다.
정제(refinement)
DB 설계에 중복(외부 키 제외) 또는 복합 열이 없음을 확인하는 절차인 정규화를 거쳐 정규화된 테이블을 만든다.
SQL 스키마 문 생성
테이블을 정의할 때는 기본 키로 사용할 열을 데이터베이스 서버에 알려줘야 하므로 테이블에 제약조건을 만들어 이 작업을 수행한다.
-- 체크 제약 조건
eye_color CHAR(2) CHECK (eye_color IN ('BR','BL','GR')),
-- 기본 키 제약 조건
CONSTRAINT pk_person PRIMARY KEY (person_id);
desc table;
명령어를 이용해 우리가 만든 테이블 정의를 볼 수 있다. 또한 NULL 값은 해당 사항 없음, 알 수 없음, 비어있는 셋 등 값을 제공할 수 없는 다양한 경우에 사용된다.
-- 테이블 생성 예시
CREATE TABLE favorite_food
(person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id)
);
-- 자동 증가 기능 ON
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
person 테이블에 데이터를 삽입할 때 person_id
열에 null
값을 제공하기만 하면 MySQL은 사용 가능한 다음 숫자로 열을 채운다.
데이터를 삽입할 때는 INSERT
를 이용해주면 되는데, 해당 구성요소는 다음과 같다.
INSERT INTO person
(person_id, fname, lname, eye_color, birth_date)
VALUES (null, 'William', 'Turner', 'BR', '1972-05-27');
UPDATE
를 이용하여 person_id
가 1
인 사람의 정보를 수정한다.
UPDATE person
SET street = '1225 Tremont St.',
city = 'Boston',
state = 'MA',
country = 'USA',
postal_code = '02138'
WHERE person_id = 1;
DELETE
를 이용하여 person_id
가 2
인 사람의 정보를 삭제한다.
DELETE FROM person
WHERE person_id = 2;
고유하지 않은 기본 키
테이블 정의에는 기본 키 제약조건 생성이 포함되므로 중복 키 값을 테이블에 삽입해서는 안 된다.
존재하지 않는 외래 키
상위 테이블에 먼저 관련 행을 작성해야만, 하위 테이블에 데이터를 입력할 수 있다.
열 값 위반
열에 제약조건이 있을 때, 이를 위반하면 에러 메세지가 나타난다.
잘못된 날짜 변환
date
열을 채울 문자열을 구성할 때, 해당 문자열이 예상 형식과 일치하지 않으면 또 다른 오류가 발생한다. 따라서 기본 형식에 의존하지 않고 형식 문자열을 명시적으로 지정하는 것이 좋다.
-- str_to_date 함수 이용
UPDATE person
SET birth_date = str_to_date('DEC-21-1980', '%b-%d-%Y')
WHERE person_id = 1;
Alan Beaulieu, 『Learning SQL』, 한빛미디어(2021)