構造大量測試資料的方法（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 + "條記錄");
	}
}