MySQL 트리거가 다른 TIMEZONE을 사용함

MySQL 트리거가 다른 TIMEZONE을 사용함

create_date 필드에 타임존을 사용하여 구매용 유니크 이름(ref)을 설정하는 트리거를 만들었습니다.중복된 레퍼런스를 발견할 때까지 정상적으로 동작했습니다.레퍼런스 중 하나의 레퍼런스의 날짜가 create_date 필드의 날짜와 일치하지 않습니다(diff의 1일).

BEGIN
SET NEW.reference := concat(
(SELECT name FROM provider WHERE id = NEW.provider_id),
 date_format(NEW.create_date, '%Y%m%d'), '/',
  (SELECT LPAD(IFNULL(MAX(SUBSTRING_INDEX(reference, '/', -1)) + 1, 0), 3, '0') 
   FROM purchase
   WHERE date_format(NEW.create_date, '%Y%m%d') = date_format(create_date, '%Y%m%d')
      AND NEW.provider_id = provider_id
  )
);
END

무슨 일이 일어났는지 더 나은 방법을 아는 사람?

PS: create_date 필드의 날짜가 올바르고, ref에서 사용되는 NEW.create_date의 날짜가 잘못되었습니다(클라이언트 시간대의 b/c일 가능성이 있습니다).

---

갱신하다

테이블 구조:

구입:

CREATE TABLE purchase
(
  id             int          auto_increment primary key,
  provider_id    int          not null,
  create_date    timestamp    null,
  create_user    int          null,
  change_date    timestamp    null,
  change_user    int          null,
  group_id       int          null,
  reference      varchar(45)  null
);

CREATE INDEX purchase_reference_index ON purchase (reference);
CREATE INDEX purchase_provider_index ON purchase (provider_id);

프로바이더:

CREATE TABLE provider
(
  id           int auto_increment primary key,
  name         varchar(45) null,
  constraint name_uniq unique (name)
);

쿼리 예:

INSERT INTO purchase (provider_id, create_date, create_user, group_id)
VALUE (4, '2019-01-30 02:36:58', 1, 3);

2019-01-30 02:36:58로서 데이터베이스에 저장됩니다.2019-01-29 23:36:58세션에서 선택하면 서버 표준 시간대를 사용합니다.

표준 시간대를 설정하는 데 사용하는 기능:

function update_timezone($timezone = null)
{
    if (is_null($timezone)) $timezone = __SERVER_TIMEZONE;

    if (in_array($timezone, timezone_identifiers_list())) {
        date_default_timezone_set($timezone);

        $tz = (new DateTime('now', new DateTimeZone(date_default_timezone_get())))->format('P');
        $conn = Database::Connect();
        Database::NonQuery("SET time_zone = '$tz';", $conn);
    }
}

내가 기대하는 것:

reference === 'provider_name20190129/00X'

제공 내용:

reference === 'provider_name20190130/00Y'

문제를 재현하는 방법:

CREATE DATABASE test;

CREATE TABLE purchase
(
  id             int          auto_increment primary key,
  provider_id    int          not null,
  create_date    timestamp    null,
  create_user    int          null,
  change_date    timestamp    null,
  change_user    int          null,
  group_id       int          null,
  reference      varchar(45)  null
);

CREATE INDEX purchase_reference_index ON purchase (reference);
CREATE INDEX purchase_provider_index ON purchase (provider_id);

CREATE TABLE provider
(
  id           int auto_increment primary key,
  name         varchar(45) null,
  constraint name_uniq unique (name)
);

CREATE TRIGGER test.purchase_ref_insert
  BEFORE INSERT
  ON test.purchase
  FOR EACH ROW
BEGIN
  SET NEW.reference := concat(
      (SELECT name FROM provider WHERE id = NEW.provider_id),
      date_format(NEW.create_date, '%Y%m%d'), '/',
      (SELECT LPAD(IFNULL(MAX(SUBSTRING_INDEX(reference, '/', -1)) + 1, 0), 3, '0')
       FROM purchase
       WHERE date_format(NEW.create_date, '%Y%m%d') = date_format(create_date, '%Y%m%d')
         AND NEW.provider_id = provider_id
      )
  );
END
;

INSERT INTO provider (name) VALUE ('test');


SET time_zone = '+00:00';
INSERT INTO purchase (provider_id, create_date, create_user, group_id)
VALUE (1, '2019-01-30 02:36:58', 1, 3);


SET time_zone = '+05:00';
INSERT INTO purchase (provider_id, create_date, create_user, group_id)
VALUE (1, '2019-01-30 02:36:58', 1, 3);

SET time_zone = '-05:00';
INSERT INTO purchase (provider_id, create_date, create_user, group_id)
VALUE (1, '2019-01-30 02:36:58', 1, 3);


SET time_zone = '+00:00';
SELECT create_date, reference FROM purchase;

그리고 내가 얻는 것은 다음과 같다.

를 사용할 수 있습니다.@@session.time_zone트리거를 CONVERT_로 설정합니다.테이블과 비교하기 전에 지정된 타임스탬프를 SYSTEM time_zone에 TZ합니다.

CREATE TRIGGER purchase_ref_insert
  BEFORE INSERT ON purchase
  FOR EACH ROW
BEGIN
 SET @ts := CONVERT_TZ(NEW.create_date, @@session.time_zone, 'SYSTEM');
 SET @providerName := (SELECT name FROM provider WHERE id = NEW.provider_id);
 SET @refPostfix := (
         SELECT 
         LPAD(IFNULL(MAX(SUBSTRING_INDEX(reference, '/', -1)) + 1, 0), 3, '0')
         FROM purchase
         WHERE NEW.provider_id = provider_id
           AND CAST(CONVERT_TZ(create_date, @@session.time_zone, 'SYSTEM') AS DATE) = CAST(@ts AS DATE)
     );
 SET NEW.create_date_tz = @@session.time_zone;
 SET NEW.reference := CONCAT(@providerName, DATE_FORMAT(@ts, '%Y%m%d'), '/', @refPostfix);
END;

테이블에 열을 하나 더 추가할 수 있습니다.
레코드가 삽입되었을 때 사용된 세션의 시간대를 포함하는 시간대입니다.

create_date_tz varchar(6)   not null default 'SYSTEM'

이렇게 하면 create_date를 고객의 시간대로 되돌릴 수 있습니다.

여기서 db <> fiddle 테스트

예:

 SELECT 
  id, create_date, reference, create_date_tz,
  CONVERT_TZ(create_date, 'SYSTEM', create_date_tz) as ts_at_TZ
 FROM purchase;

반환:

id | create_date         | reference        | create_date_tz | ts_at_TZ           
 1 | 2019-01-30 07:30:01 | test20190130/000 | -05:00         | 2019-01-30 02:30:01
 2 | 2019-01-30 02:30:02 | test20190130/001 | +00:00         | 2019-01-30 02:30:02
 3 | 2019-01-29 21:30:03 | test20190129/000 | +05:00         | 2019-01-30 02:30:03

해결책을 찾았습니다.d

방아쇠 안에 시간대를 설정하기만 하면 됩니다.SET time_zone = 'SYSTEM';

date_format이 현지화된 표준 시간대를 따르지 않습니다.

CREATE TRIGGER test.purchase_ref_insert
  BEFORE INSERT
  ON test.purchase
  FOR EACH ROW
  BEGIN
    SET @time_zone_tmp := @@time_zone;
    SET time_zone = 'SYSTEM';
    SET NEW.reference := concat(
        (SELECT name FROM provider WHERE id = NEW.provider_id),
        date_format(NEW.create_date, '%Y%m%d'), '/',
        (SELECT LPAD(IFNULL(MAX(SUBSTRING_INDEX(reference, '/', -1)) + 1, 0), 3, '0')
         FROM purchase
         WHERE date_format(NEW.create_date, '%Y%m%d') = date_format(create_date, '%Y%m%d')
           AND NEW.provider_id = provider_id
        )
    );
    SET time_zone = @time_zone_tmp;
  END
;

언급URL : https://stackoverflow.com/questions/54363954/mysql-triggers-use-different-timezone