MySQL auto generate values

Question

I search a complex SQL code, I have one right now to generate a value random between 0 - 100000 when it does not exists.

Now I want the next level, I need to insert a new value when the value its random string, its start from 1 and I need to changes this (chart count) to like 32 when the string now will return a string on 32 chars.

The string much contain all chars type include spical charts.

The problem is I use this algoritme for discount-vouchers and a numbers have not soe many combinds like numbers include a-z chars have.

I hope somebody can help me on my searching.

Andrew · Accepted Answer · 2012-02-13 21:21:59Z

1

A function to generate a random string from a collection of characters of your choosing:

DELIMITER $$

CREATE FUNCTION random_str() RETURNS char(32)
BEGIN
  DECLARE chars varchar(500);
  DECLARE rs char(32);
  SET rs = '';
  SET chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*_';
  WHILE (LENGTH(rs) < 32) DO
    SET rs = CONCAT(rs, SUBSTRING(chars, RAND() * LENGTH(chars), 1));
  END WHILE;
  RETURN rs;
END

A unique index on your value field:

ALTER TABLE my_table 
ADD UNIQUE INDEX IX_str (str);

Then, I would just be sure to catch the once-in-a-lifetime exception in my client code, or you could do something like this to insert new values:

DECLARE c int;
DECLARE rs char(32);
SET c = 1;
WHILE (c > 0) DO
  SET rs = random_string();
  SELECT c = COUNT(*) FROM my_table WHERE str = rs;
END WHILE;
INSERT INTO my_table (str) VALUES (rs);

edited Feb 13, 2012 at 21:21

answered Feb 13, 2012 at 18:48

Andrew

4,62429 silver badges31 bronze badges

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

2 Comments

ParisNakitaKejser Over a year ago

And if the random str need to be unik from orther values in my MySQL?

Andrew Over a year ago

It sounded like you had that part figured out already. See edit.

davethecoder · Accepted Answer · 2012-02-13 16:28:27Z

0

why dont you still keep the random number, but insert through password or sha1 this would essentially take your RAND and encrypt the RAND to create a random string. you just dont need to decrypt it because the encrypted value of rand, is your random string.

the other way is to create a GUId on your code side of things

answered Feb 13, 2012 at 16:28

davethecoder

3,9324 gold badges38 silver badges66 bronze badges

2 Comments

theglauber Over a year ago

If your MySQL has the base64 functions, then perhaps creating a binary string from a random number and base64-encoding it might do what you need.

ParisNakitaKejser Over a year ago

Yes, but i dont want that way :) i want to generate it on thats way, i allredy use GUID and more of taht stuff, :)

Collectives™ on Stack Overflow

MySQL auto generate values

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related