#4 프로그래밍 개발자 교육 - SQL : 데이터 유형

  이제 데이터의 유형에 대한 설명을 드리겠습니다. 데이터는 특수한 것을 제외하고는 문자, 숫자, 날짜 이 3가지 입니다. 특수한 것으로 참/거짓을 관리하는 boolean, 이미지 등이 있는데요, 대부분 문자, 숫자, 날짜만 잘 다루면 일하는 데 큰 불편함이 없을 것입니다. 데이터 변환 함수는 결구 문자와 숫자간의 데이터 형태 변환 혹은 문자와 날짜의 변환을 하는 부분, 문자간 연결, 문자열 자르기 등이 있습니다.

 

  MySQL을 실행합니다. Tray영역에서 MySQL80 서비스를 실행 합니다. 

 

  그리고 MySQL Workbench를 실행 합니다. 아래 tray 메뉴에서 SQL Editor... 를 클릭해서도 실행 됩니다.

 

  우선 기존 테이블에서 데이터 유형을 확인해 보겠습니다. 이미 우리는 데이터 유형을 많이 봐왔습니다. world DB를 사용하겠습니다. city 테이블 컬럼을 확인 합니다. 데이터 유형은 컬럼단위로 정해 집니다. 

  컬럼 정보보기 명령어는 desc 테이블명 입니다. Desc는 describe 의 약어입니다.

  Type 열이 데이터 유형을 의미합니다.  ID와 Population 컬럼은 int 이고, 나머지 컬럼은 char(숫자) 형태입니다. int란 정수형이라는 의미입니다. 정수는 0을 포함한 음수, 양수가 모두 포함되지만 소수점이하가 없는 숫자형 입니다. Name 컬럼은 char(35) 로 되어 있는데 char 이 문자열을 의미하고 괄호 안에 숫자는 문자열 최대 허용길이를 의미합니다. 그러니까 최대 35 바이트까지 관리될 수 있습니다. 영어 알파벳이나 숫자 마침표, 쉼표등의 기호는 1 바이트이 므로 35개 문자열이 저장됩니다. 그러나 한글은 멀티바이트입니다. 그래서 35자리만큼 못쓰는데 저는 보통 계산할 때 4바이트로 환산해서 길이를 산정합니다. 

 

  데이터 타입을 좀더 자세하게 기술해 볼까 하다가 어차피 다른 웹사이트 내용 내용을 베낄꺼면 쓰지 않는 편이 좋겠다 싶어서 제 기준으로 정말 자주 쓰는 것만 요약하겠습니다. 인테넷에서 mysql 데이터 타입 혹은 mysql data type 라고 검색하시면 훨씬 더 전문적인 내용이 나오니 참조하시길 바랍니다. 

 

  문자 데이터타입에는 char 이란 것 외에 varchar 라는 것이 있습니다. 문자열은 varchar와 char 만으로도 99% 이상 해결됩니다. 실제 작업하다 막히면 저도 인터넷에서 자료형을 검색해서 문제를 해결해 갑니다. 머리속에 DB별로 자리수의 최대 허용범위를 다 외우질 못하거든요. 예를 들어 문자열이 4000자 이내까지는 varchar로 해결하고 그 이상은 text, long text 가 있다는 것을 검색해서 썼을 겁니다. Char와 varchar 에는 차이가 있습니다. Char(35) 로 선언되면 그 컬럼에 데이터가 있던 없던간에 35 바이트를 기록하는 데 사용합니다. 반면에 Varchar(35)로 되어 있으면 데이터가 없으면 사용하지 않습니다. 물론 저도 자세한 메커니즘을 모르기 때문에 단 1바이트도 안쓰는지는 모릅니다만 이해하기 쉽게 기록할 만큼만 사용한다고 알고 있습니다. 4000 자리 처럼 넉넉하게 만들 거면 char를 써서 데이터 공간을 낭비하지말고 입력한 만큼만 디스크 공간을 사용하도록 varchar를 사용하는 게 좋겠죠? 극단적으로 제가 일하는 곳에서는 문자는 varchar만 사용합니다. Oracle 에서는 문자열을 대부분 varchar2 를 사용합니다. 

 

  참고로 Oracle에서 varchar와 varchar2 의 차이점을 기술하고 있는데, 인터넷에 있는 글을 발췌했기 때문에 정확하게 맞다 아니다라고 말씀을 못드리겠습니다만 내용을 보면 NULL과 empty 를 동일취급히고, varchar말고 varchar2를 써라고 하는 군요. 실제 Oracle DB로 구축된 사이트에서 일반 문자열 컬럼은 varchar2 만 사용했습니다.

※ 출처 www.thecrazyprogrammer.com

 

   숫자 타입은 숫자의 크기와 부호, 소수점 등에 따라 종류가 많습니다. 저는 여기에 일일이 기술하고 정리하고 싶지 않습니다. 현실적으로 DB를 설계할 때 대략적으로 어떤 수치가 들어올지 혹은 단위가 뭔지는 알지만 앞으로 어느 정도까지의 데이터가 관리될 지 장담할 수 있는 경우는 드뭅니다. 그리고 숫자 타입 컬럼 별로 데이터 유형을 다르게 해두면 컬럼간의 사칙연산이나 계산에 지장을 주게 됩니다. 그래서 앞서 문자를 varchar로 거의 통일해서 쓰는 것 처럼 숫자도 거의 통일 해서 쓰는 편입니다. 나중에 데이터 타입 변경하는 리스크를 안고 가는 것 보다는 디스크 용량 좀더 잡아 먹는 게 훨씬 더 유리힙니다. 저는 숫자 타입에서 bigint, decimal 2가지만 사용합니다. 소수점이 없는 정수는 bigint, 소스점이 필요한 경우 정수와 부동소수점 자리를 지정할 수 있는 decimal을 사용합니다. 나머지는 좀더 익숙해 지면 저절로 아시게 될 거라고 생각합니다. 이해를 돕기 위해 언급한 2개의 숫자 타입을 잠시만 설명 드릴 께요. 직접 test 라는 테이블을 만들었고, 컬럼은 a, b 2개입니다. a는 bigint, decimal 은 (5,2)로 생성했습니다. Bigint 자리수 20은 저절로 잡히는 거고, decimal의 5,2는 직접 정해줘야 합니다. 

  제가 a 컬럼에는 10.1 을, b 컬럼에는 55555.222 를 입력해 보았습니다. 일단 오류 없이 입력은 되었습니다. 입력된 결과를 조회해 보면 a 컬럼에 소수점 0.1 은 사라지고, b 컬럼은 999.99 로 입력되어 있습니다. a 컬럼은 이해 되실 겁니다. bigint 는 정수만 관리하기 때문에 소숫점을 빼고 정수만 입력되었습니다. 문제는 b컬럼인데, 선언을 decimal(5,2) 로 선언했는데 이 뜻은 앞에 5는 정수부와 소숫점부 자리수 모두 합친 자리수를 의미하고 끝에 2는 소수점부를 의미합니다. 따라서 정수부는 5-2=3 자리만 관리가능합니다. 입력한 값이 관리되는 최대값 999.99를 넘어서서 값이 저렇게 들어가는 겁니다. 

 

  값을 다시 입력했는데 a 컬럼에는 10000, b에는 123.45를 입력했습니다. 결과를 조회해보니 입력한 값대로 정상적으로 출력되는 것을 확인할 수 있습니다. 다시 말씀드리는데, 숫자 타입의 데이터 유형이 많이 있지만 각 유형별 제한사항을 외우기 번거롭고 또 서로 다른 유형의 숫자끼지 사칙연산을 사용하면 의도치 않은 결과가 나올 위험성이 있기 때문에 decimal 로 통일해서 씁니다.

 

  이제 날짜 타입의 데이터 유형에 대해 알아보겠습니다. MySQL에서 제가 주로 쓰는 날짜 함수는 딱 3개입니다. 첫번째 date 는 년-월-일로 1000-01-01 ~ 9999-12-31 까지 관리가능합니다. 두번재 datetime 은 덧붙여 시분초까지 관리됩니다. 그리고 세번째 timestamp 가 있습니다. 이것도 시분초까지 관리됩니다. 그럼 datetime과 timestamp가 같은 건가 봤을 때 쓰는 용도에 따라 같을 수도 있습니다. 그러나 timestamp는 1970년 ~ 2038년까지의 데이터를 관리합니다. 엄밀히 말하자면 timestamp는 1970년 1월 1일 부터 걸린 시간을 숫자로 관리합니다. 그런데 Timestamp는 아주 유용한 기능이 있습니다. 데이터를 입력하거나 자동으로 입력된 시점의 시간을 남길 필요가 있습니다. 즉, 자동으로 시간을 입력해 주는 기능이 있습니다. 아래 테이블 컬럼의 예제를 보면 입력시간 컬럼이 timestamp 타입이고, default 에 보면 CURRENT_TIMESTAMP 로 되어 있는 것을 볼 수 있습니다. 이 말은 데이터를 입력할 때, 입력시간 컬럼 값은 자동으로 현재 시간이 들어간다는 뜻입니다. 그리고 여기 현재시간에는 또 다른 중요한 것이 포함되어 있는데 바로 UTC (세계협정시) 로 관리 된다는 의미입니다. 한국 시간으로 시차가 적용된 것은 time zone 이란 것이 보통 기본적으로 system으로 설정되어 있어 OS 에 설정된 시간대로 시차를 적용해서 보여주고 있기 때문에 마치 한국시간이 입력되어 있다는 착각이 들기도 합니다.

※ 기본으로 현재 시간을 입력하는 timetamp 컬럼이 적용된 사례

 

  위 설명만으로 timetamp에 대한 이해가 되셨다고 생각이 되진 않습니다. 그래서 재밌는 실험을 하나 보여드리도록 하겠습니다. 실험은 current_timestamp가 기본 입력된 test 테이블을 만들고 조회해서 몇시에 데이터가 입력되었는지 확인 한다음, 윈도우 시간설정을 런던으로 봐꾼 후 다시 데이터를 조회해서 시간이 어떻게 조회 되는지 보는 것입니다.

 

 

이제 시간대를 UTC 런던으로 바꿉니다.

 

MySQL을 Restart 합니다. 변경된 시간대를 적용할려면 DB를 재가동 시켜야 합니다.

 

다시 test 테이블을 조회합니다. 재가동 되면서 인증을 다시 해야 합니다. 좀전에 23시 였는데 지금은 14시로 보입니다. 9시간 차이가 나는데, 분명 데이터를 변경하지 않아도 이렇게 보인 다는 겁니다. 즉, 실제 데이터는 세계 협정시 기준으로 저장되지만 시스템의 시간대 즉, time zone에 따라 시차를 적용해서 결과가 보인다는 겁니다. 이게 timestamp의 가장 큰 특징입니다. 향후, 다국적 시스템을 만들 때 이 부분이 유용하게 사용될 수도 있습니다.

※ 세계 협정시로 바꾸고 다시 조회

 

  사실 DB 4번째 교육만으로 이런 부분이 이해가실지는 잘 모르겠습니다. 그래도 너무 완벽하게 이해할려고 애쓰시기 보다 대충 감을 잡고 따라오시는 것을 권고 드립니다. 솔직히 말씀드리자면 UTC가 모르는 개발자도 수두룩합니다. 제가 넌즈시 같이 일하는 분들한테 물어봤는데 아무도 모르더군요. 경험으로 봤을 때 실적일자나 실적발생일시같은 경우는 date나 datetime을 씁니다. Audit 항목이라 해서 언제 입력되었는지 혹은 수정되었는지는 감시하기 위한 목적으로는 timestamp를 썼습니다. 그리고 날짜 타입을 쓰지 않고 varchar(8) 로 yyyymmdd 타입을 쓰거나 varchar(14) 로 yyyymmddHHmiss 형태을 문자열로 관리하는 경우도 있었습니다.

 

  

  이번 시간도 수고하셨습니다. 저도 전체 데이터 타입을 모두 숙지하는 것은 아닙니다. 제가 잘알고 있는 몇가지로 대부분 해결 가능하고 실제 업무에서도 사용가능한 데이터 타입을 최소화 시켜서 단순화 시킵니다. 복잡하면 다른 DB로 마이크레이션 하기가 어렵거든요. 다음 시간에 뵙겠습니다.