MySQL Workbench 날짜 컬럼 데이터 타입과 날짜 형식 바꾸기

 

 

 

문제 상황

 

사용하려던 데이터 csv 파일을 가져와서 확인해 봤더니, 날짜 컬럼인 InvoiceDate의 데이터 타입이 TEXT인 상황.

그리고 테이블을 확인하면, 날짜 형식은 미국식(MDY)이다.

 

(BEFORE) TEXT 데이터 타입, 미국식 날짜 형식

 

 

내가 원하는 것은 DATETIME 데이터 타입으로, 날짜 형식은 한국식(YMD)으로 변환하기.

 

 

 

MySQL Workbench 날짜 컬럼 데이터 타입과 날짜 형식 바꾸기

 

• ALTER TABLE: 테이블 변경
• ADD & DROP COLUMN: 컬럼 생성/삭제
• STR_TO_DATE: 문자를 날짜 타입으로 변환

첫번째 주의할 점은 MySQL Workbench의 안전모드이다.

만약 전체 테이블을 변경하고자, 코드를 그냥 실행하면 Error Code 1175를 만날 것이다.

이건 워크벤치가 실수로 변경되는 것을 방지하려고 안전모드 상태를 유지하기 때문에 발생하는 에러다.

해결 방법은 안전모드를 잠시 해제했다가 재활성화하는 방법이 있다.

 

두번째 주의할 점은 STR_TO_DATE() 사용 시, 날짜 포맷을 원본 데이터 포맷과 동일하게 입력해야 한다는 점이다.

원본 포맷과 다르게 입력한다면, 새로운 컬럼에 추가되는 날짜가 이상하게 저장될 수 있다.

예를 들어, 12/1/2020에서 12=월, 1=일인데 이 포맷과 다르게 입력하면 월을 일로, 일을 월로 잘못 저장하는 일이 발생한다.

 

-- 새 컬럼 생성
ALTER TABLE ecommerce
ADD COLUMN Invoice_dt DATETIME;

-- 안전 모드 해제
SET SQL_SAFE_UPDATES = 0;

-- 데이터 타입 변환 및 추가
UPDATE ecommerce
SET Invoice_dt = STR_TO_DATE(Invoicedate, '%m/%e/%Y %H:%i');

-- 안전 모드 활성
SET SQL_SAFE_UPDATES = 1;

-- 기존 컬럼 삭제 및 새 컬럼명 변경
ALTER TABLE ecommerce
DROP COLUMN InvoiceDate;

ALTER TABLE ecommerce
CHANGE COLUMN Invoice_dt InvoiceDate DATETIME;    -- 새 컬럼명 및 데이터 타입 설정

 

 

 

최종 결과

 

워크벤치의 테이블 설정에서 데이터 타입을 확인하니 DATETIME으로 잘 변경되어 있다.

바뀐 테이블을 불러와서 보면 InvoiceDate 컬럼의 데이터도 원하는 형식으로 바뀌었다.

(AFTER) DATETIME 데이터 타입, 한국식 날짜 형식