Generate and Insert the five digit random numbers using mysql [duplicate]

Question

I am wondering if there is any way to generate the random five digit number and insert into database using mysql. I know i can do it using PHP but wanted to know if i can get rid of the php and do it using database. Also, the generated number should be different than the numbers already stored in the database.

Following is example as how it should look like:

I have four letters of pattern common in random_no field which is org1 and want to append other 5 random letters as shown in following example:

+-------+-----------+-----------+--------------------------------------------+
|   id  |   title   |   phone        |         ABN       | Random No        |
+-------+-----------+----------+---------------------------------------------
|   1   |   title1    | 4765 5678   |   214-444-1234    |  org123456        |
|   2   |   title2    | 4444 4444   |   555-111-1234    |  org109876        |
|   3   |   title3    | 3333 3333   |   214-222-1234    |  org187654        |
|   4   |   title4    | 1111 1111   |   817-333-1234    |  org156432        |
|   5   |   title5    | 2222 2222   |   214-555-1234    |  org177654        |
+-------+-----------+-----------+--------------------------------------------

Any help will be appreciated.

The answer to this post might be relevant: stackoverflow.com/q/4382185/1144203 — ivan.sim
– ivan.sim, Commented Sep 10, 2014 at 0:19
Can you provide an example? For instance, how large is the table? — Gordon Linoff
– Gordon Linoff, Commented Sep 10, 2014 at 0:19
first thing look at THIS POST I wrote for how to ask a question appropriately — John Ruddell
– John Ruddell, Commented Sep 10, 2014 at 0:19
your five random numbers won't cut it if your rows gets large — Kevin
– Kevin, Commented Sep 10, 2014 at 0:22
Table has 5 fields including this field where i want to store random generated number. I have certain pattern of 4 letters which will be appended to random generated number as i can differentiate between certain fields. for instance: I have 4 letters common such as org1 and if i would generate other 5 numbers between (00001-99999) then it should look like: org122222. — RK.
– RK., Commented Sep 10, 2014 at 0:22

John Ruddell · Accepted Answer · 2014-09-10 00:55:46Z

0

Now there is no guarentee that there are not going to be duplicates... but this is getting two random numbers and multiplying them by different numbers so its not all that likely that they will be getting random numbers

UPDATE table t,
(   SELECT id, LPAD(FLOOR(7 + (RAND() * 50) * (RAND() * 333)), 5, 0) as join_num
    FROM table
)t1
SET t.random_no = CONCAT(t.random_no, t1.join_num)
WHERE t.id = t1.id;

From here I recommend you do this.. after updating your table go back through and run this query

SELECT id FROM table
GROUP BY random_no
HAVING COUNT(*) > 1;

if there are any results returned then the id's there will need a different random number and you can just change it at any duplicate spots once you know if there are any dupes

Breakdown of the update query....

update the table alias t.
select from table the id and then the random number alias t1.
concat the number and the column by row..
where the id's are equal... getting a different number for each row.

LPAD is a zero fill so that way if the number is smaller than 5 spaces it'll fill it in with 0's and then you have to use FLOOR() with RAND() for the random number.
Hope thats helpful!

edited Sep 10, 2014 at 0:55

answered Sep 10, 2014 at 0:48

John Ruddell

25.9k7 gold badges60 silver badges88 bronze badges

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

2 Comments

RK. Over a year ago

Thanks for your solution but this does not look feasible to me because i would use this scheme in 5 different tables and all of them will be updated frequently. Therefore, it is impossible for me to check every time the record inserted in the table and delete if any duplicates occur. I look for permanent solution. Alternatively, if i did not find any solution solely using mysql then i would go for php that would be quite easy but with more requests.This is the only reason why i want to go only for mysql to avoid frequent server requests.

John Ruddell Over a year ago

@RK. if you are doing this for 5 tables... and need to execute it multiple times then you should look into setting up triggers for when the tables are updated and then execute something like this query. SEE HERE

Collectives™ on Stack Overflow

Generate and Insert the five digit random numbers using mysql [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related