主页 / dba / 问题 / 291352
Accepted
E.Aigle
Asked: 2021-05-12 00:38:20 +0800 CST

在 MySQL 中将字符串更改为日期时间的存储过程

  • 772

这是对这个问题的跟进。原来我正在使用的驱动程序（无法更改）与时间戳值不兼容。可以说，我必须将数据作为字符串类型加载到我的 MySQL 8.0 数据库中。我用来读取 PLC 并写入数据库的软件中没有本机功能，可以将字符串更改为时间戳。

我现在想弄清楚的是如何设置一个触发器，将字符串转换为 insert 上的时间戳。这个想法是软件的帮助热线向我提出的，但如果你有更好的想法，我也愿意接受。到目前为止，我有两列：一列是我“接收”类型为 VARCHAR(30) 的字符串，另一列是我用触发器写入时间戳值。

这种设置对我来说感觉非常愚蠢，因为我最终得到了 2 列具有相同数据但格式不同的列，其中一个完全没用。

到目前为止，我的桌子看起来像：

CREATE TABLE `test` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `Date_VARCHAR` varchar(30) DEFAULT NULL,
  `Date_TIMESTAMP` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

触发器是：

DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `db_test`.`test_BEFORE_INSERT`     
    BEFORE INSERT ON `db_test`.`test` FOR EACH ROW
    BEGIN  
    SET NEW.Date_TIMESTAMP = NEW.Date_VARCHAR ;
    END$$
DELIMITER ;

有没有更好的方法来避免两次拥有相同的数据？我想制作一个触发器，它只转换数据并将其全部写入一列，而不是我想出的设置。

这里快速浏览一下我正在做的事情。我已经插入了一些随机值，但实际上都是通过 KEPServerEx 的数据记录器插件完成的。

mysql trigger

1 个回答

  1. Best Answer

    根据您可以申请的小提琴

    ALTER TABLE test
DROP COLUMN Date_TIMESTAMP,
ADD COLUMN Date_TIMESTAMP TIMESTAMP AS (STR_TO_DATE(Date_VARCHAR, '%Y-%m-%dT%h-%i-%s'));

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=104e72ae4730b2c8c30f84f61bf392d7

    该列Date_TIMESTAMP可以定义为 STORED（占用磁盘空间，快速访问）或 VIRTUAL（每次检索其值时计算，不占用磁盘空间）。

    或者，您可以使用 BEFORE INSERT 和 BEFORE UPDATE 触发器，它们会将插入的数据转换为正确的日期时间文字（但该列仍将保持 VARCHAR）。在 BEFORE UPDATE 中，您可以检查当前数据格式以避免双重转换导致错误。

    CREATE TRIGGER trigger_name  
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.Date_VARCHAR = STR_TO_DATE(NEW.Date_VARCHAR, '%Y-%m-%dT%h-%i-%s');

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=08eb56a0b8846297c81c07e2703c0ab9

    • 1

相关问题