DATA TYPE
DATA TYPE(데이터자료형)이란 데이터가 가질 수 있는 값의 종류다. 데이터자료형은 테이블을 생성할 때 컬럼(속성)별로 지정할 수 있다. 지정한 데이터자료형에 따라 컬럼 별로 가질 수 있는 값의 종류가 정해진다.
CREATE TABLE 살펴보기
아래는 Item(상품) 테이블을 생성하는 SQL이다. 컬럼별로 VARCHAR(40), DATE와 같은 자료형이 지정된 것을 알 수 있다.
-- [SQL-3-2-1]
-- CREATE 예제입니다. 실행하지 마세요.
CREATE TABLE Item
(
ItemId VARCHAR(40) NOT NULL COMMENT '상품ID',
ItemNm VARCHAR(100) NULL COMMENT '상품명',
ItemCat VARCHAR(40) NULL COMMENT '상품카테고리',
ItemSizeCd VARCHAR(40) NULL COMMENT '상품사이즈코드',
HotColdCd VARCHAR(40) NULL COMMENT '아이스/HOT구분코드',
LaunchDt DATE NULL COMMENT '출시일자',
PRIMARY KEY(ItemId),
CONSTRAINT Item_FK01 FOREIGN KEY(ItemCat) REFERENCES ItemCat(ItemCat)
) COMMENT '상품';
SQL
복사
Item(상품) 테이블의 ItemId(상품ID)와 ItemNm(상품명) 컬럼은 문자 형태의 데이터 자료형으로 되어 있다. 아직 다루진 않았지만, 뒤에서 다루게 될 Ord(주문) 테이블의 OrdNo(주문번호)와 OrdAmt(주문금액) 컬럼은 숫자 데이터 자료형으로 되어 있다.
MySQL의 기본 DATA TYPE(자료형)
데이터 자료형은 일반적으로 DBMS(MySQL)에서 제공하는 것 중에 골라서 사용한다. DBMS가 제공하는 기본적인 자료형에는 문자 자료형, 숫자 자료형, 날짜 자료형이 있다.
MySQL은 문자 값을 저장하기 위해 CHAR와 VARCHAR라는 문자 자료형을 제공하며, 숫자 값 저장을 위해 INT(=Integer), BIGINT, DECIMAL을 제공한다. 그리고 날짜 값을 위해 DATE와 DATETIME, TIMESTAMP 자료형을 제공한다. 지금 설명한 내용을 그림으로 정리해보면 아래와 같다.
문자, 숫자, 날짜는 다른 DBMS도 공통적으로 제공하는 데이터 자료형이다. 다만, DBMS마다 약간씩 다른 이름으로 되어있거나 기능의 차이가 있을 수 있다. 문자, 숫자, 날짜 외에도 매우 다양한 자료형이 존재한다. MySQL에는 어떤 데이터 자료형이 있는지는 아래의 공식 레퍼런스를 참고해보기 바란다. 공식 레퍼런스의 자료형을 모두 외울 필요는 없다. 필요할 때 찾아보면 된다.
위에서 살펴본 자료형에 대해 조금 더 살펴보자.
•
CHAR는 고정 길이 문자 자료형, VARCHAR는 가변 길이 문자 자료형
◦
CHAR(10): 몇 글자가 저장되든 10자리의 공간을 사용
▪
'ICE'와 같이 세 글자만 저장해도 내부적으로 10자리의 공간을 사용
◦
VARCHAR(10): 저장된 글자 수 만큼의 공간만 사용
▪
'ICE'와 같이 세 글자만 저장하면 그에 맞는 저장 공간만 사용
◦
VARCHAR가 공간 절약 측면에서 좀 더 유리
◦
일반적으로 VARCHAR를 많이 사용
◦
코드성 데이터나 ID와 같이 저장될 값의 길이가 절대적으로 일정한 경우에만 CHAR를 고려
•
INT와 BIGINT는 정수형 숫자를 저장
◦
BIGINT는 INT보다 더 큰 범위의 정수를 저장할 수 있다.
◦
INT와 BIGINT에 UNSIGNED를 추가로 지정하면 양수만 저장이 가능
•
DECIMAL: 실수를 저장하기 위해 사용(NUMERIC도 사용 가능)
◦
괄호안에 (18,3)과 같이 전체 자릿수와 소수점 자릿수를 지정
◦
DECIMAL(18,3): 총 18자리 숫자를 저장할 수 있으며, 그 중에 세 자리는 소수점 자릿수
날짜 자료형은 나중에 별도로 설명한다.
Tip. 거거익선?
거거익선이란 말이 있다. TV를 살때 크면 클수록 좋다는 것을 재밌게 표현한 말이다. 그렇다면 데이터 자료형에는 어떨까? BIGINT는 INT보다 큰 숫자를 저장할 수 있다. 그러므로 앞으로 데이터가 쌓일걸 대비한다면 INT보다는 BIGINT가 더 좋을 수 있다. 하지만 BIGINT는 INT보다 조금 더 많은 디스크 공간을 사용한다. 결론적으로 디스크 용량의 낭비가 발생할 수 있다.
VARCHAR 자료형은 어떨까? 전화번호를 저장하기 위한 컬럼을 VARCHAR(1000)으로 설정할 수 있다. VARCHAR는 가변 자료형이기 때문에 VARCHAR(1000)으로 설정해놓고 전화번호에 해당하는 11자리만 입력해서 사용하면 공간 낭비는 없다. VARCHAR는 어차피 11자리 공간만 할당해서 사용하기 때문이다. 하지만 더 큰 문제가 있다. 1,000자리를 허용할 수 있도록 만들어 났기 때문에 프로그램 오류나, 데이터 클린징 실수로 실제 1,000 자리의 글자가 입력될 가능성이 존재하게 된다. 이러한 데이터가 발생하면 데이터 품질의 이슈가 생긴다. 데이터 품질 이슈는 데이터 활용과도 직결되는 중요한 문제다. “쓰레기를 넣으면 쓰레기가 나온다.” 데이터 분석과 관련된 유명한 이야기다.
테이블 컬럼에 자료형의 크기를 정할 때는 단순히 확장성을 고려한 거거익선이 아닌, 확장성과 실용성, 데이터 품질까지 고려한 중간 지점을 찾아야 한다. 이 작업은 매우 골치 아픈 일이다. 경험치와 많은 고민이 필요하며, 고민의 결과가 답이 아닐 가능성도 있다. 다만, 거거익선만 고려해서는 안된다는 점을 기억하고 테이블의 컬럼 길이를 선정해야 한다.
DESC 명령어
DESC 명령어를 사용하면 테이블에 어떤 컬럼들이 어떤 자료형으로 되어 있는지 확인할 수 있다.
아래와 같이 DESC 명령어를 사용해 Item(상품) 테이블의 구조를 확인해보자. 각 컬럼별로 어떤 자료형을 사용하고 있는지 알 수 있다. 대부분의 컬럼이 VARCHAR 자료형으로 되어 있으며 LaunchDt만 DATE 자료형으로 되어 있다.
-- [SQL-3-2-2]
DESC startdb.Item;
-- 결과
Field Type Null Key Default Extra
---------- ------------ ---- --- ------- -----
ItemId varchar(40) NO PRI NULL
ItemNm varchar(100) YES NULL
ItemCat varchar(40) YES MUL NULL
ItemSizeCd varchar(40) YES NULL
HotColdCd varchar(40) YES NULL
LaunchDt date YES NULL
SQL
복사
이번에는 DESC startdb.Ord를 실행해보자. Ord(주문) 테이블에는 주문 정보가 담겨져 있다. 아직 사용해본적은 없지만 앞으로 SQL을 배우면서 자주 사용하게 될 테이블이다.
-- [SQL-3-2-3]
DESC startdb.Ord;
-- 결과
Field Type Null Key Default Extra
-------------- --------------- ---- --- ------- -----
OrdNo bigint unsigned NO PRI NULL
OrdDtm datetime YES NULL
PrepareCompDtm datetime YES NULL
PickupDtm datetime YES NULL
MemberId varchar(40) YES MUL NULL
ShopId varchar(40) YES MUL NULL
OrdSt varchar(40) YES NULL
OrdAmt decimal(18,3) YES NULL
PayTp varchar(40) YES NULL
SQL
복사
OrdNo(주문번호)는부호가 없는(UNSIGNED) 큰 정수(BIGINT)로 설정되어 있다. OrdDtm(주문일시), PrepareCompDtm(준비완료일시), PickupDtm(픽업일시)는 DATETIME 자료형이다. DATETTIME 자료형은 날짜에 대한 년, 월, 일과 함께 시, 분, 초도 저장하는 자료형이다. 반면에 DATE 자료형은 년, 월, 일만 저장할 수 있다. OrdAmt(주문금액)는 DECIMAL(18,3)으로 전체 18자리에 소수점 세 자리를 저장할 수 있도록 되어 있다.
DESC로 살펴본 Item과 Ord 테이블의 ERD를 살펴보면 다음과 같다.
INFORMATION SCHEMA
DESC 명령어 외에, 테이블의 구조를 확인할 수 있는 방법이 있다. 바로 메타 테이블을 조회하는 것이다. 메타 테이블이란 데이터베이스에 만들어진 오브젝트 정보를 관리하는 별도의 시스템 테이블이다. 대부분의 DBMS가 메타 테이블을 제공한다. MySQL은 INFORMATION_SCHEMA라는 스키마 영역에 메타 테이블이 존재한다. 아래 SQL로 Item의 테이블 구조를 확인할 수 있다. 같은 방법으로 Ord 테이블 구조도 살펴보기 바란다.
-- [SQL-3-2-4]
SELECT T1.TABLE_SCHEMA DB ,T1.TABLE_NAME Tab
,T1.COLUMN_NAME Col
,T1.ORDINAL_POSITION Pos ,T1.COLUMN_TYPE Typ
,T1.IS_NULLABLE Null_YN ,T1.COLUMN_KEY Ky
,T1.COLUMN_COMMENT Cmt
FROM INFORMATION_SCHEMA.COLUMNS T1
WHERE T1.TABLE_NAME = 'Item'
AND T1.TABLE_SCHEMA = 'startdb'
ORDER BY pos ASC;
-- 결과
DB Tab Col Pos Typ Null_YN Ky Cmt
------- ---- ---------- --- ------------ ------- --- ------------------
startdb item ItemId 1 varchar(40) NO PRI 상품ID
startdb item ItemNm 2 varchar(100) YES 상품명
startdb item ItemCat 3 varchar(40) YES MUL 상품카테고리
startdb item ItemSizeCd 4 varchar(40) YES 상품사이즈코드
startdb item HotColdCd 5 varchar(40) YES 아이스/HOT구분코드
startdb item LaunchDt 6 date YES 출시일자
SQL
복사
이처럼 메타 테이블을 사용하면 자신이 원하는 형태로 테이블 구조를 조회할 수 있다. 또한, 메타 테이블에는 DESC에서는 보여주지 않는 추가적인 정보들이 더 있다. 이 중에서 COMMENT 정보는 데이터 이해에 많은 도움을 준다. COMMENT는 컬럼의 설명이나 의미를 알기 쉽게 정리한 내용으로서 테이블을 생성하면서 컬럼별로 지정할 수 있다.
