Generate a random string in MySQL

Question

I'm trying to get a random string in phpmyadmin using a function. I have the following code:

CREATE FUNCTION randomPassword()
RETURNS varchar(128)
BEGIN

    SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    SET @charLen = length(@chars);

    SET @randomPassword = '';

    WHILE length(@randomPassword) < 12
        SET @randomPassword = concat(@randomPassword, substring(@chars,CEILING(RAND() * @charLen),1));
    END WHILE;

    RETURN @randomPassword ;
END;

Now I get the error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Does anyone know how I can fix this?

That specific message typically refers to missing parentheses, so that would be my guess. — Strawberry
– Strawberry, Commented Mar 10, 2015 at 11:19
END WHILE is wrong. You need only END at the end of a WHILE loop — Galma88
– Galma88, Commented Mar 10, 2015 at 11:20
It's my first function writing for mysql, so i have no idea what parentheses i'm missing? — WillemKoonings
– WillemKoonings, Commented Mar 10, 2015 at 11:21
If I was doing my first function, I'd start with something simpler!! — Strawberry
– Strawberry, Commented Mar 10, 2015 at 11:22
According to dev.mysql.com/doc/refman/5.0/en/while.html , mysql uses END WHILE — WillemKoonings
– WillemKoonings, Commented Mar 10, 2015 at 11:23

Kim · Accepted Answer · 2019-07-05 03:49:05Z

26

This is faster than concat + substring routine.

select substring(MD5(RAND()),1,20);

As I've tested inserting 1M random data, md5 routine consumes only 1/4 (even less) time of concat + substring routine;

The problem is a md5 string contains only 32 chars so if you need a longer one you'd have to manually generate more md5 strings and substring it yourself.

edited Jul 5, 2019 at 3:49

answered Jul 5, 2019 at 3:43

Kim

5,5157 gold badges42 silver badges65 bronze badges

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

Comments

Galma88 · Accepted Answer · 2015-03-10 11:53:00Z

11

Try this more simple solution:

SELECT CONV(FLOOR(RAND() * 99999999999999), 10, 36)

answered Mar 10, 2015 at 11:53

Galma88

2,5566 gold badges31 silver badges50 bronze badges

1 Comment

pedorro Over a year ago

brilliant solution!

Dharman · Accepted Answer · 2019-08-28 12:06:22Z

3

SELECT SUBSTRING(REPLACE(REPLACE(REPLACE( TO_BASE64(MD5(RAND())), '=',''),'+',''),'/',''), 2, 40)

This solution to generate a fixed length random string that contains all lower- and upper-case chars and digits.

SELECT SUBSTRING(REPLACE(REPLACE(REPLACE( TO_BASE64(MD5(RAND())), '=',''),'+',''),'/',''), 2, FLOOR(10+RAND()*31))

If you need a random length string (from 10 to 40 symbols in this example)

edited Aug 28, 2019 at 12:06

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Aug 28, 2019 at 12:04

Vladimir Borovsky

311 bronze badge

Comments

WillemKoonings · Accepted Answer · 2015-03-10 11:57:59Z

2

It's solved by using the Delimiter, i don't know for sure how, but it works Thanks

DELIMITER $$
CREATE FUNCTION randomPassword()
RETURNS varchar(128)
BEGIN

SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @charLen = length(@chars);

SET @randomPassword = '';

WHILE length(@randomPassword) < 12
    DO
    SET @randomPassword = concat(@randomPassword, substring(@chars,CEILING(RAND() * @charLen),1));
END WHILE;

RETURN @randomPassword ;
END$$
DELIMITER ;

answered Mar 10, 2015 at 11:57

WillemKoonings

2652 gold badges4 silver badges13 bronze badges

Comments

Galma88 · Accepted Answer · 2015-03-10 11:26:11Z

0

CREATE FUNCTION randomPassword()
RETURNS varchar(128)
AS
BEGIN

declare @chars nvarchar(25);
declare @charlen int;
declare @randomPassword nvarchar(128);

SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @charLen = len(@chars);

SET @randomPassword = '';

WHILE(LEN(@randomPassword) < 12)
BEGIN
    SET @randomPassword = concat(@randomPassword, substring(@chars,CEILING(RAND() * @charLen),1));
END

RETURN @randomPassword
END

answered Mar 10, 2015 at 11:26

Galma88

2,5566 gold badges31 silver badges50 bronze badges

5 Comments

WillemKoonings Over a year ago

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS BEGIN declare @chars nvarchar(25)' at line 3

Galma88 Over a year ago

Try to remove ..AS.. Maybe in MySql it works in this way

WillemKoonings Over a year ago

already did: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@chars nvarchar(25)' at line 5

Galma88 Over a year ago

substitute NVARCHAR to VARCHAR

WillemKoonings Over a year ago

oh yeah, should have seen that. but still gives the same error though

Collectives™ on Stack Overflow

Generate a random string in MySQL

5 Answers 5

Comments

1 Comment

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related