mysql 生成测试数据

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

create procedure add_data(IN num int)
BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE id varchar(40);
    WHILE count < num DO
            SET 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 + "条-->>");
}