MySQL中批量插入数据

不管怎么样, 你需要大量的数据, 那么问题来了, 怎么快速地插入呢?

1. 这是我创建的一个批量插入的存储过程…

当然, 你可以把参数去掉, 一次性插入1W, 10W…

CREATE DEFINER=`ijdb_user1`@`%` PROCEDURE `author_copy_self`(IN `nThousnd` INT)
    NO SQL
BEGIN

DECLARE i INT DEFAULT 0;
SET i = 0;
WHILE i < nThousnd
DO
  INSERT INTO 
  author(name,email) 
    SELECT name,email 
    FROM author 
    WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM author)))
    ORDER BY id LIMIT 1000; 
SET i = i + 1;
END WHILE ;

END

2. 表author结构是这样的:

3. 创建一个事件, 让他慢慢地运行去, 如果你不着急的话….

CREATE DEFINER=`ijdb_user1`@`%` EVENT `auto_insert_n_thousnd_records_2_author`
ON SCHEDULE EVERY 1 MINUTE 
STARTS '2015-09-18 16:39:16' 
ENDS '2017-09-17 00:00:00'
ON COMPLETION 
PRESERVE
ENABLE 

DO CALL author_copy_self(1)

转载于:https://www.cnblogs.com/BigBigLiang/p/4818768.html