mysql通过存储过程以及事务批量生成数据

存储过程加上事务能够提高插入效率:

CREATE DEFINER=`root`@`%` PROCEDURE `autoinsert`(IN NUM INT)
BEGIN
  DECLARE INIT_NUM INT DEFAULT 0 ;
    START TRANSACTION;
    WHILE(INIT_NUM < NUM)
    DO
        insert into `user`(`name`,`city_id`) values(rand_str(10),rand_int(2));
        SET INIT_NUM = INIT_NUM+1;
    END WHILE;
    COMMIT;
    END

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `forum_id` bigint(20) unsigned NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test_forum_id_IDX` (`forum_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=750136 DEFAULT CHARSET=utf8mb4

DROP PROCEDURE IF EXISTS test.BatchInsertTest;
delimiter //
CREATE PROCEDURE BatchInsertTest(IN loop_time INT)
  BEGIN
      DECLARE Var INT;
      SET Var = 0;
      START TRANSACTION;
      WHILE Var < loop_time DO
          INSERT INTO `test` ( `forum_id`, `created`) select  FLOOR(1 + (RAND() * 1000)),FROM_UNIXTIME(UNIX_TIMESTAMP()-(RAND() * 30 * 86400));
          SET Var = Var + 1;
      END WHILE;
     COMMIT;
  END;
  //
delimiter ;

CALL BatchInsertTest(10000);

select count(*) from test;

mysql生成固定位数随机字母以及数字