Generate value not in mysql table

Question

I have used following query that use to generate random value not in mysql table.

SELECT FLOOR( RAND()*1000 + 50 ) AS temp FROM tempcode WHERE  'temp' NOT IN ( SELECT code FROM tempcode ) LIMIT 1

but when I reduce the value to RAND()*4 for checking purpose using dummy data that not functioning well.

SELECT FLOOR( RAND()*1000 + 50 ) AS temp FROM tempcode WHERE  'temp' NOT IN ( SELECT code FROM tempcode ) LIMIT 1

What would be the reason? Any suggestions to generate value not in mysql table?

Because you're only generating numbers from 0 to 3 - not much randomness and a lot of collisions. Perhaps `UUID()` will do what you need. — , Mar 24 '15 at 04:30
@HoboSapiens How to use UUID() in mysql? If all the data generated value by random if it is already in the table it should return null?? — manitaz, Mar 24 '15 at 04:36
@phpPhil WHat do you mean by "Try wrapping temp with back ticks". Can you send me sample code? — manitaz, Mar 24 '15 at 04:37
`SELECT UUID()` will do. By definition a UUID should be unique. — , Mar 24 '15 at 04:46
Nevermind - I deleted the comment, because it was not correct and does not fix your issue. This was based on `'temp'` not looking like correct syntax, but [according to this](http://stackoverflow.com/questions/11680025/how-to-generate-random-number-without-repeat-in-database-using-php), it is. Sorry about the confusion. — phpPhil, Mar 24 '15 at 04:48
I need to generate integer value. That return 09728031-d1e1-11e4-bf6e-101f74b278c3 Any help? — manitaz, Mar 24 '15 at 04:50
@phpPhil The above code is not working I think. have checked it with populated data. — manitaz, Mar 24 '15 at 04:53

score 1 · Answer 1 · answered Apr 29 '15 at 13:34

This is not elegant, but it works as requested until all 1000 codes are used:

select x from  
    (select concat(hundreds, tens, ones) x from 
    (select 1 as ones union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) ones, 
    (select 1 as tens union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) tens, 
    (select 1 as hundreds union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) hundreds) thousand
    where thousand.x not in 
        (select code from tempcode) 
    order by rand() limit 1;

Generate value not in mysql table

1 Answers1