這個SQL需要點計算數值去轉換出來~
請各位大神想想如何產生隨機10組IP出來~
如下圖：(IP是隨機產生)
寫法等有人想好再貼~@@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的格式，應該更複雜吧..
已邀請的邦友 {{ invite_list.length }}/5
select concat(
trunc( random() * 254 ), '.'
,trunc( random() * 254 ), '.'
,trunc( random() * 254 ), '.'
,trunc( random() * 254 )
)::INET
from generate_series(1,20);
excel =ROUNDDOWN(RAND()*255,0)&"."&ROUNDDOWN(RAND()*255,0)&"."&ROUNDDOWN(RAND()*255,0)&"."&ROUNDDOWN(RAND()*255,0)
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
用RANDBETWEEN(0,255)取代Rounddown(RAND()*255, 0)
=CONCATENATE(RANDBETWEEN(0,255),",",RANDBETWEEN(0,255),",",RANDBETWEEN(0,255),",",RANDBETWEEN(0,255))
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;
}
iThome鐵人賽
看影片追技術