比MySQL中的UUID更短的不重复的字母数字代码 [英] A shorter non-repeating alphanumeric code than UUID in MySQL

问题描述

当我插入记录时，MySQL数据库是否可能生成只包含数字和字母的5或6位数代码？如果是这样呢？

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?

就像goo.gl，bit.ly和jsfiddle这样做。对于exaple：

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

• http：// bit .ly / 3PKQcJ


• http：// jsfiddle.net/ XzKvP

• http://bit.ly/3PKQcJ
• http://jsfiddle.net/XzKvP

cZ6ahF，3t5mM，xGNPN，xswUdS。

所以 UUID_SHORT（）将无法工作，因为它返回一个值 23043966240817183

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

1. 必须是唯一的（不重复）


2. 可以但不要求以主键整数值为基础


3. 必须缩放（在使用所有可能的组合时会增加一个字符）


4. 必须 （ 1234 不能为 BCDE ，而 1235



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.

推荐答案

我建议使用Redis完成这个任务，其实。它具有使该任务适合其使用的所有特征。

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.

我们将创建两个列表， buffered_ids ， used_ids 。 cronjob将每5分钟（或任何你喜欢的间隔）运行，它将检查 buffered_ids 的长度，并保持它的长度，例如5000。当您需要使用ID时，请从 buffered_ids 中弹出，并将其添加到 used_ids 。

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具有集，它们是集合中的唯一项。

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".

您的cronjob，在bash中：

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

b $ b

获取下一个用于您的应用程序的uid（伪代码中，因为您没有指定语言）

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

编辑要安全地弹出一个值，请先将其添加到used_ids，然后将其从buffered_ids中删除。

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

这篇关于比MySQL中的UUID更短的不重复的字母数字代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！