为了在视频表中插入随机数据,我想创建一个存储过程。我已经在做了UserProfile记录数据的表生成数据。4 Z+ x1 y' g9 k' L) s
注:用户名在视频表中FK元素。 3 P# {8 f# H& [0 g, H5 uCREATE TABLE UserProfile ( Username VARCHAR(45) NOT NULL , UserPassword VARCHAR(45) NOT NULL , Email VARCHAR(45) NOT NULL , FName VARCHAR(45) NOT NULL , LName VARCHAR(45) NOT NULL , Birthdate DATE , Genger VARCHAR(10) NOT NULL , ZipCode INT , Image VARCHAR(50) , PRIMARY KEY(Username));GOCREATE TABLE Video ( VideoId INT NOT NULL DEFAULT 1000 , Username VARCHAR(45) NOT NULL , VideoName VARCHAR(160) NOT NULL , UploadTime DATE , TotalViews INT , Thumbnail VARCHAR(100) , PRIMARY KEY(VideoId), FOREIGN KEY(Username) REFERENCES UserProfile(Username));GO 2 [3 }; w- O# H o" m 解决方案: " a( v; Z1 [9 [) Z
即使在SQL生成随机数据并不难。& Q& H% M/ k: V" x
例如,从你的userprofile在表中获取随机用户名。 . f7 _ `* F' W, Z6 _BEGIN-- get a random row from a tableDECLARE @username VARCHAR(50)SELECT @username = [Username] FROM SELECT ROW_NUMBER() OVER(ORDER BY [Username]) [row],[Username] FROM [UserProfile]) t WHERE t.row = 1 (SELECT CAST(RAND() * COUNT(*) as INT) FROM [UserProfile])print(@username)END产生随机整数… 2 T3 e+ E) s* G: ^) oBEGIN-- get a random integer between 3 and 7 - 1)DECLARE @totalviews INTSELECT @totalviews = CAST(RAND() * 5 3 as INT)print(@totalviews)END生成随机的varchar字符串 / c% D; V% G5 eBEGIN-- get a random varchar ascii char 32 to 128DECLARE @videoname VARCHAR(160)DECLARE @length INTSELECT @videoname = ''SET @length = CAST(RAND() * 160 as INT)WHILE @length 0 BEGIN SELECT @videoname = @videoname CHAR(CAST(RAND() * 96 32 as INT)) SET @length = @length - 1 ENDprint(@videoname)END最后,随机日期2 E& M. s( _! ? \! B
BEGIN-- get a random datetime /- 365 daysDECLARE @uploadtime DATETIMESET @uploadtime = GETDATE() * 2 * RAND() - 365)print(@uploadtime)END