2

Is it possible for MySQL database to generate a 5 or 6 digit code comprised of only numbers and letters when I insert a record? If so how?

Just like goo.gl, bit.ly and jsfiddle do it. For exaple:

cZ6ahF, 3t5mM, xGNPN, xswUdS...

So UUID_SHORT() will not work because it returns a value like 23043966240817183

Requirements:

  1. Must be unique (non-repeating)
  2. Can be but not required to be based off of primary key integer value
  3. Must scale (grow by one character when all possible combinations have been used)
  4. Must look random. (item 1234 cannot be BCDE while item 1235 be BCDF)
  5. Must be generated on insert.

Would greatly appreciate code examples.

capdragon
  • 14,565
  • 24
  • 107
  • 153
  • possible duplicate of [How do sites like goo.gl or jsfiddle generate their URL codes?](http://stackoverflow.com/questions/10299901/how-do-sites-like-goo-gl-or-jsfiddle-generate-their-url-codes) – 000 Jul 11 '13 at 16:05
  • Why the constraint "Must be generated on insert."? – 000 Jul 11 '13 at 17:22
  • @JoeFrambach To minimize the "back and forth" example. So I can do everything in ONE shot instead of: Insert, return primary key, generate code using primary key as seed, update transaction with generated code. – capdragon Jul 11 '13 at 18:29
  • I think you're asking for too much. – 000 Jul 11 '13 at 18:35
  • @JoeFrambach I saw another post, they guy was doing it. But the question was not exactly related to HOW he was doing it so he did not have the solution. But he did say it was a funtion he made that generated the code at the time the PK was generated. I appreciate your effort. Thanks. – capdragon Jul 11 '13 at 18:43
  • Can you post a link please? – 000 Jul 11 '13 at 18:46
  • @JoeFrambach The "duplicate" question is a C# solution. This is a mySQL solution. Please read carefully. – capdragon Jul 11 '13 at 18:52
  • Well good luck, I'm out. – 000 Jul 11 '13 at 18:55
  • @JoeFrambach http://stackoverflow.com/a/5886175/442580 – capdragon Jul 11 '13 at 19:08

2 Answers2

2

Try this: 

SELECT LEFT(UUID(), 6);
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • 1
    (+1) I like the simplicity though I'm concerned about the uniqueness of it. After a billion entries I'm bound to have some collisions. Plus it does not scale. Thanks for the effort though. – capdragon Jul 11 '13 at 15:19
  • What do you mean it *"does not scale"*? Replace `6` with `7` or `8` or `12`. That's the definition of "scale". – ypercubeᵀᴹ Jul 11 '13 at 15:22
  • @ypercube As I said `grow by one character when all possible combinations have been used`. This should not have to be done manually. It should scale on it's own without manual intervention. Also it is not unique. Eventually there will be a collision. – capdragon Jul 11 '13 at 15:27
1

I recommend using Redis for this task, actually. It has all the features that make this task suitable for its use. Foremost, it is very good at searching a big list for a value.

We will create two lists, buffered_ids, and used_ids. A cronjob will run every 5 minutes (or whatever interval you like), which will check the length of buffered_ids and keep it above, say, 5000 in length. When you need to use an id, pop it from buffered_ids and add it to used_ids.

Redis has sets, which are unique items in a collection. Think of it as a hash where the keys are unique and all the values are "true".

Your cronjob, in bash:

log(){ local x=$1 n=2 l=-1;if [ "$2" != "" ];then n=$x;x=$2;fi;while((x));do let l+=1 x/=n;done;echo $l; }
scale=`redis-cli SCARD used_ids`
scale=`log 16 $scale`
scale=$[ scale + 6]
while [ `redis-cli SCARD buffered_ids` -lt 5000 ]; do
    uuid=`cat /dev/urandom | tr -cd "[:alnum:]" | head -c ${1:-$scale}`
    if [ `redis-cli SISMEMBER used_ids $uuid` == 1]; then
        continue
    fi
    redis-cli SADD buffered_ids $uuid
done

To grab the next uid for use in your application (in pseudocode because you did not specify a language)

$uid = redis('SPOP buffered_ids');
redis('SADD used_ids ' . $uid);

edit actually there's a race condition there. To safely pop a value, add it to used_ids first, then remove it from buffered_ids.

$uid = redis('SRANDMEMBER buffered_ids');
redis('SADD used_ids ' . $uid);
redis('SREM buffered_ids ' . $uid);
000
  • 26,951
  • 10
  • 71
  • 101
  • I've edited so the scale automatically increases as more items are added! – 000 Jul 11 '13 at 15:46
  • Edited for random alphas, rather than uuid with outputs hyphens – 000 Jul 11 '13 at 15:56
  • Thanks Joe, but I'm looking for a MySQL solution/function based on a pseudo random permutation or something that will allow me to skip the needles seeking for duplicates. I might have considered this but using Redis is simply not an option for me in my current environment. – capdragon Jul 11 '13 at 18:32