テストデータ生成の研究
66520 ワード
文書ディレクトリ1.javaコードを使用して千万データを生成する例 2.mysqlを使用して千万データを生成する例.韓順平mysqlチュートリアル 1.javaコードを使用して千万データを生成する例
Javaを使用してテストデータテーブル構造を生成する
JAvaコード、転載元:https://blog.csdn.net/qq_30629571/article/details/70756394
2.mysqlを使用して千万データを生成する例.韓順平mysqlチュートリアルから
テーブル構造の作成
よびだし
Javaを使用してテストデータテーブル構造を生成する
drop database if exists datatest;
create database datatest;
use datatest;
CREATE TABLE datamillion (
sname VARCHAR (255),
description VARCHAR (255),
creat_time TIMESTAMP,
asset varchar(255),
weight VARCHAR (255),
address VARCHAR (255),
email VARCHAR (255),
moblie VARCHAR (255),
habit VARCHAR (255),
school VARCHAR (255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;
JAvaコード、転載元:https://blog.csdn.net/qq_30629571/article/details/70756394
package test;
import java.io.*;
import java.net.SocketTimeoutException;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class CreateData {
//
public static String mysqlDriver = "com.mysql.jdbc.Driver";
public static String mysqlurl = "jdbc:mysql://localhost:3306/datatest?useUnicode=true&characterEncoding=UTF-8";
public static String base = "abcdefghijklmnopqrstuvwxyz0123456789";
private static String firstName=" " +
" " +
" " +
" ";
private static final String[] email_suffix=("@gmail.com,@yahoo.com,@msn.com,@hotmail.com,@aol.com,@ask.com,@live.com," +
"@qq.com,@0355.net,@163.com,@163.net,@263.net,@3721.net,@yeah.net,@googlemail.com,@126.com,@sina.com," +
"@sohu.com,@yahoo.com.cn").split(",");
private static String[] telFirst="134,135,136,137,138,139,150,151,152,157,158,159,130,131,132,155,156,133,153".split(",");
/**
* Email
* @param lMin
* @param lMax
* @return
*/
public static String getEmail(int lMin,int lMax) {
int length=getNum(lMin,lMax);
StringBuffer sb = new StringBuffer();
for (int i = 0; i < length; i++) {
int number = (int)(Math.random()*base.length());
sb.append(base.charAt(number));
}
sb.append(email_suffix[(int)(Math.random()*email_suffix.length)]);
return sb.toString();
}
/**
*
*/
private static String getTel() {
int index=getNum(0,telFirst.length-1);
String first=telFirst[index];
String second=String.valueOf(getNum(1,888)+10000).substring(1);
String thrid=String.valueOf(getNum(1,9100)+10000).substring(1);
return first+second+thrid;
}/**
* aihao
*/
private static String[] habitstr=(" , , DOTA, , , , , , , , , , , ," +
" , , , , ").split(",");
private static String getHabit() {
int index=getNum(0,habitstr.length-1);
String first=habitstr[index];
return first;
}/**
* school
*/
private static String[] schoolstr=(" , , , , , , , , , , , , , ," +
" , , , , ").split(",");
private static String getSchool() {
int index=getNum(0,schoolstr.length-1);
String first=schoolstr[index];
return first;
}
/**
*
*/
private static String getChineseName() {
int index=getNum(0, firstName.length()-1);
String first=firstName.substring(index, index+1);
int index2=getNum(0, firstName.length()-1);
String second=firstName.substring(index2, index2+1);
return first+second;
}
/**
*
* @return
*/
private static String[] road=(" , , , , , , , , , ," +
" , , , , , , , , , , , , , ," +
" , , , , , , , , , , , , , ," +
" , , , , , , , , , , , , ," +
" , , , , , , , , , , , , ," +
" , , , , , , , , , , , ," +
" , , , , , , , , , , , , , ," +
" , , , , , , , , , , , , , ," +
" , , , , , , , , , ," +
" , , , , , , , , , , , ," +
" , , , , , , , , , , , , ," +
" , , , , , , , , , ").split(",");
private static String getRoad() {
int index=getNum(0,road.length-1);
String first=road[index];
String second=String.valueOf(getNum(11,150))+" ";
String third="-"+getNum(1,20)+"-"+getNum(1,10);
return first+second+third;
}
/**
*
* @param len int
* @return String
*/
public static String getRandomJianHan(int len) {
String ret="";
for(int i=0;i<len;i++){
String str = null;
int hightPos, lowPos; //
Random random = new Random();
hightPos = (176 + Math.abs(random.nextInt(39))); //
lowPos = (161 + Math.abs(random.nextInt(93))); //
byte[] b = new byte[2];
b[0] = (new Integer(hightPos).byteValue());
b[1] = (new Integer(lowPos).byteValue());
try
{
str = new String(b, "GBk"); //
}
catch (UnsupportedEncodingException ex)
{
ex.printStackTrace();
}
ret+=str;
}
return ret;
}
/**
*
* @return
*/
public static Map getPersonInfo() {
Map map=new HashMap();
map.put("name", getChineseName());
map.put("road", getRoad());
map.put("tel", getTel());
map.put("email", getEmail(6,9));
return map;
}
public static int getNum(int start,int end) {
return (int)(Math.random()*(end-start+1)+start);
}
//public static String excelPath = "e:\\tables.xlsx";
//
public static void main(String[] args) throws IOException, Exception{
long start = System.currentTimeMillis(); //
//
Class.forName(mysqlDriver);//jvm ， ManangerDriver.registerDriver(...)
Connection mysqlconn = DriverManager.getConnection(mysqlurl, "root", "123456");// ， ，
mysqlconn.setAutoCommit(false);//sql
//Statement stmt = mysqlconn.createStatement();// SQL stmt
PreparedStatement pst = null;// statement， PrepareStatement SQL
// id /
String sql = "insert into datamillion(sname,description,creat_time,asset,weight,address,email,moblie,habit,school) " +
"values(?,?,?,?,?,?,?,?,?,?)";
pst = mysqlconn.prepareStatement(sql);
//
for (int i = 0; i < 500000; i++) {
String name1 = getChineseName();
String description2 = getRandomJianHan(10);
java.util.Date date = new java.util.Date();// .
Timestamp create_time3 = new Timestamp(date.getTime());
float asset4 = (float)getNum(100,2000000);
float weight5 = (float)getNum(100,200);
String address6 = getRoad();
String email7 = getEmail(15,18);
String mobile8 = getTel();
String habit9 = getHabit();
String school0 = getSchool();
pst.setString(1, name1);
pst.setString(2, description2);
pst.setTimestamp(3, create_time3);
pst.setFloat(4, asset4);
pst.setFloat(5, weight5);
pst.setString(6, address6);
pst.setString(7, email7);
pst.setString(8, mobile8);
pst.setString(9, habit9);
pst.setString(10, school0);
pst.addBatch();// SQL
if ((i + 1) % 10000 == 0) {
pst.executeBatch();//
mysqlconn.commit();//
pst.clearBatch();// SQL
}
}
long end = System.currentTimeMillis();//
System.out.print((end-start)/1000.0);//
//
//stmt.close();
pst.close();
mysqlconn.close();
}
}
2.mysqlを使用して千万データを生成する例.韓順平mysqlチュートリアルから
テーブル構造の作成
drop table if exists dept;
CREATE TABLE dept( /* */
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
drop table if exists emp;
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /* */
ename VARCHAR(20) NOT NULL DEFAULT "", /* */
job VARCHAR(9) NOT NULL DEFAULT "",/* */
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/* */
hiredate DATE NOT NULL,/* */
sal DECIMAL(7,2) NOT NULL,/* */
comm DECIMAL(7,2) NOT NULL,/* */
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /* */
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
drop table if exists salgrade;
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
#
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
drop function if exists rand_string;
delimiter $$
create function rand_string(n INT)
returns varchar(255) #
begin
# chars_str， varchar(100)
# chars_str 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
# concat : mysql
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
drop function if exists rand_num;
delimiter$$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter;
drop procedure if exists insert_emp;
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 autocommit 0
#autocommit = 0 :
set autocommit = 0; # sql
repeat
set i = i + 1;
# ， emp
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
#commit sql ，
commit;
end $$
delimiter ;
よびだし
call insert_emp(100001,8000000);