时间戳

首页 > 解决方案 > MySQL 触发器使用不同的 TIMEZONE

mysql - MySQL 触发器使用不同的 TIMEZONE

问题描述

我创建了一个触发器来为购买设置一个 uniq 名称（参考），使用时区作为 create_date 字段。它工作正常，直到我注意到重复的引用，其中一个引用的引用中的日期与 create_date 字段中的日期不匹配（差异的 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;

这就是我得到的：

在此处输入图像描述

标签： mysqltriggerstimezonemariadb

解决方案

在与表进行比较之前，您可以使用@@session.time_zone触发器中的 CONVERT_TZ 将给定的时间戳转换为 SYSTEM time_zone。 

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

推荐阅读