Create and store MD5 hash of existing value after INSERT

Question

I have an X table, with a field called email and another email_hash, I would like that every time a new record was inserted with email, Mysql would use the value of the inserted email and create an MD5 hash and insert it in the email_hash, how can I do this? triggers?

Answer 1

To encrypt a value in MD5, just use the same name function, for example:

SELECT MD5('Psr');
        -> '0dd833a62f068acadd6604eec8daf236'

With this you can use the trigger_time BEFORE with the trigger_event INSERT. For example:

/* Cria a tabela com o campos necessários */
CREATE TABLE `user` (
    `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `email` VARCHAR(40) NOT NULL,
    `email_hash` VARCHAR(32) NULL
);

delimiter //

/* Cria o trigger para setar, antes de inserir no DB, o valor do campo `email_hash` */
CREATE TRIGGER md5Email BEFORE INSERT ON `user` 
FOR EACH ROW
BEGIN
    SET NEW.`email_hash` = MD5( NEW.`email` );
END;//

delimiter ;

/* Insere o valor */
INSERT INTO `user` (`email`) VALUES ("[email protected]");