Generate random string in MySql stored function

Question

I need generate a random string in MySql stored function. I solved this problem, but my solution seems too ugly and too slow:

CREATE FUNCTION generate_salt() RETURNS varchar(11) CHARSET utf8
BEGIN
    DECLARE res varchar(11) DEFAULT '';
    DECLARE salt_sym varchar(2);
    DECLARE tmp_res varchar(20) DEFAULT'';
    DECLARE salt_len int DEFAULT 10;

    WHILE salt_len > 0 DO
      SET salt_sym = HEX(FLOOR(32 + RAND() * 96));
      SET tmp_res = CONCAT(tmp_res, salt_sym), salt_len = salt_len - 1;
    END WHILE;
    set res=UNHEX(tmp_res);
    RETURN res;
END

This code converts the random integers from a diapason [32, 126] to hexadecimal string and then converts the hexadecimal string to ascii-string using unhex() function.

Is there any way to convert a digit to ASCII character ( 0x30 => '0', 0x31=>'1', 0x32=> '2', etc.) without double (byte => hex 2-literal string => char) conversion?

slaakso · Accepted Answer · 2015-11-27 11:21:58Z

1

Why not just use:

return sha2(rand(), 256)

If you want more randomness, just concat multiple rand() calls.

answered Nov 27, 2015 at 11:21

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Verjans · Accepted Answer · 2016-12-17 08:53:56Z

0

For generating random string, you can use CHAR() (to cast the integer to a character) and MOD() functions:

CHAR(65+MOD(ROUND(RAND()*100),26))

edited Dec 17, 2016 at 8:53

Martin Verjans

4,8361 gold badge26 silver badges49 bronze badges

answered Nov 27, 2015 at 11:13

Vlad

264 bronze badges

Collectives™ on Stack Overflow

Generate random string in MySql stored function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related