SQL의 데이터 타입에 대해 알아보자

안녕하세요, 끙정입니다.

 

오늘은 DBMS의 데이터 타입에 대해서 알아보겠습니다.

DBMS마다 지원하는 타입이 다양하지만 최대한 표준에 맞춰서 사용하고 있습니다.

반드시 알아야 할 타입 정도 까지만 정리하도록 하겠습니다.

 

1. 수치형

수치형은 수학적인 숫자값을 저장하는 타입이며 정수형과 실수형으로 구분합니다.

정수형은 소수점 이하를 표현하지 않는 수치값입니다.

 

오라클	SQL Server	MariaDB	설명
INT	INT	INT	4바이트. ± 21억
	BIGINT	BIGINT	8바이트. ± 900경
		MEDIUMINT	3바이트. ± 830만
SMALLINT	SMALLINT	SMALLINT	2바이트. -32768~32767
	TINYINT	TINYINT	1바이트. SQL Server: 0~255 MariaDB: -128~127

 

요즘은 저장 장치가 워낙 저렴하고 짧은 타입이 속도는 오히려 느려 INT형을 쓰는 것이 무난합니다.

 

실수형은 소수점 이하까지 정밀한 값을 표현하는 수치형입니다.

DBMS마다 실수형을 칭하는 이름이 다른데,

오라클은 주로 NUMBER 타입을 쓰고,

SQL Server와 MariaDB는 DECIMAL 또는 NUMERIC 타입을 씁니다.

 

타입	SQL Server	오라클	MariaDB
DECIMAL	O	O	O
NUMBER	X	O	X
NUMERIC	O	O	O

 

자리수는 1~38까지, 정밀도는 -84~127까지 지정할 수 있으며 길이와 정밀도에 따라 최대 22바이트까지 차지합니다.

 

NUMBER(최대자리수, 소수점이하정밀도)

 

최대 자리수 이하는 지정한 정밀도 자리에서 반올림 처리합니다.

 

NUMBER(5)	12346	소수점 자리에서 반올림
NUMBER(7, 2)	12345.68	소수점 이하 두 번째 자리에서 표시
NUMBER(5, -3)	12000	천자리에서 반올림

 

오라클은 모두 수치값을 NUMBER로 표현하며 정수형은 따로 없습니다.

다른 DBMS와의 호환성과 표준 준수를 위해 INT를 NUMBER(10)의 동의어로,

SMALLINT를 NUMBER(5)의 동의어로 정의합니다.

 

DBMS의 NUMBER와 DECIMAL 타입은 다른 프로그래밍 언어에서 사용하는

float, double 부동소수점 타입과는 내부 구조가 다르며 포괄하는 범위가 더 넓습니다.

프로그래밍 언어와의 호환을 위해 부동 소수점 타입도 지원합니다.

 

설명	SQL Server	오라클	MariaDB
4바이트 실수형	FLOAT(~24) = REAL	BINARY_FLOAT	FLOAT
8바이트 실수형	FLOAT(25~)	BINARY_DOUBLE	DOUBLE = REAL

 

REAL은 SQL Server에서는 FLOAT 타입이지만 MariaDB에서는 DOUBLE 타입으로 되어 있어 헷갈립니다.

과거와의 호환성 확보를 위해 제공하는 타입일 뿐이므로 앞으로는 웬만하면 사용하지 않는 것이 좋습니다.

 

 

2. 문자형

문자형은 사람의 이름이나 제품명 등과 같은 문자열 데이터를 기억하는 타입입니다.

문장을 구성하는 글자 개수가 가변적이어서 괄호 안에 최대 길이를 지정합니다.

최대 길이보다 짧은 문자열을 처리하는 방식과 인코딩 방식에 따라 여러 가지 타입이 있습니다.

 

설명	오라클	SQL Server	MariaDB
고정 길이 문자열	CHAR	CHAR	CHAR
가변 길이 문자열	VARCHAR VARCHAR2	VARCHAR	VARCHAR
고정 길이 유니코드 문자열	NCHAR	NCHAR	NCHAR
가변 길이 유니코드 문자열	NVARCHAR2	NVARCHAR	NVARCHAR
긴 문자열. 폐기 예쩡이며 VARCHAR(MAX) 권장	CLOB	TEXT	TEXT
유니코드 긴 문자열	NCLOB	NTEXT	NTEXT

 

CHAR형과 VARCHAR형은 고정 길이와 가변 길이 문자열입니다.

인코딩 방식에 따라서도 타입이 나누어집니다.

유니코드 방식인 NCHAR, NVARCHAR는 문자 하나당 2바이트를 할당합니다.

어차피 2바이트 이상인 한글이나 일본어는 별 손해가 없지만 영문 데이터는 낭비가 심합니다.

 

유니코드 문자열은 접두로 대문자 N(National의 약자)를 붙여 N'대한민국', N'서울시'로 표기합니다.

 

국내용이라면 굳이 유니코드를 쓸 필요가 없지만 해외 버전도 만든다면 사정이 달라집니다.

여러 나라의 언어를 동시에 저장해야 한다면 반드시 유니코드를 사용해야 합니다.

 

	고정 길이	가변 길이
문자열	CHAR	VARCHAR
유니코드	NCHAR	NVARCHAR

 

3. 날짜형

날짜와 시간은 일상 생활에서 늘 사용하는 정보입니다.

그러나 수치형이나 문자열에 비해 구조가 복잡합니다.

년월일시분초라는 여러 가지 구성요소로 이루어져 있는데다

각 요소의 진법 체계가 60진법, 10진법, 12진법 등으로 다양하고 나라마다 체계가 다르기까지 합니다.

 

날짜 자체의 포맷도 복잡한데 DBMS별 타입이 제각각이고 버전별 차이까지 존재합니다.

 

설명	오라클	SQL Server	MariaDB
날짜와 시간을 0.0033초 단위로 저장		DATETIME	DATETIME
날짜와 시간을 100나노초 단위로 저장		DATETIME2
datetime2와 같되 표준시간대를 인식		DATETIMEOFFSET
4바이트, 분단위까지 저장		SMALLDATETIME
날짜만 저장		DATE	DATE
날짜와 시간을 초단위로 저장	DATE
시간만 100나노초 단위로 저장		TIME	TIME
날짜와 시간을 ms단위까지 저장	TIMESTAMP
UTC를 적용한 초단위 에폭타임			TIMESTAMP

 

통상의 경우는 년월일시분초까지만 기록하는 경우가 대부분이며 오라클은 DATE 타입을 쓰고

SQL Server와 MariaDB는 DATETIME 타입을 쓰면 무난합니다.

 

각 DBMS별로 날짜와 시간 데이터를 저장하는 기본적인 방법을 알아보겠습니다.

먼저 날짜 타입의 필드 하나만 가지는 간단한 테이블을 만들겠습니다.

 

/* Oracle */
CREATE TABLE tDate (today DATE);

/* SQL Server, MariaDB */
CREATE TABLE tDate (today DATETIME);

 

해당 필드에 간단하게 현재 시간을 입력해보겠습니다.

각 DBMS별로 현재 시간을 구하는 함수가 다릅니다.

 

/* Oracle */
INSERT INTO tDate VALUES (SYSDATE);

/* SQL Server */
INSERT INTO tDate VALUES (GETDATE());

/* MariaDB */
INSERT INTO tDate VALUES (NOW());

 

특정 날짜를 입력하는 것은 굉장히 까다롭습니다.

오라클의 날짜 상수는 NLS_DATE_FORMAT 설정값을 따르는데

이 값의 디폴트가 'RR/MM/DD'로 되어 있어 '년/월/일' 포맷으로 날짜만 표현할 수 있으며 시간은 항상 자정입니다.

시간까지 지정하려면 설정을 변경합니다.

환경설정-데이터베이스-NLS-날짜형식에 'HH24:MI:SS'를 추가합니다.

 

 

/* Oracle */
INSERT INTO tDate VALUES ('2021/12/25') /* 그냥 날짜만 집어넣을 때 */
INSERT INTO tDate VALUES (TO_DATE('2021/12/25 12:34:56', 'yyyy/mm/dd hh24:mi:ss')); /* 시간까지 집어넣을 때 */

/* SQL Server */
INSERT INTO tDate VALUES ('2021/12/25 12:34:56');

/* Maria DB */
INSERT INTO tDate VALUES ('20211225123456');

 

오라클의 환경설정을 통해 포맷을 변경해주었지만,

설정은 세션마다 달라질 수 있어 호환성이 떨어집니다.

설정과 상관없이 시간까지 입력하여면 TO_DATE 함수로 날짜 포맷을 지정하여 입력합니다.

SQL Server의 날짜 상수는 '년/월/일 시:분:초' 또는 '년-월-일 시:분:초' 형식으로 표현합니다.

MariaDB도 이 형식을 인식하며 또한 '년월일시분초' 형식도 지원합니다.

연도를 두 자리로 표기할 수도 있는데 이 경우 50 이상은 20세기로 인식하고 49이하면 21세기로 인식합니다.

호환성을 위해 가급적이면 네 자리 다 적는 것이 좋습니다.

 

4. ANSI 표준 타입

수치, 문자형, 날짜형 외에도 세상에 존재하는 모든 정보를 포괄하기 위해 DBMS별로 수많은 타입을 더 정의합니다.

대부분 대체되거나 폐기되었으나 여전히 쓰이는 실용성 있는 타입은 아래와 같습니다.

 

설명	SQL Server	오라클	MariaDB
진위형. 1이면 참, 0이면 거짓. NULL도 가능	BIT		BOOLEAN
n비트 표현		RAW	BIT(n)
XML 문서를 저장	XML	XML Type
JSON 문서를 저장			JSON
고정 길이 이진 데이터	BINARY	BLOB	BINARY
가변 길이 이진 데이터	VARBINARY	BLOB	VARBINARY
지도상의 좌표를 저장	GEOMETRY		GEOMETRY

 

DBMS별로 복잡한 타입을 ANSI 표준을 통해 표준화하고자 했습니다.

그러나 ANSI표준안이 모든 DBMS의 타입을 포괄하지 못하며 기존 구조와 다르기 때문에 적용이 쉽지는 않습니다.

 

타입	설명
Integer	4바이트 정수
Smallint	2바이트 정수
Float	4바이트 실수
Double	8바이트 실수
Number(p, s), Decimal(p, s)	전체 자리수와 유효 숫자를 지정한 실수
Character(n)	고정 길이 문자열
Character Varying(n)	가변 길이 문자열
National Character(n)	고정 길이 유니코드 문자열
National Character Varying(n)	가변 길이 유니코드 문자열
Date	날짜
Time	시간
TimeStamp	날짜와 시간

 

DBMS의 특이한 점은 참/거짓 을 표현하는 진위형이 따로 정의되어 있지 않다는 점입니다.

표준에 BOOLEAN 타입이 정의되어 있지만 1비트를 다루는 것이 비효율적이고 NULL 상태 표현이 곤란해 아무도 쓰지 않습니다.

 

SQL Server는 BIT 타입이 있지만 실제로는 쓰지 않고 MariaDB는 TINYINT(1)로 맵핑해 놓았으며

오라클이나 DB2는 아예 없습니다.

PostgreSQL이 그나마 BOOLEAN 타입을 지원하지만 값이 없을 때 NULL로만 표현하며

표준이 규정하는 UNKNOWN 예약어를 지원하지 않아 반쪽짜리입니다.

 

따라서 진위형을 표현할 때는 통상 정수형 필드에 1과 0을 넣어 참, 거짓을 표현하거나

CHAR(1) 필드에 Y/N 또는 T/F 문자를 저장하는 것이 관례입니다.

 

 

 

그만 알아보겠습니다.

 

 

 

출처

http://www.yes24.com/Product/Goods/101637633?OzSrank=1

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24