Create and store existing MD5 hash after INSERT

Question

Create and store existing MD5 hash after INSERT

Navigation

#1 by (2 votes)

0

I have an X table, with a field called email and another email_hash, I would like every time a new record with email was inserted, MySQL would use the value of the inserted email and create an MD5 hash and insert into email_hash, as Can I do this? triggers?

mysql

asked by anonymous 29.04.2018 / 06:30

1 answer

Error accessing RDS bank with Heidsql Doubt about Messagebox

score 2 · Accepted Answer

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

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

With this you can use trigger_time BEFORE with 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]");