mysql 存储过程循环插入数据

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `test_insert`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_insert`()
BEGIN 
SET @i=1; 
SET @value='值';
SET @max=10000000; 
 SET autocommit=0;-- 禁止自动提交
 START TRANSACTION;
 WHILE (@i<=@max)
 DO 
  INSERT INTO t_part_test VALUES (@i,CONCAT(@value,@i)); 
  IF (@i%10000=0)
  THEN COMMIT;
  END IF;
   SET @i = @i + 1; 
    END WHILE;
    COMMIT;
    SET autocommit=1;-- 禁止自动提交
     END$$

DELIMITER ;

插入一千万条数据 16分钟

调用存储过程：call test_insert

2. loaddata加载数据

java生成导入文件

File file2 = new File("D:\\localdata2.txt");
		try {
			if (file2.exists()) {
				System.out.println("文件存在");
				boolean deleteFlag = file2.delete();
				if (deleteFlag) {
					System.out.println("文件删除成功");
				}
			}
			file2.createNewFile(); // 文件的创建，注意与文件夹创建的区别
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		/**
		 * 最简单的文件读写方法是使用类FileWriter
		 * (它的父类依次是java.io.OutputStreamWriter——>java.io.
		 * Writer——>java.lang.Object );
		 */

		// 下面是向文件file2里面写数据
		try {
			FileWriter fileWriter = new FileWriter(file2);
			StringBuilder sbStr = new StringBuilder();
			int maxValue = 10000000;
			for (int i = 1; i <= maxValue; i++) {
				if (i == maxValue) {
					sbStr.append(i + "," + "值" + i);
				}else{
					sbStr.append(i + "," + "值" + i + "/n");
				}
			}
			fileWriter.write(sbStr.toString());
			fileWriter.close(); // 关闭数据流

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

loaddata 执行耗时10分钟

LOAD DATA LOCAL INFILE 'D:\\localdata.txt' INTO TABLE t_part_test3(id,detail)  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';