0

Trying to generate a unique code to prevent duplicates i use the following query using HAVING clause so i can use the alias but i get duplicate key errors:

SELECT
  FLOOR(100 + RAND() * 899) AS random_code 
FROM product_codes 
HAVING random_code NOT IN (values) 
LIMIT 1

The following code did not work and is what i need:

https://stackoverflow.com/a/4382586

Is there a better way to accomplish this or there is something wrong in my query?

Community
  • 1
  • 1
  • 1
    What is in your `values`? – Michael Berkowski Jan 24 '13 at 02:07
  • I generate the values with the following query: `SELECT codes FROM product_codes` just before i call the HAVING query and using PHP implode function i get the following result (0, 258, 365, 789, 563, 693) –  Jan 24 '13 at 02:13
  • Why mention `product_codes` in the original query? Nothing from that table is used! – Rick James May 07 '17 at 03:16

2 Answers2

2

If you want a unique code that is guaranteed to be unique, use the mySQL function UUID()

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

"A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other."

If a UUID is too long (e.g. it has to be exactly a certain number of digits), then hash the UUID (with md5 or sha-256 for example), take a certain number of bits and turn that into a decimal integer. The hashing is important since it's the whole UUID that guarantees uniqueness, not any one part of it. However, you will now be able to get hash collisions, which will be likely as soon as you have more than sqrt(2^bits) entries. E.g. if you use 10 bits for 0-1023, then after about 32 entries a hash collision becomes likely. If you use this few bits, consider an incrementing sequence instead.

Patashu
  • 21,443
  • 3
  • 45
  • 53
  • I do really know how to get unique ids, but my goal was to use a single mysql query to get a code left between 0 and 999, so a three digit number not already in the table, but thanks for trying. –  Jan 24 '13 at 08:37
  • 1
    I would be incredibly surprised if there's a solution for this that doesn't require generating values and checking the table for them until you get one that is not in it. It seems very non-SQL-y. – Patashu Jan 24 '13 at 08:40
  • Me too, i'd be incrediby surprised if it could be done in one query using MYSQL, but since i found that Q answered i thought it might be possible but as you say it's not. Thanks anyway. –  Jan 24 '13 at 11:37
0

Wanted to use a MYSQL query to get random numbers left between 0-999 as a code but tryed that query and also i ended filling the values condition from 0 to 999 and still got always duplicate codes, strange behaviour so i ended up using PHP.

The steps i use now:

Create an array populated with 0 to 999, in the future if i need more codes will use 0 to 9999.

$ary_1 = array(0, 1, 2, ...., 999)

Create other array populated with all codes in the table.

$ary_2 = array(4, 5, 985, 963, 589)

Get a resulting array using array_diff.

$ary_3 = array_diff($ary_1, $ary_2)

Get an array key using array_rand from the resulting array_diff.

$new_code_key = array_rand($ary_3, 1)

Use that key to get the code and create the MYSQL query.

$ary_3[$new_code_key]

Unset that key from the new codes array so i speed up the process and just have to get anoher array_rand key and unset later.

unset($ary_3[$new_code_key])