mysql唯一编号生成 [英] mysql unique number generation

问题描述

我只想通过干净的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唯一编号生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！