问题描述
我只想通过干净的mySQL生成唯一的随机整数(从10000到99999);有什么想法吗?
I want to generate a unique random integer (from 10000 to 99999) identity just by clean mySQL; any ideas?
我不想通过循环在php中生成此数字(生成数字->在数据库中检查它),因为我想在mySQL查询中使用一些智能解决方案.
I don't want to generate this number in php by cycling (generate number -> check it in database) because I want to use some intelligent solution in a mySQL query.
推荐答案
虽然看起来有些尴尬,但这是可以实现的目标:
While it seems somewhat awkward, this is what can be done to achieve the goal:
SELECT FLOOR(10000 + RAND() * 89999) AS random_number
FROM table
WHERE random_number NOT IN (SELECT unique_id FROM table)
LIMIT 1
简单地说,它会生成N个随机数,其中N是表行的计数,过滤掉表中已经存在的那些行,并将其余的行限制为一个.
Simply put, it generates N random numbers, where N is the count of table rows, filters out those already present in the table, and limits the remaining set to one.
在大桌子上可能有点慢.为了加快处理速度,您可以根据这些唯一的ID创建一个视图,然后使用它代替嵌套的select语句.
It could be somewhat slow on large tables. To speed things up, you could create a view from these unique ids, and use it instead of nested select statement.
删除引号
这篇关于mysql唯一编号生成的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!