MySQL의 타임스탬프 2038 문제를 해결하는 방법

타임스탬프는 1970년 1월 1일 00:00:00 GMT(베이징 시간 1970년 1월 1일 08:00:00)부터 현재까지의 총 초 수를 나타냅니다.
MySQL 5.5/5.6/5.7의 세 가지 주요 버전과 N 마이너 버전을 포함하여 다양한 버전의 MySQL이 프로덕션 환경에 배포됩니다. MySQL의 낮은 상위 호환성으로 인해 동일한 SQL이 다음과 같이 버전마다 다르게 동작합니다. 데이터 유형은 여러 측면에서 자세히 소개됩니다.

타임스탬프 데이터 액세스

위의 세 가지 주요 MySQL 버전에서 기본 타임스탬프(Timestamp) 유형의 값 범위는 ’1970-01-01 00:00:01’ UTC부터 ’2038-01 - 19 03:14:07’ UTC에서 데이터는 두 번째 수준까지 정확합니다. 이 값 범위에는 약 22억 개의 값이 포함되어 있으므로 4바이트 INT 유형이 타임스탬프 데이터를 저장하는 데 사용됩니다.
1. 데이터를 스탬핑할 때 먼저 현지 시간대 시간을 UTC 시간대로 변환한 다음, UTC 시간대 시간을 UNIX_TIMESTAMP 함수를 사용하여 INT 형식의 밀리초 값으로 변환한 후 데이터베이스에 저장합니다.
2. 타임스탬프 데이터를 읽을 때 먼저 INT 형식의 밀리초 값을 FROM_UNIXTIME 함수를 사용하여 UTC 시간대 시간으로 변환한 다음 이를 현지 시간대로 변환하고 마지막으로 클라이언트에 반환합니다.

MySQL 5.6.4 이상 버전에서는 타임스탬프 유형 데이터를 마이크로초(백만분의 1초) 단위로 가장 높은 정밀도로 저장할 수 있습니다. 데이터 유형은 타임스탬프(N)로 정의됩니다. N의 값 범위는 0-6입니다. 기본값은 0입니다. 밀리초 단위로 정확해야 하면 Timestamp(3)로 설정하고, 마이크로초 단위로 정확해야 하면 Timestamp(6)로 설정하세요. 내부 저장 공간은 있지만 타임스탬프 유형은 변경되지 않습니다.

타임스탬프 필드 정의

타임스탬프 필드 정의는 주로 두 가지 유형의 작업에 영향을 미칩니다.

• 레코드를 삽입할 때 타임스탬프 필드에는 DEFAULT CURRENT_TIMESTAMP가 포함됩니다. 레코드를 삽입할 때 특정 시간 데이터가 지정되지 않으면 타임스탬프 필드가 포함됩니다. 현재 시간

• 에 대한 레코드를 업데이트할 때 타임스탬프 필드에 ON UPDATE CURRENT_TIMESTAMP가 포함됩니다. 레코드 업데이트 시 특정 시간 데이터가 지정되지 않으면 타임스탬프 필드 값이 현재 시간

으로 설정됩니다.

PS1: CURRENT_TIMESTAMP는 NOW() 함수와 유사하게 CURRENT_TIMESTAMP( ) 함수를 사용하여 현재 시간을 가져오는 것을 의미합니다.

위의 두 가지 작업 유형에 따라 타임스탬프 열에는 네 가지 조합 정의가 있을 수 있으며 그 의미는 다음과 같습니다.

• 필드가 타임스탬프로 정의되면 해당 필드가 삽입이나 업데이트 모두 자동으로 현재 시간으로 설정되지 않음을 의미합니다.

• 필드가 타임스탬프 DEFAULT CURRENT_TIMESTAMP로 정의되면 필드는 삽입 시 현재 시간만 할당되고 값이 지정되지 않으며 업데이트되고 값이 지정되지 않을 때 수정되지 않음을 의미합니다.

• 필드가 타임스탬프 ON UPDATE CURRENT_TIMESTAMP로 정의되면 삽입 시 필드에 "0000-00-00 00:00:00" 값이 할당되고 값이 지정되지 않으며 현재 시간으로 업데이트된다는 의미입니다. 업데이트되고 값이 지정되지 않은 경우.

• 필드가 타임스탬프 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP로 정의되면 삽입 또는 업데이트 시 필드에 값이 지정되지 않고 현재 시간이 할당된다는 의미입니다.

PS1: MySQL에서 실행되는 테이블 생성 문과 최종 테이블 생성 문에는 차이가 있습니다. 생성된 테이블의 테이블 생성 문을 얻으려면 SHOW CREATE TABLE TB_XXX를 사용하는 것이 좋습니다.

다양한 MySQL 버전의 타임스탬프 필드 사용 차이

• MySQL 5.5 및 이전 버전에서는 하나의 타임스탬프 필드에 대해 DEFUALT CURRENT_TIMESTAMP 또는 ON UPDATE CURRENT_TIMESTAMP만 정의할 수 있었지만 MySQL 5.6 및 MySQL에서는 이것이 취소되었습니다. 5.7. 이 제한 사항

• MySQL 5.6 버전에서는 explain_defaults_for_timestamp 매개변수의 기본값이 1이고, MySQL 5.7 버전에서는explicit_defaults_for_timestamp 매개변수의 기본값이 0입니다.

• MySQL 5.5 및 MySQL 5.7 버전에서는 타임스탬프 유형의 기본값은 NOT NULL입니다. MySQL 5.6 버전에서는 타임스탬프 유형의 기본값은 NULL입니다.

c1 타임스탬프가 테이블 생성 문에 설정된 경우

• 는 MySQL 5.5 DEFAULT CURRENT_TIMESTAMP의 c1 타임스탬프 NOT NULL과 동일합니다. ON UPDATE CURRENT_TIMESTAMP;

• MySQL 5.6의 c1 타임스탬프 NULL DEFAULT NULL과 동일;

MySQL 5의 c1 타임스탬프 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP와 동일합니다. 7;

• c1 타임스탬프가 기본값인 경우 0은 테이블 생성 문에 사용됩니다.

• MySQL 5.5의 c1 timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’

• c1 timestamp NULL ‘0000과 동일합니다. MySQL 5.6에서 -00-00 00: 00:00’;

은 MySQL 5.7에서 c1 타임스탬프 NOT NULL DEFAULT ‘0000-00-00 00:00:00’;

🎜과 동일합니다. licit_defaults_for_timestamp 매개변수의 기본값입니다. 🎜🎜PS2: 타임스탬프 열의 기본값이 ’0000-00-00 00:00:00’인 경우 "타임스탬프 값 범위 내에 있지 않음"의 기본값을 사용하면 경고가 생성되지 않습니다. 🎜

타임스탬프 유형으로 인한 예외

MySQL 매개변수 time_zone=system일 때 타임스탬프 필드를 쿼리하면 시간대 변환을 위해 시스템 시간대가 호출됩니다. 그러나 시스템 시간대의 전역 잠금 문제로 인해 스레드 컨텍스트가 자주 발생합니다. 여러 개의 동시 대용량 데이터 액세스가 발생하면 스위치, CPU 사용량이 급증하고 시스템 응답이 느려지고 일시 중지된 애니메이션이 설정됩니다.

타임스탬프 유형 및 시간 유형 선택

일부 "데이터베이스 지침" 문서에서는 날짜/시간 필드 대신 타임스탬프 유형을 사용하는 것이 좋습니다. 그 이유는 타임스탬프 유형이 4바이트를 사용하는 반면 날짜/시간 필드는 8바이트를 사용하기 때문입니다. 하지만 실제 프로덕션 환경에서는 타임스탬프 유형을 사용해도 성능이 크게 향상되지 않으며, 타임스탬프 유형의 정의 및 값 범위로 인해 비즈니스 사용에 제한이 있을 수 있습니다.

MySQL 5.6.4 이상 버전에서는 타임스탬프 유형(timestamp) 데이터를 가장 높은 정밀도의 마이크로초로 변환할 수 있으며, 시간 유형(datetime) 데이터도 가장 높은 정밀도의 마이크로초로 변환할 수 있습니다. ) 또한 타임스탬프 유형을 얻을 수 있습니다. 예를 들어 필드를 dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3);으로 정의하는 것과 같습니다. 시간 유형(datetime)의 액세스 범위는 ’1000입니다. -01-01 00:00:00.000000’ "9999-12-31 23:59:59.999999"로 변경하면 각 기간의 데이터를 더 잘 저장할 수 있습니다.

타임스탬프 유형 사용에 대한 권장 사항

데이터의 마지막 업데이트 시간만 중요시하는 경우 타임스탬프 열을 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

생성 시간과 업데이트 시간을 권장합니다. 업데이트 시간을 타임스탬프 필드로 설정하고 생성 시간을 DAETIME 또는 TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’로 정의하고 레코드를 삽입할 때 생성 시간을 명시적으로 지정합니다

. 단일 타임스탬프 열만 정의하고 DEFAULT 및 ON UPDATE 속성을 명시적으로 정의하는 것이 좋습니다.

MySQL에서는 타임스탬프 필드를 할당하거나 업데이트할 수 있지만 필요한 경우에만 타임스탬프 열을 명시적으로 삽입하고 업데이트하는 것이 좋습니다.

time_zone 매개변수를 시스템 외부의 값으로 설정하는 것이 좋습니다. 예를 들어 중국 서버는 "+8:00"으로 설정됩니다.

MySQL 오프라인 테스트 버전과 온라인 프로덕션 버전을 유지하는 것이 좋습니다. 일관된.

Timestamp와 datetime의 유사점과 차이점

동일점:

• 은 자동으로 업데이트 및 초기화될 수 있으며 기본 표시 형식은 동일 YYYY-MM-dd HH:mm:ss

차이:

• 타임스탬프 시간 범위는 다음과 같습니다: ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ 자동 시간대 변환, 밀리초의 실제 저장 바이트 저장

• 날짜/시간의 시간 범위: ‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’ 시간대, 8바이트 저장을 지원하지 않습니다.

타임스탬프 및 날짜 설정 자동 업데이트 시간

date 및 mydate에 값을 할당하지 않고 특정 데이터가 업데이트되거나 새 데이터가 삽입되면 date 및 mydate 두 필드가 자동으로 현재 기본값으로 설정됩니다. time

CREATE TABLE `mytime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `mydate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

2038 문제

타임스탬프에 저장된 시간이 '2038-01-19 03:14:07'UTC보다 큰 경우 mysql은 오류를 보고합니다. 이는 mysql 자체의 문제이므로 타임스탬프가 초과하면 당연히 오류가 보고됩니다. 일부 스크린샷은 다음과 같습니다.

Solution

timestamp 상한이 있지만 타임스탬프를 저장하므로 시간대 관련 요구 사항을 처리해야 하는 경우에는 시간대 문제를 고려할 필요가 없습니다. , 2038 제한을 해결할 수 있습니다. 타임스탬프를 정수 유형으로 변경하여 타임스탬프를 저장한 다음 프로그램에서 변환하는 것이 좋습니다(이 솔루션은 구현되지 않았으며 단지 제안일 뿐이므로 주의해서 사용하세요! !)

시간대 문제를 고려할 필요가 없다면 datatime 유형의 타임스탬프로 직접 대체하면 충분합니다. datatime의 값 범위가 훨씬 크기 때문에 위의 그림을 볼 수 있습니다.

교체 아이디어:

1. 원본 필드의 이름을 수정합니다.

ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';

새 데이터 시간 유형 필드를 만듭니다(원본을 대체할 새 열 생성).

ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';

원래 필드 열의 데이터를 새 필드 열에 복사합니다.

UPDATE `student` SET `entry_date` = `temp_entry_date`;

전체 SQL은 다음과 같습니다. (원래 타임스탬프의 기본값도 필요하다는 점에 유의해야 합니다. 또한)

ALTER TABLE `student` DROP `temp_entry_date`;

위 내용은 MySQL의 타임스탬프 2038 문제를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!