create procedure insert_t() #创建一个储存过程 begin set @a=2000000001; #学号 set @e=18200000000; #手机号 while @a<2000020001 do insert into student values(@a,'周润发',男,@e); set @a=@a+1; set @e=@e+1; end while; end; call insert_t(); 改过之后还是不行。第一次执行插入2900条数据,第二次1449,每次执行都不一样
把吧,b,c,e都换成常量也不行求解。是不是循环语句有问题?
这个真不至于性别还创建一个函数,给你个全过程,插入多少就call多少 USE test; DROP TABLE IF EXISTS student; CREATE TABLE `student` ( `stu_id` INT(11) NOT NULL AUTO_INCREMENT, `stu_name` CHAR(30) NOT NULL, `stu_sex` CHAR(2) NOT NULL, `stu_telephone` CHAR(11) DEFAULT NULL, PRIMARY KEY (`stu_id`) ); -- 创建姓名函数 DELIMITER $$ DROP FUNCTION IF EXISTS rang_string $$ CREATE FUNCTION `test`.`rang_string`() RETURNS VARCHAR(6) BEGIN DECLARE chars_str NVARCHAR(50) DEFAULT '赵钱孙李周吴郑王高乔孟姜浮沉董工好啊的是和额发的我'; DECLARE return_str VARCHAR(6) DEFAULT ''; DECLARE i INT DEFAULT 0; DECLARE n INT; SET n=ROUND(2+RAND(),0); WHILE i < n DO SET return_str=CONCAT(return_str,SUBSTRING(chars_str,ROUND(RAND()*25,0),1)); SET i = i + 1; END WHILE; RETURN return_str; END$$ DELIMITER ; -- 姓名 SELECT test.rang_string(); -- 性别 SELECT IF(RAND()>0.5,'男','女')-- 手机 SELECT IF(RAND()<0.5,13000000000,15000000000)+ROUND(RAND()*100000000,0) -- 插入数据存储 DELIMITER $$ DROP PROCEDURE IF EXISTS sp_student $$ CREATE PROCEDURE `test`.`sp_student` (n INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < n DO INSERT INTO test.student SELECT NULL,`test`.`rang_string`(),IF(RAND()>0.5,'男','女'),IF(RAND()<0.5,13000000000,15000000000)+ROUND(RAND()*100000000,0); SET i = i +1; END WHILE; END$$ DELIMITER;
如果血魂stu_id想从2000000001开始,那就创建表的时候修改下AUTO_INCREMENT ALTER TABLE student AUTO_INCREMENT=2000000001;
调用一下!
begin
set @a=2000000001; #学号
set @e=18200000000; #手机号
while @a<2000020001 do
insert into student values(@a,'周润发',男,@e);
set @a=@a+1;
set @e=@e+1;
end while;
end;
call insert_t();
改过之后还是不行。第一次执行插入2900条数据,第二次1449,每次执行都不一样
USE test;
DROP TABLE IF EXISTS student;
CREATE TABLE `student` (
`stu_id` INT(11) NOT NULL AUTO_INCREMENT,
`stu_name` CHAR(30) NOT NULL,
`stu_sex` CHAR(2) NOT NULL,
`stu_telephone` CHAR(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
);
-- 创建姓名函数
DELIMITER $$
DROP FUNCTION IF EXISTS rang_string $$
CREATE
FUNCTION `test`.`rang_string`()
RETURNS VARCHAR(6)
BEGIN
DECLARE chars_str NVARCHAR(50) DEFAULT '赵钱孙李周吴郑王高乔孟姜浮沉董工好啊的是和额发的我';
DECLARE return_str VARCHAR(6) DEFAULT '';
DECLARE i INT DEFAULT 0;
DECLARE n INT;
SET n=ROUND(2+RAND(),0);
WHILE i < n
DO
SET return_str=CONCAT(return_str,SUBSTRING(chars_str,ROUND(RAND()*25,0),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END$$
DELIMITER ;
-- 姓名
SELECT test.rang_string();
-- 性别
SELECT IF(RAND()>0.5,'男','女')-- 手机
SELECT IF(RAND()<0.5,13000000000,15000000000)+ROUND(RAND()*100000000,0)
-- 插入数据存储
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_student $$
CREATE PROCEDURE `test`.`sp_student` (n INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < n DO
INSERT INTO test.student SELECT NULL,`test`.`rang_string`(),IF(RAND()>0.5,'男','女'),IF(RAND()<0.5,13000000000,15000000000)+ROUND(RAND()*100000000,0);
SET i = i +1;
END WHILE;
END$$
DELIMITER;
ALTER TABLE student AUTO_INCREMENT=2000000001;