Best way for unique random string for MySQL Long table

Question

I know how to create random characters in both PHP and MySQL, but the question is, should I create a random 4 char row for a table with a size of 10 thousand rows. What is the best way to ensure that it remains unique?

I can use a longer string if I need to, but no longer than 12.

Just to make it simple, a table exists. I need to add an extra column and fill it with a random 4 char row, and the keys should remain unique.

Answer 1

Option:

.

val
------
0
1
...
9
a
b
...
z

SELECT CONCAT(a.val,b.val,c.val,d.val)
FROM chars AS a
JOIN chars AS b
JOIN chars AS c
JOIN chars AS d
ORDER BY RAND()
LIMIT 10000

---

, ID , .

A. .

, . .

. .

, , , . , , , , .

0-9a-z, , 36 ⁴. MB.

Answer 2

, , , .

, , , "base 62".

Answer 3

DISTINCT.

, , , 4 char :

mysql> SELECT DISTINCT random_strings FROM chars;

Answer 4

, , :

 CREATE FUNCTION gen_alphanum () RETURNS CHAR(4)
    RETURN 
    ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m  ','n','o','p','q','r','s','t','u','v','w','x','y', 'z',
    'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z',);
    )

Answer 5

, MySQL .

:

• MySQL. SQL . , NOT EXISTS(SELECT MyRandomString FROM MyTable) , , .

• MyRandomString.

• :

 UPDATE MyTable
 SET    MyRandomString = fn_CreateSomeRandomString()

• :

 INSERT INTO MyTable (foo, bar, MyRandomString) 
 VALUES ('','', fn_CreateSomeRandomString());

UDF PasteBin.

Answer 6

MySQL 5.6, TO_BASE64 :

select LEFT( TO_BASE64( SHA(rand()) ), 6 ) ;

, 5.6,

DELIMITER //

drop function if exists randChr //
create function randChr()
returns char
BEGIN
  IF RAND() <= 0.5 THEN -- Lowercase
    return CHAR( 97 + 25*rand() ) ;
  ELSE -- uc
    return CHAR( 65 + 25*rand() ) ;
  END IF;
END //

drop function if exists randString //
create function randString( len int )
returns varchar(255)
BEGIN
  SET @n = 0;
  SET @res = '' ;
  REPEAT
    SET @res = concat( @res, randChr() ) ;
    set @n = @n + 1 ;
  UNTIL @n >= len END REPEAT;
  return @res ;
END //

DELIMITER ;

-- USE:
select randString( 5 );
select randString( 60 );