0

I create a table like this:

create table table1 (

    field1 text not null,
    field2 text not null, 
    field3 text not null,
    ...
    id binary primary key not null
);

If i add a row to this table, i want to automatically create a MD5 Hash out of the values of field1 and field2. This created MD5 should be used as primary key inside of this row as an ID.

How do i do this?

Officer Bacon
  • 724
  • 1
  • 7
  • 22
  • 1
    check this out: http://stackoverflow.com/questions/4236912/how-to-create-calculated-field-in-mysql – rasso Jan 13 '15 at 13:49
  • 3
    Also it is not a good idea to have an hash algorithm value as PK because duplicates is possible, although low probability it can happen. Why not stick just with a generated id like auto_increment? – Jorge Campos Jan 13 '15 at 13:52
  • Duplicate PKs are possible, but i dont think they appear in a table with about 10000 entries (in my case). i need to do this, because i have to compare the first two values of 2 different tables. they do match a few times. Since text compare takes very long, i wanted to try this with hashes. – Officer Bacon Jan 13 '15 at 14:18

1 Answers1

2

Create an INSERT TRIGGER:

DELIMITER $$

CREATE TRIGGER generate_md5  
BEFORE INSERT ON table1
FOR EACH ROW  
BEGIN  
  NEW.id = MD5(CONCAT(NEW.field1, NEW.field2))
END $$

DELIMITER ;
user4035
  • 22,508
  • 11
  • 59
  • 94