Mysql MRG_MyISAM引擎分表法
DROP TABLE IF EXISTS `t_s_offline_event1`;
CREATE TABLE `t_s_offline_event1` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event2`;
CREATE TABLE `t_s_offline_event2` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event3`;
CREATE TABLE `t_s_offline_event3` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event4`;
CREATE TABLE `t_s_offline_event4` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event5`;
CREATE TABLE `t_s_offline_event5` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event6`;
CREATE TABLE `t_s_offline_event6` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event7`;
CREATE TABLE `t_s_offline_event7` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event8`;
CREATE TABLE `t_s_offline_event8` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event9`;
CREATE TABLE `t_s_offline_event9` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `t_s_offline_event0`;
CREATE TABLE `t_s_offline_event0` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
CREATE TABLE `test`.`create_id` (
`id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT ,
`ctTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
status int(11) NOT NULL
PRIMARY KEY (id)
) ENGINE = MYISAM
DROP TABLE IF EXISTS `t_s_offline_event`;
CREATE TABLE `t_s_offline_event` (
`id` int(11) NOT NULL ,
`fileName` varchar(128) DEFAULT NULL,
`fileOffTime` int(11) DEFAULT NULL,
`chktm` timestamp NULL DEFAULT NULL,
`laneIndex` int(11) DEFAULT NULL,
`event` varchar(20) DEFAULT NULL,
`grade` varchar(8) DEFAULT NULL,
`location` varchar(31) DEFAULT '',
`pltnum` varchar(20) DEFAULT NULL,
`speed` int(2) DEFAULT NULL,
`logo` varchar(20) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`lpcolor` varchar(20) DEFAULT NULL,
`platetype` varchar(20) DEFAULT NULL,
`vehicletype` varchar(20) DEFAULT NULL,
`imageL` varchar(128) DEFAULT 'images/base/404insets.png',
`imageA` varchar(128) DEFAULT 'images/base/404insets.png',
`imageB` varchar(128) DEFAULT 'images/base/404insets.png',
`imageC` varchar(128) DEFAULT 'images/base/404insets.png',
`imageCombo` varchar(128) DEFAULT 'images/base/404insets.png',
PRIMARY KEY (`id`)
) ENGINE=MRG_MYISAM UNION=(t_s_offline_event1,t_s_offline_event2, t_s_offline_event3, t_s_offline_event4, t_s_offline_event5, t_s_offline_event6, t_s_offline_event7, t_s_offline_event8, t_s_offline_event9, t_s_offline_event0 ) INSERT_METHOD=no ;
delete from t_s_offline_event0;
delete from t_s_offline_event1;
delete from t_s_offline_event2;
delete from t_s_offline_event3;
delete from t_s_offline_event4;
delete from t_s_offline_event5;
delete from t_s_offline_event6;
delete from t_s_offline_event7;
delete from t_s_offline_event8;
delete from t_s_offline_event9;
JAVA代码插入1千万条数据
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Main {
private static long MAX_ID = 10000000;
public static String getTableName(long id){
if(id>MAX_ID)
return "t_s_offline_event9";
return "t_s_offline_event"+(id/(MAX_ID/10));
}
public static void main(String[] args) {
PreparedStatement pstm =null;
ResultSet rs = null;
String plate = "";
long maxid = 1;
String filename = "C:/UsersGKJIDesktop北1_CVR录像1_2013-11-14 15-54-00_2013-11-14 18-23-31_0.avi";
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String time = "";
String plateStr = "ABCDEFGHIGKLMNOPQRSTUVWXYZ";
int rand = 0 ;
int randPlateType = 0;
int index = 0;
int j=0;
String location[] ={"dl03010101", "dl03010102", "dl03010103", "dl03010104"};
String locationid="";
String tableName = getTableName(0);
int lines = 0;
int i=1;
int maxLineUpdate=1000;
String sqlmax="";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/vsimonitor",
"root", "password");
sqlmax = "select max(id) from t_s_offline_event ";
pstm = con.prepareStatement(sqlmax);
rs = pstm.executeQuery();
if(rs!=null && rs.next()){
maxid = rs.getLong(1);
System.out.println(rs.getLong(1));
}
while(i<MAX_ID){
tableName = getTableName(i);
String sql ="INSERT INTO "+tableName+" VALUES ";
j=0;
while(j<maxLineUpdate){
j++;
maxid ++;
i++;
time = format.format(new Date());
rand = (int)Math.random()*100%4;
locationid = location[rand];
rand = (int)Math.random()*10%5;
plate = "贵";
if(rand==0)
plate += "AU";
else{
index = (int)Math.random()*100%26;
plate += plateStr.charAt(index);
index = (int)Math.random()*100%26;
plate += plateStr.charAt(index);
}
rand = (int)(Math.random()*1000);
plate += String.format("%04d", rand);
// System.out.println(plate);
sql += "("+maxid+", '"+filename+"', '159250', '"+time+"', '3', 'kk', '01', '"+locationid+"', '"+plate+"', '0', 'black', 'blue_white', '', 'normal_blue', 'small', '/pic/channel1/2013_11_15/pic1/13_42_22_082_00124.jpg', '/pic/channel1/2013_11_15/pic1/13_42_22_082_00124a.jpg', '', '', '')";
if(j!=maxLineUpdate)
sql +=",";
else
sql +=";";
}
// System.out.println(sql);
pstm = con.prepareStatement(sql);
lines = pstm.executeUpdate();
System.out.println("Update lines="+lines + " "+time + " maxid="+maxid +" tableName="+tableName);
}
}catch(Exception ex){
ex.printStackTrace();
}
}
}