NOT NULL TIMESTAMP 타입 column이 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP로 설정되었을 때 해결법

아래와 같은 테이블 생성 스크립트를 돌렸는데, DESC 테이블을 해봤더니 결과가 조금 이상했다.

 

CREATE TABLE `coupon`
(
    `id`		bigint(20) NOT NULL AUTO_INCREMENT,
    `name`		varchar(30) NOT NULL, 
    `description`	varchar(30) NOT NULL, 
    `user_id`		bigint(20) NOT NULL,
    `expire_at`		timestamp(3) NOT NULL,
    `created_at`	timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at`	timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

DESC coupon;

=====================================

NO   Field                       Type            Null   Key   Default                Extra                            
--   -------------------------   -------------   ----   ---   --------------------   ------------------------------   
1    id 			 bigint(20)      NO     PRI   (NULL)                 auto_increment                   
2    name			 varchar(66)     NO           (NULL)                                                  
3    description		 varchar(66)     NO           (NULL)                                                  
4    user_id			 bigint(20)      NO           (NULL)                 
5    expire_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)   on update CURRENT_TIMESTAMP(3)   
6    created_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)                                    
7    updated_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)   on update CURRENT_TIMESTAMP(3)

 

expire_at은 별다른 DEFAULT, on update 설정이 없었음에도 불구하고 DEFAULT CURRENT_TIMESTAMP, on update CURRENT_TIMESTAMP로 설정이 된 것이다.

 

원인은 MySQL의 `explicit_defaults_for_timestamp` 이라는 timestamp type column의 default를 조절하는 옵션과 관련이 있었다. 이 옵션이 OFF인 경우, timestamp type column의 default는 아래와 같이 동작한다.

 

1. nullable로 선언되지 않았다면, 자동으로 NOT NULL로 선언된다.

2. NULL을 insert하면 자동으로 CURRENT_TIMESTAMP가 들어간다.

3. nullable이 아니면서 default 값이 정해지지 않은 timestamp 컬럼이 처음 등장했을 때, DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP로 선언된다.

4. 두 번째 timestamp 컬럼부터는 nullable이 아니면서 DEFAULT가 선언되지 않았다면 자동으로 DEFAULT ‘0000-00-00 00:00:00’로 선언된다.

 

아래와 같은 Query로 확인할 수 있다.

show variables like "explicit%"

Variable_name				Value
-------------				-----
explicit_defaults_for_timestamp		OFF

 

set global explicit_defaults_for_timestamp = 1; 로 해당 옵션을 enable해주고 다시 테이블을 생성하면, 아래와 같이 정상적으로 테이블이 생성이 된다 🙂

 

NO   Field                       Type            Null   Key   Default                Extra                            
--   -------------------------   -------------   ----   ---   --------------------   ------------------------------   
1    id 			 bigint(20)      NO     PRI   (NULL)                 auto_increment                   
2    name			 varchar(66)     NO           (NULL)                                                  
3    description		 varchar(66)     NO           (NULL)                                                  
4    user_id			 bigint(20)      NO           (NULL)                 
5    expire_at			 timestamp(3)    NO           (NULL)
6    created_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)                                    
7    updated_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)   on update CURRENT_TIMESTAMP(3)

 

테이블에 이미 데이터들이 들어가 있어서, 새롭게 테이블을 생성할 수 없는 상황도 있을 것이다. 이 경우 default값과 on update 값을 제거하는 방법은 다음과 같다.

// on update 제거
ALTER TABLE coupon CHANGE expire_at expire_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3);
// default값 제거
ALTER TABLE coupon alter column expire_at DROP DEFAULT;

DESC coupon;

=====================================

NO   Field                       Type            Null   Key   Default                Extra                            
--   -------------------------   -------------   ----   ---   --------------------   ------------------------------   
1    id 			 bigint(20)      NO     PRI   (NULL)                 auto_increment                   
2    name			 varchar(66)     NO           (NULL)                                                  
3    description		 varchar(66)     NO           (NULL)                                                  
4    user_id			 bigint(20)      NO           (NULL)                 
5    expire_at			 timestamp(3)    NO           (NULL)
6    created_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)                                    
7    updated_at			 timestamp(3)    NO           CURRENT_TIMESTAMP(3)   on update CURRENT_TIMESTAMP(3)

Reference

1. http://minsql.com/mysql/MySQL-explicit_defaults_for_timestamp/