0

I'm trying to get a unique code number which doesn't exists in the "codes" table using a query I found in several other SO threads:

https://stackoverflow.com/a/25435263/471573 https://stackoverflow.com/a/11680058/471573

I'm currently trying to use this code:

CREATE TABLE codes (
  code int NOT NULL
);

INSERT INTO `codes` (`code`) VALUES 
(1),(2),(3),(4),(5); 

 SELECT FLOOR(RAND() * 10) AS `rand_code` 
   FROM `codes` 
  WHERE "rand_code" NOT IN (SELECT `code` FROM `codes`) 
  LIMIT 1

sqlfiddle:

http://sqlfiddle.com/#!9/5e807/2

With lots of issues:

  1. if no codes are in the table, I get no results at all.
  2. if there ARE any codes, the results is not unique. I can get a result already in the table...

Any idea on how to make this work?

Thanks!

Community
  • 1
  • 1
trueicecold
  • 820
  • 10
  • 23
  • One option, assuming you plan to do something like this in the real world, would be to use a UUID which is very unlikely to ever be generated again. Then handle that unlikely edge case by just generating another UUID. Make the UUID column in your database table unique. – Tim Biegeleisen Apr 30 '17 at 11:53
  • Edit your question and put sample data and desired results in as text tables. – Gordon Linoff Apr 30 '17 at 12:37
  • 1
    Perhaps you're approaching the problem from the wrong end. – Strawberry Apr 30 '17 at 12:37

1 Answers1

0

rand() is evaluated everywhere it appears in the query. To do what you want, you can repeatedly run a query like this:

SELECT r.rand_code
FROM (SELECT FLOOR(RAND() * 10) as rand_code) r
WHERE NOT EXISTS (SELECT 1 FROM codes c WHERE c.code = r.rand_code);

This may not return a row. Then you need to re-run it.

Or, because you have such a limited number of codes, use a derived table to generate all of them, then randomly choose among the ones that don't exist:

select r.rand_code
from (select 0 as rand_code union all select 1 . . .
      select 9
     ) r left join
     codes c
     on c.code = r.rand_ocde
where c.code is null
order by rand()
limit 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks @Gordon. However, I was hoping to avoid re-running queries, otherwise I'd just pick a random number in nodejs or php and test it against the DB, and re-run if it's already there etc... Also, the codes are not limited.This was just a sample data. The real table could have lots and lots of codes (by the thousands), so that means the more codes it has, re-running the query will be much heavier on the server (running 10,20,100 queries etc...) – trueicecold May 01 '17 at 09:20