title: Generating a Random AND Unique PIN using a MySQL Stored Function with WHILE loop
body: <p>I'm trying to write a stored function for MySQL to generate a random and unique PIN, which will then be inserted into the 'PIN' field in a database table. This has to be done by the DB as various systems may query the database and call this function.</p>
  
  <p>The random number is the easy bit; where I'm struggling is the process for checking that the generated PIN hasn't already been used and if so, generating new PINs until we get a fresh one. To achieve this, I'm trying to use a WHILE loop but it seems to be getting stuck whirring around because when I run the function MySQL just grinds to a halt and CPU shoots up until I stop/restart the service.</p>
  
  <p>This is my function at the moment:</p>
  
  <pre><code>DELIMITER @@
  CREATE FUNCTION generate_unique_pin() RETURNS int(11)
  BEGIN
  
  DECLARE pin INT;
  DECLARE myloop BOOL;
  SET myloop = 1;
  
  WHILE myloop = 1 DO
     SET pin = FLOOR( RAND( ) * 99999999 );
     IF (SELECT COUNT(*) AS used FROM mydb.mytable WHERE PIN = pin) = 0 THEN
        SET myloop = 0;
     END IF;
  END WHILE;
  
  RETURN pin;
  END@@
  DELIMITER ;
  </code></pre>
  
  <p>I've also tried it with something like</p>
  
  <pre><code>SET used = (SELECT COUNT(*) AS used FROM mydb.mytable WHERE PIN = pin)
  IF used = 0 THEN (etc...)
  </code></pre>
  
  <p>But I still get the same issue. The random number code works because if I strip the loop bit out the function works fine (just doesn't guarantee uniqueness) and the query also works fine if I run it on its own, returning a record set with "used" and 0 or 1.</p>
  
  <p>Any help or advice gratefully received!</p>