#####创建分表,按月
CREATE TABLE `fwq_vehicle_pass_201811` (
`SHOWORDER` int(11) DEFAULT NULL,
`SHEETNAME` varchar(200) DEFAULT NULL,
`SHEETID` varchar(36) NOT NULL,
`DATATYPE` int(11) DEFAULT NULL,
`DATASTATUSID` int(11) DEFAULT '1',
`LASTOPENTIME` varchar(20) DEFAULT NULL,
`OPENER` varchar(20) DEFAULT NULL,
`OPENERID` varchar(36) DEFAULT NULL,
`PATTERNID` varchar(36) DEFAULT NULL,
`CREATEDATE` varchar(30) DEFAULT NULL,
`EDITUSERID` varchar(36) DEFAULT NULL,
`CREATEORGID` varchar(36) DEFAULT NULL,
`EDITUSER` varchar(20) DEFAULT NULL,
`CREATEUSERID` varchar(36) DEFAULT NULL,
`CREATEUSER` varchar(20) DEFAULT NULL,
`CREATEDEPT` varchar(80) DEFAULT NULL,
`CREATEORG` varchar(80) DEFAULT NULL,
`CREATEDEPTID` varchar(36) DEFAULT NULL,
`PASS_ID` bigint(255) DEFAULT NULL,
`CROSSING_ID` varchar(100) DEFAULT NULL,
`LANE_NO` varchar(100) DEFAULT NULL,
`DIRECTION_INDEX` varchar(100) DEFAULT NULL,
`PLATE_NO` varchar(100) DEFAULT NULL,
`PLATE_TYPE` varchar(100) DEFAULT NULL,
`PASS_TIME` datetime DEFAULT NULL,
`VEHICLE_SPEED` varchar(100) DEFAULT NULL,
`VEHICLE_LEN` varchar(100) DEFAULT NULL,
`PLATE_COLOR` varchar(100) DEFAULT NULL,
`VEHICLE_COLOR` varchar(100) DEFAULT NULL,
`VEHICLE_TYPE` varchar(100) DEFAULT NULL,
`VEHICLE_COLOR_DEPTH` varchar(100) DEFAULT NULL,
`PLATE_STATE` varchar(100) DEFAULT NULL,
`IMAGE_PATH` varchar(500) DEFAULT NULL,
`PLATE_IMAGE_PATH` varchar(500) DEFAULT NULL,
`TFS_ID` varchar(100) DEFAULT NULL,
`VEHICLE_STATE` varchar(100) DEFAULT NULL,
`RES_NUM1` varchar(100) DEFAULT NULL,
`RES_NUM2` varchar(100) DEFAULT NULL,
`RES_STR3` varchar(100) DEFAULT NULL,
`RES_STR4` varchar(100) DEFAULT NULL,
`VEHICLE_INFO_LEVEL` varchar(100) DEFAULT NULL,
`VEHICLE_LOGO` varchar(100) DEFAULT NULL,
`VEHICLE_SUBLOGO` varchar(100) DEFAULT NULL,
`VEHICLE_MODEL` varchar(100) DEFAULT NULL,
`province` varchar(100) DEFAULT NULL,
`PILOTSUNVISOR` varchar(100) DEFAULT NULL,
`sheet_sap` varchar(36) DEFAULT NULL,
PRIMARY KEY (`SHEETID`),
KEY `fvp_index` (`PASS_ID`) USING BTREE,
KEY `fvp_pt_index` (`PASS_TIME`) USING BTREE,
KEY `fvp_pl_index` (`PLATE_NO`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
说明:
创建表按月进行分表
ENGINE=MyISAM merge存储引擎来实现分表
#####创建总表
CREATE TABLE `fwq_vehicle_pass` (
`SHOWORDER` int(11) DEFAULT NULL,
`SHEETNAME` varchar(200) DEFAULT NULL,
`SHEETID` varchar(36) NOT NULL,
`DATATYPE` int(11) DEFAULT NULL,
`DATASTATUSID` int(11) DEFAULT '1',
`LASTOPENTIME` varchar(20) DEFAULT NULL,
`OPENER` varchar(20) DEFAULT NULL,
`OPENERID` varchar(36) DEFAULT NULL,
`PATTERNID` varchar(36) DEFAULT NULL,
`CREATEDATE` varchar(30) DEFAULT NULL,
`EDITUSERID` varchar(36) DEFAULT NULL,
`CREATEORGID` varchar(36) DEFAULT NULL,
`EDITUSER` varchar(20) DEFAULT NULL,
`CREATEUSERID` varchar(36) DEFAULT NULL,
`CREATEUSER` varchar(20) DEFAULT NULL,
`CREATEDEPT` varchar(80) DEFAULT NULL,
`CREATEORG` varchar(80) DEFAULT NULL,
`CREATEDEPTID` varchar(36) DEFAULT NULL,
`PASS_ID` bigint(255) DEFAULT NULL,
`CROSSING_ID` varchar(100) DEFAULT NULL,
`LANE_NO` varchar(100) DEFAULT NULL,
`DIRECTION_INDEX` varchar(100) DEFAULT NULL,
`PLATE_NO` varchar(100) DEFAULT NULL,
`PLATE_TYPE` varchar(100) DEFAULT NULL,
`PASS_TIME` datetime DEFAULT NULL,
`VEHICLE_SPEED` varchar(100) DEFAULT NULL,
`VEHICLE_LEN` varchar(100) DEFAULT NULL,
`PLATE_COLOR` varchar(100) DEFAULT NULL,
`VEHICLE_COLOR` varchar(100) DEFAULT NULL,
`VEHICLE_TYPE` varchar(100) DEFAULT NULL,
`VEHICLE_COLOR_DEPTH` varchar(100) DEFAULT NULL,
`PLATE_STATE` varchar(100) DEFAULT NULL,
`IMAGE_PATH` varchar(500) DEFAULT NULL,
`PLATE_IMAGE_PATH` varchar(500) DEFAULT NULL,
`TFS_ID` varchar(100) DEFAULT NULL,
`VEHICLE_STATE` varchar(100) DEFAULT NULL,
`RES_NUM1` varchar(100) DEFAULT NULL,
`RES_NUM2` varchar(100) DEFAULT NULL,
`RES_STR3` varchar(100) DEFAULT NULL,
`RES_STR4` varchar(100) DEFAULT NULL,
`VEHICLE_INFO_LEVEL` varchar(100) DEFAULT NULL,
`VEHICLE_LOGO` varchar(100) DEFAULT NULL,
`VEHICLE_SUBLOGO` varchar(100) DEFAULT NULL,
`VEHICLE_MODEL` varchar(100) DEFAULT NULL,
`province` varchar(100) DEFAULT NULL,
`PILOTSUNVISOR` varchar(100) DEFAULT NULL,
`sheet_sap` varchar(36) DEFAULT NULL,
PRIMARY KEY (`SHEETID`),
KEY `fvp_index` (`PASS_ID`) USING BTREE,
KEY `fvp_pt_index` (`PASS_TIME`) USING BTREE,
KEY `fvp_pl_index` (`PLATE_NO`) USING BTREE
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`fwq_vehicle_pass_201709`,`fwq_vehicle_pass_201710`,`fwq_vehicle_pass_201711`,`fwq_vehicle_pass_201712`,`fwq_vehicle_pass_201801`,`fwq_vehicle_pass_201802`,`fwq_vehicle_pass_201803`,`fwq_vehicle_pass_201804`,`fwq_vehicle_pass_201805`,`fwq_vehicle_pass_201806`,`fwq_vehicle_pass_201807`,`fwq_vehicle_pass_201808`,`fwq_vehicle_pass_201809`,`fwq_vehicle_pass_201810`,`fwq_vehicle_pass_201811`,`fwq_vehicle_pass_201812`,`fwq_vehicle_pass_201901`,`fwq_vehicle_pass_201902`,`fwq_vehicle_pass_201903`,`fwq_vehicle_pass_201904`,`fwq_vehicle_pass_201905`,`fwq_vehicle_pass_201906`,`fwq_vehicle_pass_201907`,`fwq_vehicle_pass_201908`,`fwq_vehicle_pass_201909`,`fwq_vehicle_pass_201910`,`fwq_vehicle_pass_201911`,`fwq_vehicle_pass_201912`) COMMENT='过车总表';
说明:
ENGINE=MRG_MyISAM
INSERT_METHOD=LAST 表示插入到最后创建的分表
UNION=(…) 表示所有分表
根据数据日期(此处用过车时间)自动进行分表,java代码如下:
片段1:拿到过车时间的年月:yyyyMM。如果与上一条不一致,则新建表xxxx_yyyyMM
Map<String, Object> map = newVehicle.get(i);
Timestamp time = (Timestamp) map.get("pass_time");
String pass_time = DateUtil.dateToStringYMDHMS(time);
map.put("pass_time", Timestamp.valueOf(pass_time));
//得到数据的年月
String yearMonth = DateUtil.dateToStringYM(time).replace("-", "");
//建表
createTable(conn, ps, tomorrowAndTodayPs, lastYearMonth,yearMonth);
lastYearMonth = yearMonth;
片段2:
/**
* 创建新分表
* @param conn
* @param ps
* @param tomorrowAndTodayPs
* @param lastYearMonth 上条保存的过车记录的过车年月
* @param yearMonth 本条保存的过车记录的年月
* @throws SQLException
*/
private void createTable(Connection conn, PreparedStatement ps,
PreparedStatement tomorrowAndTodayPs, String lastYearMonth,
String yearMonth) throws SQLException {
if(!lastYearMonth.equals(yearMonth)) {//如果不相等
if(!"".equals(lastYearMonth)) {//如果不是第一条,则表示夸月了,需要先提交
//#########
//1如果年月不一样, 则表示需要提交之前保存的信息。
ps.executeBatch();
tomorrowAndTodayPs.executeBatch();
//#########
}
// 2然后呢创建当前年月的分表
String tableName="fwq_vehicle_pass_"+yearMonth;
String checkTable = "show tables like '"+tableName+"' ";
Statement stmt = (Statement) conn.createStatement();
ResultSet resultSet = stmt.executeQuery(checkTable);
if (!resultSet.next()) {//不存在则新建
//创建分表
Statement stat = (Statement) conn.createStatement();
//创建表fwq_vehicle_pass_YYYYMM
stat.executeUpdate(getCreateTableSQL(tableName));
// 3 删除总表再创建总表
//先查询所有分表
String selectTables = "show tables like 'fwq_vehicle_pass_%'";
Statement stmts = (Statement) conn.createStatement();
ResultSet resultSets = stmts.executeQuery(selectTables);
//分表集合
StringBuffer ts = new StringBuffer("(");
int i = 0;
while (resultSets.next()) {
String table = resultSets.getString(1);
String index = table.substring(17);
if(NumberUtil.isNumeric(index)) {
if(i > 0) {
ts.append(",");
}
ts.append("`"+table+"`");
i++;
}
}
ts.append(")");
//创建主表的语句
String mainTable = "fwq_vehicle_pass";
String createMainTableSQL = getCreateMainTableSQL(mainTable, ts.toString());
//删除旧的主表
String dropTable = "DROP TABLE IF EXISTS " + mainTable;
Statement delSta = (Statement) conn.createStatement();
delSta.executeUpdate(dropTable);
//创建主表
Statement createSta = (Statement) conn.createStatement();
createSta.executeUpdate(createMainTableSQL);
}
}
}
private String getCreateTableSQL(String tableName) {
StringBuffer ss = new StringBuffer();
ss.append("CREATE TABLE `").append(tableName).append("` ( ");
ss.append(" `SHOWORDER` int(11) DEFAULT NULL, ");
ss.append(" `SHEETNAME` varchar(200) DEFAULT NULL, ");
ss.append(" `SHEETID` varchar(36) NOT NULL, ");
ss.append(" `DATATYPE` int(11) DEFAULT NULL, ");
ss.append(" `DATASTATUSID` int(11) DEFAULT '1', ");
ss.append(" `LASTOPENTIME` varchar(20) DEFAULT NULL, ");
ss.append(" `OPENER` varchar(20) DEFAULT NULL, ");
ss.append(" `OPENERID` varchar(36) DEFAULT NULL, ");
ss.append(" `PATTERNID` varchar(36) DEFAULT NULL, ");
ss.append(" `CREATEDATE` varchar(30) DEFAULT NULL, ");
ss.append(" `EDITUSERID` varchar(36) DEFAULT NULL, ");
ss.append(" `CREATEORGID` varchar(36) DEFAULT NULL, ");
ss.append(" `EDITUSER` varchar(20) DEFAULT NULL, ");
ss.append(" `CREATEUSERID` varchar(36) DEFAULT NULL, ");
ss.append(" `CREATEUSER` varchar(20) DEFAULT NULL, ");
ss.append(" `CREATEDEPT` varchar(80) DEFAULT NULL, ");
ss.append(" `CREATEORG` varchar(80) DEFAULT NULL, ");
ss.append(" `CREATEDEPTID` varchar(36) DEFAULT NULL, ");
ss.append(" `PASS_ID` bigint(255) DEFAULT NULL, ");
ss.append(" `CROSSING_ID` varchar(100) DEFAULT NULL, ");
ss.append(" `LANE_NO` varchar(100) DEFAULT NULL, ");
ss.append(" `DIRECTION_INDEX` varchar(100) DEFAULT NULL, ");
ss.append(" `PLATE_NO` varchar(100) DEFAULT NULL, ");
ss.append(" `PLATE_TYPE` varchar(100) DEFAULT NULL, ");
ss.append(" `PASS_TIME` datetime DEFAULT NULL, ");
ss.append(" `VEHICLE_SPEED` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_LEN` varchar(100) DEFAULT NULL, ");
ss.append(" `PLATE_COLOR` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_COLOR` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_TYPE` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_COLOR_DEPTH` varchar(100) DEFAULT NULL,");
ss.append(" `PLATE_STATE` varchar(100) DEFAULT NULL, ");
ss.append(" `IMAGE_PATH` varchar(500) DEFAULT NULL, ");
ss.append(" `PLATE_IMAGE_PATH` varchar(500) DEFAULT NULL, ");
ss.append(" `TFS_ID` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_STATE` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_NUM1` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_NUM2` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_STR3` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_STR4` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_INFO_LEVEL` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_LOGO` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_SUBLOGO` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_MODEL` varchar(100) DEFAULT NULL, ");
ss.append(" `province` varchar(100) DEFAULT NULL, ");
ss.append(" `PILOTSUNVISOR` varchar(100) DEFAULT NULL, ");
ss.append(" `sheet_sap` varchar(36) DEFAULT NULL, ");
ss.append(" PRIMARY KEY (`SHEETID`), ");
ss.append(" KEY `fvp_index` (`PASS_ID`) USING BTREE, ");
ss.append(" KEY `fvp_pt_index` (`PASS_TIME`) USING BTREE, ");
ss.append(" KEY `fvp_pl_index` (`PLATE_NO`) USING BTREE ");
ss.append(") ENGINE=MyISAM DEFAULT CHARSET=utf8; ");
return ss.toString();
}
/**
* 创建主表
* @param tableName 主表名称
* @param tables 从表集合
* @return
*/
private String getCreateMainTableSQL(String tableName, String tables) {
StringBuffer ss = new StringBuffer();
ss.append("CREATE TABLE `").append(tableName).append("` ( ");
ss.append(" `SHOWORDER` int(11) DEFAULT NULL, ");
ss.append(" `SHEETNAME` varchar(200) DEFAULT NULL, ");
ss.append(" `SHEETID` varchar(36) NOT NULL, ");
ss.append(" `DATATYPE` int(11) DEFAULT NULL, ");
ss.append(" `DATASTATUSID` int(11) DEFAULT '1', ");
ss.append(" `LASTOPENTIME` varchar(20) DEFAULT NULL, ");
ss.append(" `OPENER` varchar(20) DEFAULT NULL, ");
ss.append(" `OPENERID` varchar(36) DEFAULT NULL, ");
ss.append(" `PATTERNID` varchar(36) DEFAULT NULL, ");
ss.append(" `CREATEDATE` varchar(30) DEFAULT NULL, ");
ss.append(" `EDITUSERID` varchar(36) DEFAULT NULL, ");
ss.append(" `CREATEORGID` varchar(36) DEFAULT NULL, ");
ss.append(" `EDITUSER` varchar(20) DEFAULT NULL, ");
ss.append(" `CREATEUSERID` varchar(36) DEFAULT NULL, ");
ss.append(" `CREATEUSER` varchar(20) DEFAULT NULL, ");
ss.append(" `CREATEDEPT` varchar(80) DEFAULT NULL, ");
ss.append(" `CREATEORG` varchar(80) DEFAULT NULL, ");
ss.append(" `CREATEDEPTID` varchar(36) DEFAULT NULL, ");
ss.append(" `PASS_ID` bigint(255) DEFAULT NULL, ");
ss.append(" `CROSSING_ID` varchar(100) DEFAULT NULL, ");
ss.append(" `LANE_NO` varchar(100) DEFAULT NULL, ");
ss.append(" `DIRECTION_INDEX` varchar(100) DEFAULT NULL, ");
ss.append(" `PLATE_NO` varchar(100) DEFAULT NULL, ");
ss.append(" `PLATE_TYPE` varchar(100) DEFAULT NULL, ");
ss.append(" `PASS_TIME` datetime DEFAULT NULL, ");
ss.append(" `VEHICLE_SPEED` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_LEN` varchar(100) DEFAULT NULL, ");
ss.append(" `PLATE_COLOR` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_COLOR` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_TYPE` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_COLOR_DEPTH` varchar(100) DEFAULT NULL,");
ss.append(" `PLATE_STATE` varchar(100) DEFAULT NULL, ");
ss.append(" `IMAGE_PATH` varchar(500) DEFAULT NULL, ");
ss.append(" `PLATE_IMAGE_PATH` varchar(500) DEFAULT NULL, ");
ss.append(" `TFS_ID` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_STATE` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_NUM1` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_NUM2` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_STR3` varchar(100) DEFAULT NULL, ");
ss.append(" `RES_STR4` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_INFO_LEVEL` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_LOGO` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_SUBLOGO` varchar(100) DEFAULT NULL, ");
ss.append(" `VEHICLE_MODEL` varchar(100) DEFAULT NULL, ");
ss.append(" `province` varchar(100) DEFAULT NULL, ");
ss.append(" `PILOTSUNVISOR` varchar(100) DEFAULT NULL, ");
ss.append(" `sheet_sap` varchar(36) DEFAULT NULL, ");
ss.append(" PRIMARY KEY (`SHEETID`), ");
ss.append(" KEY `fvp_index` (`PASS_ID`) USING BTREE, ");
ss.append(" KEY `fvp_pt_index` (`PASS_TIME`) USING BTREE, ");
ss.append(" KEY `fvp_pl_index` (`PLATE_NO`) USING BTREE ");
ss.append(") ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=").append(tables);
ss.append(";");
return ss.toString();
}