MySQL 如何使用PHP在数据库中生成不重复的随机数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11680025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Generate Random number without repeat in database using PHP?
提问by aslamdoctor
I would like to generate a 5 digit number which do not repeat inside the database. Say I have a table named numbers_mstwith field named my_number.
我想生成一个在数据库中不重复的 5 位数字。说我有一个表命名numbers_mst与现场命名my_number。
I want to generate the number the way that it do not repeat in this my_number field. And preceding zeros are allowed in this. So numbers like 00001 are allowed. Another thing is it should be between 00001 to 99999. How can I do that?
我想以在 my_number 字段中不重复的方式生成数字。并且在此允许前面的零。所以像 00001 这样的数字是允许的。另一件事是它应该在 00001 到 99999 之间。我该怎么做？
One thing I can guess here is I may have to create a recursive function to check number into table and generate.
我在这里可以猜到的一件事是我可能必须创建一个递归函数来将数字签入表中并生成。
回答by Tushar
SELECT FLOOR(RAND() * 99999) AS random_num
FROM numbers_mst
WHERE "random_num" NOT IN (SELECT my_number FROM numbers_mst)
LIMIT 1
What this does:
这是做什么的：
- Selects random number between 0 - 1 using RAND().
- Amplifies that to be a number between 0 - 99999.
- Only chooses those that do not already exist in table.
- Returns only 1 result.
- 使用 RAND() 选择 0 - 1 之间的随机数。
- 将其放大为 0 - 99999 之间的数字。
- 只选择表中不存在的那些。
- 仅返回 1 个结果。
回答by Stijn Van Bael
In addition to Tushar's answer to make it work when numbers_mst is empty:
除了 Tushar 在 numbers_mst 为空时使其工作的答案之外：
SELECT random_num
FROM (
SELECT FLOOR(RAND() * 99999) AS random_num
UNION
SELECT FLOOR(RAND() * 99999) AS random_num
) AS numbers_mst_plus_1
WHERE `random_num` NOT IN (SELECT my_number FROM numbers_mst)
LIMIT 1
回答by Ben Guild
NOTE:The other solutions posted will work onlyif the column is configured as NOT NULL. If NULL, it'll just return no results. You can fix the query like this:
注意：发布的其他解决方案仅在列配置为NOT NULL. 如果NULL，它只会返回任何结果。您可以像这样修复查询：
SELECT random_num
FROM (
SELECT FLOOR(RAND() * 99999) AS random_num
) AS numbers_mst_plus_1
WHERE random_num NOT IN (SELECT my_number FROM numbers_mst WHERE my_number IS NOT NULL)
LIMIT 1
... The ...WHERE my_number IS NOT NULLis necessary!
……...WHERE my_number IS NOT NULL有必要！
EDIT:I just wanted to mention that I intentionally removed the inner SELECT's table name because it didn't seme necessary and seemed to break if there was no data in the table yet? However, maybe this was intentionally included? — Please clarify or comment for everyone, thanks.
编辑：我只想提一下，我故意删除了 innerSELECT的表名，因为它似乎没有必要并且如果表中没有数据似乎会中断？但是，也许这是故意包含的？——请大家澄清或评论，谢谢。
回答by David Schwartz
Generate random number.
Check if random number is in database.
If not, stop, use this number.
Go to step 1.
生成随机数。
检查随机数是否在数据库中。
如果没有，停止，使用这个号码。
转到步骤 1。
回答by Roni
This is easiest method to build unique code generator without check database, it will save database query execution time.
这是无需检查数据库即可构建唯一代码生成器的最简单方法，它将节省数据库查询执行时间。
function unique_code_generator($prefix='',$post_fix='')
{
$t=time();
return ( rand(000,111).$prefix.$t.$post_fix);
}
Enjoy, have a nice coding day..
享受，有一个美好的编码日..
:)
:)
回答by Robbie
You have two approaches:
你有两种方法：
The first, suggested in other answer, is to create a random number (using mt_rand() ) and check it's not in the database. If it is in the database, regnerate and try again. This is simplest if you are generating a single number - see other answers for code. But if you are gnerating more that 50% of the numbers it will be very slow and inefficient.
第一个，在其他答案中建议，是创建一个随机数（使用 mt_rand() ）并检查它不在数据库中。如果它在数据库中，请重新生成并重试。如果您生成单个数字，这是最简单的 - 请参阅代码的其他答案。但是，如果您生成超过 50% 的数字，它将非常缓慢且效率低下。
If ou want a lot of numbers, the alternative is to populate a database with all the records and have a column "picked". Run a query to find how many are "not picked" and then find a random number between 0 and the number "not picked". Then run the SQL query to get the number in that position (where not picked, use LIMIT in mysql) and mark as picked. A bit convoluted, it's more work and less efficient if you only want a few numbers, but will be much better when you want to get more that 50% (estimate) of the numbers.
如果您想要很多数字，另一种方法是用所有记录填充数据库并“选择”一列。运行查询以查找“未选择”的数量，然后在 0 和“未选择”之间找到一个随机数。然后运行 SQL 查询以获取该位置的数字（未选择的位置，在 mysql 中使用 LIMIT）并将其标记为已选择。有点令人费解，如果您只想要几个数字，则工作量更大，效率更低，但是当您想要获得超过 50%（估计）的数字时会好得多。
Note: you can make it more efficient by storing the count selected locally and running a few less queries.
注意：您可以通过在本地存储选择的计数并减少运行一些查询来提高效率。
回答by Jafar Pager Jaya
I know my answer is late, but if anyone is looking for this subject in the future, who would not have a random number with leading zero, you should add the LPAD()function.
我知道我的回答晚了，但是如果将来有人在寻找这个主题，并且没有带前导零的随机数，您应该添加该LPAD()函数。
So your query it will like
所以你的查询会喜欢
SELECT LPAD(FLOOR(RAND()*99999),5,0)
Have a nice day.
祝你今天过得愉快。
回答by Jerem
The folowing query generates all int from 0 to 99,999, find values which are not used in the target table and output one of these free number randomly :
以下查询生成从 0 到 99,999 的所有 int，查找目标表中未使用的值并随机输出这些空闲数之一：
SELECT random_num FROM (
select a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.a) as random_num
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as e
) q
WHERE random_num NOT IN(SELECT my_number FROM numbers_mst)
ORDER BY RAND() LIMIT 1
Ok, it is long, slow and not scalable but it works as a standalone query! You can add a remove more "0" (Joins a, b, c, d, e) to increase or reduce the range.
好的，它很长、很慢且不可扩展，但它作为一个独立的查询工作！您可以添加一个删除更多的“0”（连接 a、b、c、d、e）以增加或减少范围。
You can also use this kind of rows generator technique to create rows with all dates for example.
例如，您还可以使用这种行生成器技术来创建包含所有日期的行。
回答by Hariramprasath Nandhagopalan
DELIMITER $$
USE `temp` $$
DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$
CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255))
BEGIN
DECLARE uniqueValue VARCHAR(8) DEFAULT "";
DECLARE newUniqueValue VARCHAR(8) DEFAULT "";
WHILE LENGTH(uniqueValue) = 0 DO
SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1)
) INTO @newUniqueValue;
SET @rcount = -1;
SET @query=CONCAT('SELECT COUNT(*) INTO @rcount FROM ',tableName,' WHERE ',columnName,' like ''',newUniqueValue,'''');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @rcount = 0 THEN
SET uniqueValue = @newUniqueValue ;
END IF ;
END WHILE ;
SELECT uniqueValue;
END$$
DELIMITER ;
Use this stored procedure and you can use this with dynamic values like
使用此存储过程，您可以将其与动态值一起使用，例如
call GenerateUniqueValue('tablename', 'columnName')
回答by Arju S Moon
We can simply do with this:
我们可以简单地这样做：
$regenerateNumber = true;
do {
$regNum = rand(2200000, 2299999);
$checkRegNum = "SELECT * FROM teachers WHERE teacherRegNum = '$regNum'";
$result = mysqli_query($connection, $checkRegNum);
if (mysqli_num_rows($result) == 0) {
$regenerateNumber = false;
}
} while ($regenerateNumber);
$regNumwill have the value which is not present in the database
$regNum将具有数据库中不存在的值
