[Mysql-Mariadb] datetime 에 공백 넣기(NO_ZERO_DATE, NO_ZERO_IN_DATE)

MySQL 및 MariaDB에서 데이트타임 필드에 공백 입력 시 발생하는 에러 해결 방법

Mysql이나 Mariadb를 사용할 때 데이트타임 필드에 공백을 입력해야 하는 상황이 발생하는데
그때 공백이 올바르게 입력되어도 에러가 발생하는 경우가 있을 경우 해결하는 케이스 입니다.

에러 상황

다음과 같은 기존 쿼리에서 데이트타임 필드에 공백을 넣으면 문제가 없는 경우도 있지만 갑자기 에러가 발생할 수 있습니다.

UPDATE USER
SET AGREE_DT = :AGREE_DT,
       AGREE_SEQ = :AGREE_SEQ
WHERE USER_SEQ = :USER_SEQ

에러 내용: Error 1292: Incorrect datetime value: '' for column 'AGREE_DT' at row 1

 

해결 방법

이런 경우에는 sql_mode를 확인해야 합니다.

SELECT @@sql_mode

위와 같이 쿼리를 수행할 때 NO_ZERO_DATE 및 NO_ZERO_IN_TIME이 항목 중에 있는지 확인해야 합니다.

• 해당 항목이 보이면 ON이며, 보이지 않으면 OFF입니다.

이 두 옵션에 대해 먼저 알아봅시다.

• NO_ZERO_TIME: 데이트타임 필드에 공백 값이 입력되면 자동으로 0000-00-00 00:00:00 형식으로 등록됨을 보장합니다.
• NO_ZERO_IN_TIME: 연도 및 월에 0을 입력하는 것을 허용하지 않습니다. _2023-00-12 08:57:11_과 같은 데이터를 삽입하려고 하면 에러가 발생합니다.

설정 변경

• 빈 공백에 기본값으로 _0000-00-00 00:00:00_을 입력하려면 OFF이어야 합니다.
• 일반 데이트타임 형식으로만 등록하려면 ON이어야 합니다.

위의 옵션을 설정하려면 다음 쿼리를 수행하면 됩니다.

SET SESSION sql_mode='STRICT_TRANS_TABLE,NO_ZERO_TIME,NO_ZERO_IN_DATE'

STRICT_TRANS_TABLE 옵션은 요청 트랜잭션에 대한 데이터 검증 및 처리를 담당하며 명시적으로 ON 상태로 유지됩니다.

글로벌 설정

만약 GLOBAL로 설정하려면 다음과 같이 수행합니다. 이때는 super privileges가 있는 계정 (예: root)으로 수행되어야 합니다.

SET GLOBAL sql_mode='STRICT_TRANS_TABLE,NO_ZERO_DATE,NO_ZERO_IN_DATE'