MySQL通过MERGE分表

最新推荐文章于 2023-03-10 15:20:20 发布

qq_41273205

最新推荐文章于 2023-03-10 15:20:20 发布

阅读量150

点赞数

分类专栏： java MySQL

本文链接：https://blog.csdn.net/qq_41273205/article/details/103636803

版权

java 同时被 2 个专栏收录

19 篇文章 1 订阅

订阅专栏

MySQL

5 篇文章 0 订阅

订阅专栏

#####创建分表，按月

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();
		
	}

qq_41273205

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL通过MERGE分表

#####创建分表，按月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,...
复制链接

扫一扫