构造大量测试数据的方法（MySql）

创建测试表

CREATE TABLE `sys_user` (
	`id` CHAR (32) NOT NULL DEFAULT '' COMMENT '主键',
	`username` VARCHAR (100) NOT NULL DEFAULT '' COMMENT '用户名',
	`password` CHAR (32) NOT NULL DEFAULT '' COMMENT '密码',
	`status` TINYINT (1) NOT NULL DEFAULT '0' COMMENT '状态',
	`desz` VARCHAR (200) DEFAULT NULL COMMENT '描述',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用户表';
           

方法一：存储过程

1.创建存储过程

DELIMITER @
CREATE PROCEDURE myproc (IN total INT)
BEGIN

DECLARE v INT ;
SET v = 1 ;
WHILE v <= total DO
	INSERT INTO sys_user (
		id,
		username,
		PASSWORD,
		STATUS,
		desz
	)
VALUES
	(
		REPLACE (uuid(), '-', ''),
		concat('用户-', v),
		concat('pwd-', v),
		v % 2,
		concat('描述-', v)
	) ;
SET v = v + 1 ;
END
WHILE ;
END ;@
DELIMITER ;
           

2.调用存储过程

call myproc
           

方法二：多线程 + jdbc批量提交

public class DataTest implements Runnable {
	private final static String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp&characterEncoding=UTF8";
	private final static String username = "root";
	private final static String password = "";

	/**
	 * 线程数
	 */
	private int threadNum;

	/**
	 * 每个线程插入记录数量
	 */
	private int operateNum;

	public DataTest(int threadNum, int operateNum) {
		this.threadNum = threadNum;
		this.operateNum = operateNum;
	}

	public static void main(String[] args) {
		int threadNum = 100, operateNum = 10000;

		for (int i = 0; i < threadNum; i++)
			new Thread(new DataTest(i, operateNum)).start();

	}

	public void run() {
		try {
			long start = System.currentTimeMillis();
			String threadName = Thread.currentThread().getName();
			System.out.println("线程" + threadName + "启动");

			Connection conn = getConn();
			Statement st = conn.createStatement();

			for (int i = 1; i <= operateNum; i++) {
				st.addBatch(createSql(i));
				if (i % 1000 == 0)
					commit(threadName, st);
			}

			if (operateNum % 1000 != 0)
				commit(threadName, st);

			st.close();
			conn.close();

			System.out.println(
					"线程" + threadName + "，插入" + operateNum + "条，耗时： " + (System.currentTimeMillis() - start) + " 毫秒");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private String createSql(int i) {
		String index = i + "-" + threadNum;

		String id = uuid();
		String username = "用户" + index;
		String password = uuid();
		boolean status = new Random().nextBoolean();
		String desz = "描述" + index;

		return "insert into sys_user(id,username,password,status,desz) values('" + id + "','" + username + "','"
				+ password + "'," + status + ",'" + desz + "')";
	}

	private String uuid() {
		return UUID.randomUUID().toString().replace("-", "");
	}

	public static Connection getConn() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(url, username, password);
	}

	private void commit(String threadName, Statement st) throws Exception {
		System.out.println("线程" + threadName + "，提交" + st.executeBatch().length + "条记录");
	}
}