MySql 循环添加存储过程

最新推荐文章于 2024-07-19 03:25:55 发布

原创最新推荐文章于 2024-07-19 03:25:55 发布 · 318 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #存储

Mysql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种使用MySQL存储过程实现数据批量插入的方法。通过定义一个存储过程，该过程可以接受参数并根据这些参数随机生成字符串来作为数据进行插入。此方法能够有效地提高数据插入效率，并展示了如何设置循环来控制插入数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DROP TABLE school;

CREATE TABLE `school` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL,
  `score` DECIMAL(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
)CHARSET=utf8;


DELIMITER ;; 
CREATE PROCEDURE rand_insert(IN NUM INT,IN first_name VARCHAR(500),IN middle_name VARCHAR(500),IN last_name VARCHAR(500),IN two_percent DECIMAL(3,2)) 
BEGIN 
DECLARE fname VARCHAR(3);  
DECLARE flength INT;
DECLARE mname VARCHAR(3);  
DECLARE mlength INT;
DECLARE lname VARCHAR(3);  
DECLARE llength INT;
DECLARE fullname VARCHAR(9);
DECLARE username VARCHAR(9);
DECLARE score INT;  #分数
DECLARE i INT DEFAULT 0;
DECLARE two_num INT DEFAULT 0;
SET flength = LENGTH(first_name)/3;
SET mlength = LENGTH(middle_name)/3;
SET llength = LENGTH(last_name)/3;
WHILE i<num DO 
SET fname = SUBSTRING(first_name,FLOOR(1+RAND()* flength),1);
SET mname = SUBSTRING(middle_name,FLOOR(1+RAND()* mlength),1);
SET lname = SUBSTRING(last_name,FLOOR(1+RAND()* llength),1);
SET two_num = two_num+1;
IF two_num < ROUND(num * two_percent) OR two_num = ROUND(num * two_percent) THEN 
  SET fullname = CONCAT(fname,mname);
ELSE
  SET fullname = CONCAT(fname,mname,lname);
END IF;
SET score = ROUND(RAND()*100);
#如果名字在这次添加中已经存在，就再走一次循环
SELECT `name` INTO username FROM school ORDER BY id DESC LIMIT 0,1;
IF username = fullname AND i>0 THEN 
  SET i=i;
END IF;
IF fullname IS NULL THEN 
  SET i=i;
END IF;
IF (username != fullname OR i<1) AND fullname IS NOT NULL THEN 
  INSERT INTO `school` (`name`,score) VALUES (fullname,score);
  SET i=i+1; 
END IF;

END WHILE ; 
COMMIT;
END;; 

DELETE FROM `school`;
CALL rand_insert(12,'阿斯兰的房间爱上老地方','是电风扇的范德萨','自行车字形从',0.3);
SELECT * FROM school;