title: Generate unique and random code digit with MySQL
body: <p>Initial goal:</p>
  
  <p>I would like to generate random and unique codes (6 digits) in a table.
  I use a SQL query like this one to do that:</p>
  
  <pre><code>SELECT SUBSTRING(CRC32(RAND()), 1, 6) as myCode
  FROM `codes`
  HAVING myCode NOT IN (SELECT code FROM `codes`)
  </code></pre>
  
  <p>I asked me about how it will react when there will be no more available codes so I do the following test</p>
  
  <hr>
  
  <p>Test context:</p>
  
  <p>MySQL version: 5.5.20</p>
  
  <p>MySQL Table:</p>
  
  <pre><code>CREATE TABLE `codes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `code` VARCHAR( 10 ) NOT NULL ,
  UNIQUE (
  `code`
  )
  ) ENGINE = InnoDB;
  </code></pre>
  
  <p>Initial data:</p>
  
  <pre><code>INSERT INTO `codes` (`id`, `code`)
  VALUES (NULL, '1'), (NULL, '2'), (NULL, '3'), (NULL, '4'), (NULL, '5'), (NULL, '6'), (NULL, '7'), (NULL, '8');
  </code></pre>
  
  <p>SQL Query:</p>
  
  <pre><code>SELECT SUBSTRING(CRC32(RAND()), 1, 1) as myCode
  FROM `codes`
  HAVING myCode NOT IN (SELECT code FROM `codes`)
  </code></pre>
  
  <hr>
  
  <p>By execute this query, I expect that it will always return 9 because it is the only code of one digit which does not exists.</p>
  
  <p>But the result is:</p>
  
  <ul>
  <li>Sometime it return any rows </li>
  <li><strong>Sometime it return rows with values that already exists</strong></li>
  </ul>
  
  <p>I don't understand this behavior so if someone can help :)</p>
  
  <p>So the big question is:</p>
  
  <blockquote>
    <p>How MySQL can return rows with values that already exists?</p>
  </blockquote>
  
  <p>Thanks</p>