需求:在不改变现有数据的情况下,插入N条数据;
实例表:
mysql> desc student; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(10) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | sex | varchar(4) | YES | | NULL | | | birth | year(4) | YES | | NULL | | | department | varchar(20) | YES | | NULL | | | address | varchar(50) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+
创建语句:
DROP TABLE IF EXISTS `student`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `sex` varchar(4) DEFAULT NULL, `birth` year(4) DEFAULT NULL, `department` varchar(20) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `student` -- LOCK TABLES `student` WRITE; /*!40000 ALTER TABLE `student` DISABLE KEYS */; INSERT INTO `student` VALUES (901,'张老大','男',1985,'计算机系','北京市海淀区'),(902,'张老二','男',1986,'中文系','北京市昌平区'),(903,'张三','女',1990,'中文系','湖南省永州市'),(904,'李四','男',1990,'英语系','辽宁省阜新市'),(905,'王五','女',1991,'英语系','福建省厦门市'),(906,'王六','男',1988,'计算机系','湖南省衡阳市'); /*!40000 ALTER TABLE `student` ENABLE KEYS */; UNLOCK TABLES;
创建存储过程:
DROP PROCEDURE IF EXISTS proc_i_loop; DELIMITER ;; CREATE PROCEDURE proc_i_loop(IN loop_times INT) BEGIN DECLARE var INT DEFAULT 0; set var = (select count(*) from student); select var; set loop_times=loop_times+var; WHILE var<loop_times DO SET var=var+1; INSERT INTO student(id,name,sex,birth,department,address) VALUES (var,CONCAT('test',var),'男',1999,'法律系',NULL); END WHILE; END ;; DELIMITER ;
执行插入:
call proc_i_loop(100000);