iT邦幫忙

1

又來出奇怪題了XD..如何產生10組隨機IP資料

這個SQL需要點計算數值去轉換出來~

請各位大神想想如何產生隨機10組IP出來~

如下圖:(IP是隨機產生)
https://ithelp.ithome.com.tw/upload/images/20181112/20061369gl3b3XOD0T.png

寫法等有人想好再貼~@@a


SQL如下

select Convert(varchar,CAST(NEWID() AS binary(1)) % 255)
+ '.' + Convert(varchar,CAST(NEWID() AS binary(1)) % 255)
+ '.' + Convert(varchar,CAST(NEWID() AS binary(1)) % 255) 
+ '.' + Convert(varchar,CAST(NEWID() AS binary(1)) % 255)
as IP
from (
	select Row_Number()Over(order by [number]) as Sort
	from master..spt_values
) as k
where Sort <= 10

小魚
其實我是這樣大量更新的

update keyword 
set keyword_IP = Convert(varchar,CAST(NEWID() AS binary(1)) % 255)
+ '.' + Convert(varchar,CAST(NEWID() AS binary(1)) % 255)
+ '.' + Convert(varchar,CAST(NEWID() AS binary(1)) % 255) 
+ '.' + Convert(varchar,CAST(NEWID() AS binary(1)) % 255)

如果要轉換IPv6的格式,應該更複雜吧..

看更多先前的討論...收起先前的討論...
小魚 iT邦高手 1 級 ‧ 2018-11-13 00:28:01 檢舉
問題是你產生的也只是 字串 而已啊...
又不是真的IP...
當然不是真的IP~@@
其實是因為某個因素~要補上假IP資料..一次要刷幾10萬筆資料= =a
TB iT邦新手 4 級 ‧ 2018-11-13 09:17:14 檢舉
PHP 的話我會用 DB Seeder
供您參考 : )
suasem iT邦新手 5 級 ‧ 2018-11-13 09:24:12 檢舉
任意IP都行嗎? 保留IP要不要濾掉,IP可以重複嗎,或是可以重複幾次
隨機當然是任意@@
newkevin iT邦高手 1 級 ‧ 2018-11-13 11:25:32 檢舉
通常都是請款
證明有那麼多筆連進來
是嗎 嘿嘿
行家...你真聰明...XD
4
一級屠豬士
iT邦新手 3 級 ‧ 2018-11-13 09:22:58
最佳解答
select concat(
        trunc( random() * 254 ), '.'
       ,trunc( random() * 254 ), '.'
       ,trunc( random() * 254 ), '.'
       ,trunc( random() * 254 )
       )::INET
  from generate_series(1,20);

https://ithelp.ithome.com.tw/upload/images/20181113/20092833YXbS3VpOms.png

喔喔~果然各SQL都不一樣

2
eigen
iT邦新手 1 級 ‧ 2018-11-13 09:10:04

excel =ROUNDDOWN(RAND()*255,0)&"."&ROUNDDOWN(RAND()*255,0)&"."&ROUNDDOWN(RAND()*255,0)&"."&ROUNDDOWN(RAND()*255,0)

喔~~你採用Excel方式~

2
rogeryao
iT邦研究生 5 級 ‧ 2018-11-13 09:37:37

DROP TABLE Test01
CREATE TABLE Test01
(RID int IDENTITY(1,1), IP varchar(15))
DECLARE
@TotalNum INT,
@Num INT

SET @TotalNum = 10
SET @Num =1

WHILE @Num <= @TotalNum
BEGIN
insert into Test01(IP)
SELECT (CONVERT(VARCHAR(4),ROUND(RAND()*254,0))+'.'+
CONVERT(VARCHAR(4),ROUND(RAND()*254,0))+'.'+
CONVERT(VARCHAR(4),ROUND(RAND()*254,0))+'.'+
CONVERT(VARCHAR(4),ROUND(RAND()*254,0)))
SET @Num = @Num + 1
END
select *
from Test01

https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=0ff00f44021821912edbae2f5e905057

喔~~用TSQL

2
pcw
iT邦研究生 4 級 ‧ 2018-11-13 09:43:29

excel

RANDBETWEEN(0,255)取代Rounddown(RAND()*255, 0)

=CONCATENATE(RANDBETWEEN(0,255),",",RANDBETWEEN(0,255),",",RANDBETWEEN(0,255),",",RANDBETWEEN(0,255))
0
watain666
iT邦新手 5 級 ‧ 2018-11-13 12:00:37

Faker 看你要 ipv4, local ipv4, ipv6 都有

$faker = new Faker\Generator();
$faker->addProvider(new Faker\Provider\Internet($faker));

for ($i=0; $i < 10; $i++) {
    echo $faker->ipv4 . PHP_EOL;
    echo $faker->localIpv4 . PHP_EOL;
    echo $faker->ipv6 . PHP_EOL;
}

其實程式的迴圈,跟資料庫有關的我都禁用了
不能用for while do
這些跑幾百萬筆資料時,網頁都會掛在那裏...

我要發表回答

立即登入回答