1

I wish to let database column to auto generate around 300++ random alphanumeric string every time I insert a new entry to the database. The code below only capable of size of 36.

DELIMITER ;;
CREATE TRIGGER testdata
BEFORE INSERT ON testdata
FOR EACH ROW
BEGIN
  IF new.token IS NULL THEN
    SET new.token = uuid();
  END IF;
END
;;

Thanks for everyone that answer this question.

Yih Wei
  • 537
  • 3
  • 21
  • What's the purpose of this string? There's techniques [like this](http://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql) to generate alphanumeric sequences, but if you need something cryptographically unique and unguessable, `RAND()` is too predictable for that. – tadman Apr 19 '17 at 05:03
  • Hi. @tadman Thanks for your reply. The purpose of this string is like some kind of authentication. Mind suggest any better method for me to apply on which is crytographically unique? – Yih Wei Apr 19 '17 at 05:06
  • UUID-type values are only really intended to be unique, not unpredictable. For something cryptographically unique you'll need a secure random number generator and then encode it in base-62 or something of that sort. This may not be the best thing for MySQL to be handling, but you can try. It's a lot easier to do in an external programming language. – tadman Apr 19 '17 at 05:11

1 Answers1

1

One option would be to just chain together the output of multiple calls to uuid(), i.e.

DELIMITER ;;
CREATE TRIGGER testdata
BEFORE INSERT ON testdata
FOR EACH ROW
BEGIN
    IF new.token IS NULL THEN
        SET new.token = CONCAT(REPLACE(uuid(), '-', ''), REPLACE(uuid(), '-', ''), ...)
    END IF;
END
;;

Note here that I remove the hyphen from the UUIDs because they are superfluous and do not do much in the way of making the strings more random.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360