我想在不使用程序的情況下用唯一的亂數填充表。
我試過用這個回復來做，但沒有成功。
我正在嘗試做的是這樣的事情，但驗證數字不會重復：
INSERT into table (row1,row2)
WITH RECURSIVE
cte AS ( SELECT 0 num, LPAD(FLOOR(RAND() * 99999999),8,0) random_num
UNION ALL
SELECT num 1, LPAD(FLOOR(RAND() * 99999999),8,0) random_num
FROM cte WHERE num < 100000-1)
SELECT random_num, null
FROM cte;
在上面的示例中，我能夠生成隨機值并插入它們，但無需驗證數字是否重復。
我試圖這樣做：
INSERT into table (row1,row2)
WITH RECURSIVE
cte AS ( SELECT 0 num, LPAD(FLOOR(RAND() * 99999999),8,0) random_num
UNION ALL
SELECT num 1, LPAD(FLOOR(RAND() * 99999999),8,0) random_num
FROM cte WHERE num < 100000-1AND random_num NOT IN (SELECT random_num FROM cte WHERE random_num IS NOT NULL))
SELECT random_num, null
FROM cte;
但是AND random_num NOT IN (SELECT random_num FROM cte WHERE random_num IS NOT NULL)在 where 情況下的條件會導致SQL Error [4008] [HY000]: Restrictions imposed on recursive definitions are violated for table 'cte'
關于如何做到這一點的任何建議？謝謝你！。
uj5u.com熱心網友回復：
如果您有一個表 - 任何表 - 例如有 100 行，那么您可以生成 0 到 99999999 之間的百萬個不同的亂數，如下所示：
select distinct floor(rand() * 100000000)
from t as t0, t as t1, t as t2
limit 1000000
請注意，由于distinct，您將需要生成更多的行，以便在distinct 之后獲得所需的行數。
uj5u.com熱心網友回復：
這可能是一個選擇。生成所有可能的值，隨機排序并獲取所需數量的條目。
CREATE TABLE random_data (
row1 INT PRIMARY KEY AUTO_INCREMENT,
row2 VARCHAR(10) NOT NULL,
UNIQUE KEY _Idx1 ( row2 )
);
INSERT INTO random_data (row2)
SELECT LPAD(num, 8, 0)
FROM (
SELECT h * 10000000 g * 1000000 f * 100000 e * 10000 d * 1000 c * 100 b * 10 a AS num
FROM (SELECT 0 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) ta
JOIN (SELECT 0 b 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) tb
JOIN (SELECT 0 c 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) tc
JOIN (SELECT 0 d 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) td
JOIN (SELECT 0 e 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) te
JOIN (SELECT 0 f 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) tf
JOIN (SELECT 0 g 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) tg
JOIN (SELECT 0 h 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) th
) n
ORDER BY RAND()
LIMIT 100000;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/376315.html
上一篇：奇數/偶數遞回函式中的分段錯誤
下一篇：遞回復制一個字串