mysql 生成大量数据

往mysql数据库中批量数据主要用到两种方法，一种是使用存储过程

create procedure add_data(IN num int)
BEGINDECLARE count INT DEFAULT 0;DECLARE id varchar(40);WHILE count < num DOSET count = count + 1;SET id = substring(md5(rand()), 1, 20);insert INTO table (id) VALUES (id);END WHILE;
END;SET AUTOCOMMIT=False
call add_data(5000000)
SET AUTOCOMMIT=True

另一种是写java脚本，优点是比较灵活，但速度不够快

@Test
void test() throws SQLException {connect = DriverManager.getConnection(host, username, password);connect.setAutoCommit(false);long start = System.currentTimeMillis();insert("risk_info0", 5000000);insert("risk_info1", 10000000);for (int i = 2; i <= partNum; i++) {insert("risk_info" + i, 100000);}long end = System.currentTimeMillis();System.out.println(end - start);connect.close();
}private static void insert(String tableName, int count) throws SQLException {String sql = String.format("insert into %s (id) values (?);", tableName);PreparedStatement pstmt = connect.prepareStatement(sql);try {for (int i = 1; i <= count; i++) {pstmt.setString(1, RandomStringUtils.randomAlphanumeric(20));pstmt.addBatch();if (i % 10000 == 0) {pstmt.executeBatch();connect.commit();System.out.println("commit");}}pstmt.executeBatch();connect.commit();} catch (Exception e) {System.out.println("failed! " + e.getMessage());return;}System.out.println("\n<<--表" + tableName + "插入记录" + count + "条-->>");
}