MySQL 8.0 Incorrect date value error
aws Aurora MySQL 5.7에서 8 업그레이드 하면서 나온 에러
date 컬럼에 where '' 했더니 에러 하지만 '0000-00-00' 검색 됨.
이유는 고마우신분이 작성한 글 내용
MySQL 서버에서 sql_mode 옵션에 NO_ZERO_DATE 옵션과, NO_ZERO_IN_DATE 옵션이 존재한다. 이 2개 옵션은 다음과 같이 동작한다.
NO_ZERO_DATE
enable: 0000-00-00 값을 invalid date로 인식한다.
disable: 0000-00-00 값을 저장할 수 있다.
NO_ZERO_IN_DATE
enable: date의 month 또는 day 부분에 0이 입력되면 invalid date로 인식한다.
disable: 2022-12-00 또는 2022-00-01과 같은 형태의 date 입력이 가능하다.
MySQL 5.7, 8.0 이상의 버전에서는 아래와 같은 테이블을 생성하고 데이터를 넣지 않은 상태에서
mysql> CREATE TABLE `new_table` (
`id_table` int(11) NOT NULL AUTO_INCREMENT,
`text_table` varchar(45) DEFAULT NULL,
`date_table` date DEFAULT NULL,
PRIMARY KEY (`id_table`)
) ENGINE=InnoDB
다음의 WHERE절 조건을 가진 쿼리를 수행하면,
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = ''
OR
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '0000-00-00'
Incorrect date value라는 오류가 발생한다.
데이터를 넣는 경우에도 마찬가지이다.
반면에 MySQL 5.6 버전에서는 경고(Warning)가 발생할 뿐 오류(Error)는 발생하지 않는다.
History
: MySQL 5.7 이전 버전에서는 기본 값으로 0000-00-00 이라는 DATE 값 저장할 수 있었으나, MySQL 5.7, 8.0 버전부터는 기본값으로 0000-00-00 을 저장할 수 없도록 변경되었다. 이 값을 저장하도록 변경하려면 SQL_MODE에서 NO_ZERO_IN_DATE 와 NO_ZERO_DATE 옵션을 제거해야 한다(이 상황에서는 NO_ZERO_DATE 옵션만).
NO_ZERO_IN_DATE, NO_ZERO_DATE 옵션 제거
옵션을 제거하고 2022-06-00 데이터를 넣으면 데이터가 정상적으로 저장되는 것을 확인 할 수 있다.
mysql> INSERT INTO new_table (text_table, date_table) VALUES ('good','2022-06-16'), ('one zero','2022-06-00');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from new_table;
+----------+------------+--------------+
| id_table | text_table | date_table |
+----------+------------+--------------+
| 1 | good | 2022-06-16 |
| 2 | one zero | 2022-06-00 |
| 3 | good | 2022-06-16 |
| 4 | one zero | 0000-00-00 |
+----------+------------+--------------+
하지만, 아래 WHERE 조건으로 데이터를 조회할 때는 여전히 오류가 발생한다.
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '';
ERROR 1525 (HY000): Incorrect DATE value: ''
Warning (Code 1292): Incorrect date value: '' for column 'date_table' at row 1
Error (Code 1525): Incorrect DATE value: ''
empty set과 0000-00-00 조건은 NO_ZERO_DATE 옵션이 비활성화되기 전까지는 동일한 Incorrect DATE value 오류를 리턴했기 때문에 해당 옵션을 비활성화하면 empty value 조건에도 영향이 있을거라 생각했지만 그렇지 않았다. 즉 empty value는 NO_ZERO_IN_DATE, NO_ZERO_DATE 옵션과 전혀 관련이 없는 string 조건일 뿐이다.
mysql> mysql> select * from new_table where date_table = '0000-00-00';
+----------+------------+------------+
| id_table | text_table | date_table |
+----------+------------+------------+
| 4 | one zero | 0000-00-00 |
+----------+------------+------------+
MySQL 서버가 date 타입 컬럼에서 string을 처리하는 방식
MySQL 서버가 date 타입 컬럼에서 string을 처리하는 방식은 5.x 버전에서 8.0 버전으로 넘어가는 과정에서 변경되었다.
상수 문자열(constant string)로 DATE 값을 비교할 때, MySQL은 먼저 string을 date로 변환(conversion)하고 비교(comparison)를 수행한다. MySQL 8.0(8.0.16) 이전 버전에서는 이 conversion 과정(string → date)에서 오류가 발생하면 그대로 오류를 리턴하는 것이 아니라 값 비교 대상을 date에서 string으로 바꾸어 수행했다. 하지만 MySQL 8.0 버전 이상에서는 conversion 과정에서 fail이 발생하면 comparison 단계에서 ER_WRONG_VALUE와 함께 fail이 발생하도록 변경되었다.
정리하면, invalid value(string)가 조건절에 사용될 경우
5.x: 경고 발생(warning)
8.0: 오류 발생(error)
[출처] MySQL 8.0 string dates|작성자 이범확
https://blog.naver.com/sinjoker/222960058609
댓글
댓글 쓰기