CODEWITHSUNDEEP
mysqldatabaserandom
Prabhjot Singh Rai
Prabhjot Singh Rai

Reputation: 2545

Inserting a unique random alphanumeric number in mysql

I want to generate a unique random alphanumeric number in mysql so that every entry in the database is retrieved by that number and moreover it wont be a sequence which the client can recognise. Please let me know how can I generate such a number.

Upvotes: 1

Views: 3118

Answers (2)

El Gucs
El Gucs

Reputation: 967

You can't generate an unique random number. Over time, the randomness will generate a number already stored. You need to make a "quasi-random" number, meaning that it's a number based on another data, but it just looks random. You can use the primary key on the table as the base number to generate the "fake-random" number

INSERT INTO myTable(primaryKey,quasiRandom) 
SELECT IFNULL(MAX(primaryKey),0)+1, CAST(CONCAT(IFNULL(MAX(primaryKey),0)+1,CHAR(FLOOR(RAND()*26)+65),FLOOR(100+RAND()*(500-100))) AS CHAR(50)) AS random_alpha_numeric FROM myTable;

Upvotes: 0

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

Below sql give you random alpha numeric number.

     SELECT CAST(CONCAT( CHAR(FLOOR(RAND()*26)+65),FLOOR(100+RAND()*(500-100)))
 AS CHAR(50))AS random_alpha_numeric

you can generate range of random number like these.

syntax: FLOOR(min_value+RAND()*(max_value-min_value))

You can insert random alpha numeric number in to table like below.

INSERT INTO `table_name` (column_name ) VALUES( CONCAT( 
CHAR(FLOOR(RAND()*26)+65),FLOOR(100+RAND()*(500-100))))

Thank you.

Upvotes: 1

Related Questions