Início / dba / Perguntas / 291352
Accepted
E.Aigle
Asked: 2021-05-12 00:38:20 +0800 CST

Procedimento armazenado para alterar string para datetime no MySQL

  • 772

Este é um seguimento a esta pergunta . Acontece que o driver que estou usando (não posso alterá-lo) não é compatível com valores de carimbo de data/hora. Vamos dizer que está definido que eu tenho que carregar os dados no meu banco de dados MySQL 8.0 como um tipo de string. Não há nenhuma função nativa dentro do software que estou usando para ler no PLC e escrever no banco de dados que pode alterar uma string para um timestamp.

O que estou tentando descobrir agora é como configurar um gatilho que converte a string em um timestamp em insert . Essa ideia me foi sugerida pela linha direta de ajuda do software, mas se você tiver alguma ideia melhor, estou aberto a isso também. Até agora, tenho 2 colunas: uma onde "recebo" a string que é do tipo VARCHAR(30), e outra onde escrevo o valor do timestamp com o trigger.

Essa configuração parece super estúpida para mim porque acabo com 2 colunas com os mesmos dados apenas em formatos diferentes, e uma delas é completamente inútil.

Até agora, minha tabela está assim:

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;

E o gatilho é:

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 ;

Existe uma maneira melhor de fazer isso para evitar ter os mesmos dados duas vezes? Eu gostaria de fazer um gatilho que apenas converta os dados e grave tudo em uma coluna em vez da configuração que criei.

Aqui está uma rápida olhada no que eu tenho acontecendo . Eu fiz uma inserção de alguns valores aleatórios, mas na verdade tudo está sendo feito com o plugin datalogger do KEPServerEx.

mysql trigger

1 respostas

  1. Best Answer

    Com base no violino você pode aplicar

    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

    A coluna Date_TIMESTAMPpode ser definida como STORED (ocupa espaço em disco, acesso rápido) ou VIRTUAL (calculada cada vez que seu valor é recuperado, não ocupa espaço em disco).

    Alternativamente, você pode usar os gatilhos BEFORE INSERT e BEFORE UPDATE que converterão os dados inseridos em literais de data e hora corretos (mas a coluna permanecerá VARCHAR mesmo assim). Em BEFORE UPDATE você pode verificar o formato de dados atual para evitar a conversão dupla que causará um erro.

    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

relate perguntas