mysql uuid 长度,比MySQL中的UUID更短的不重复的字母数字代码

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:

Must be unique (non-repeating)

Can be but not required to be based off of primary key integer value

Must scale (grow by one character when all possible combinations have been used)

Must look random. (item 1234 cannot be BCDE while item 1235 be BCDF)

Must be generated on insert.

Would greatly appreciate code examples.

解决方案

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);