Mysql MRG_MyISAM引擎分表法

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 +=",";elsesql +=";";}//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();}}}