Does MySQL support ability automatically to fill field using hash md5 or another?
For example, I want to use primary key of photos in table as hash, to avoid selection of photo by id.
Advertisement
Answer
As commented by @MitchWheat, mysql supports various encryption functions : MD5, SHA, SHA1 and SHA2, …
However, using an automatic hash as primary key is not a good practice.
First of all, an autoincremented integer will always perform better than a hash ; it is easier to insert, to access, to index and also requires less storage. One other thing is that, by using a hash, you are exposing yourself to a (small, but real) risk of collision.
Second, it is much harder to implement an automatic hash than an autoincremented integer. As functions cannot be used as column defaults, you will need to use a trigger, and probably a separate table for sequencing. See this SO answer for a demonstration of this set up.
An intermediate option would be to rely on a specific field, or a combination of fields, that you would automatically hash before inserts (and updates) using a trigger to generate a primary key.
Something like :
DELIMITER $$
CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
SET NEW.id = MD5(CONCAT(NEW.field_to_hash1, NEW.field_to_hash2));
END$$
DELIMITER ;