MiloŇ° MiloŇ° - 18 days ago 5
MySQL Question

MySQL Trigger - create random hashkey on insert

I am trying to create a trigger on MYSQL insert that will generate a random 32 chars hashkey if the inserted field has null hashkey.

I tried it in the following way:

CREATE TRIGGER `external_contacts_hash_auto_create` BEFORE INSERT ON `external_contacts`

FOR EACH ROW BEGIN SET NEW.hash_key =

concat(
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('abcdefghijklmnopqrstuvwxyz0123456789', rand(@seed)*36+1, 1)

) WHERE OLD.hash_key = null;
END


But the generated hashkey is always the same, nothing is random.

Answer

Your whole trigger is full of errors. You never initialize or assign a value to @lid or @seed.

What is the purpose of the number 4294967296? It makes absolutely no sense to me.

A statement SET whatever WHERE whatever; is syntactically wrong. There's no WHERE clause here.

When you compare with NULL it's either IS NULL or IS NOT NULL.

In a BEFORE INSERT trigger there is no OLD row.

And don't get me started about your coding style repeating the same statement 32 times.

It seems you just want to have a random string. You can use something like this instead:

CREATE TABLE ins_hash (id int auto_increment primary key, hash_key char(32));

DELIMITER $$
CREATE TRIGGER trg_ins_hash BEFORE INSERT ON ins_hash
FOR EACH ROW 
BEGIN

    SET @string := 'abcdefghijklmnopqrstuvwxyz0123456789';
    SET @i := 1;
    SET @hash := '';

    WHILE (@i <= 32) DO
        SET @hash := CONCAT(@hash, SUBSTRING(@string, FLOOR(RAND() * 36 + 1), 1));
        SET @i := @i + 1;
    END WHILE;

    SET NEW.hash_key := @hash;

END$$
DELIMITER ;

INSERT INTO ins_hash (id) VALUES (NULL);
INSERT INTO ins_hash (id) VALUES (NULL), (NULL);

SELECT * FROM ins_hash;
+----+----------------------------------+
| id | hash_key                         |
+----+----------------------------------+
|  1 | tl84mhbtuhng50di5y132sitcyg54wt6 |
|  2 | 3otr42mqfq42q8m7vgec8u9ihk6woer8 |
|  3 | ph0dkgbv4r42q47cv18vbpa4efldw2de |
+----+----------------------------------+
3 rows in set (0.00 sec)
Comments