数据库分表

使用merge引擎来实现分表 

CREATE TABLE table1 ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) , PRIMARY KEY (`id`) ) ENGINE=MyISAM; CREATE TABLE table2 LIKE table1; //创建总表 CREATE TABLE tableAll LIKE table1; ALTER TABLE tableAll ENGINE=MERGE UNION=(table1,table2);

注意：在总表tableAll中会出现主键重复的情况，可以建立只有一个属性的表，自增来生成唯一主键；也可以手动生成一个ID，实现主键唯一的目的，生成主键代码链接。 但是这种分表方法有弊端：不支持事务，比较麻烦！  

使用视图来实现分表 

CREATE TABLE table1 ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(45) , PRIMARY KEY (`id`) ); CREATE TABLE table2 LIKE table1; //创建视图 (总表) create or REPLACE view tableAll as select * from table1 union select * from table2;

主键同样存在重复的问题：生成主键代码链接。

//分表：user_info_0、user_info_1、user_info_2 视图：user_info public static void createTable(JdbcTemplate jdbcTemplate, Integer tableNum) { try { Connection conn = jdbcTemplate.getDataSource().getConnection(); DatabaseMetaData dbMetaData = conn.getMetaData(); String tableName = "user_info"; List<String> list = new ArrayList<>(); if (tableNum > 0) { String tbName = tableName + "_" + tableNum; String sql = "CREATE table if not EXISTS " + tbName + " like " + tableName + "_0"; jdbcTemplate.execute(sql); } String tableName1 = tableName + "_%"; ResultSet rs = dbMetaData.getTables(null, null, tableName1, new String[]{"TABLE"}); while (rs.next()) { String string = rs.getString(3); list.add(string); } String sql2 = " create or REPLACE view " + tableName + " as "; for (int i = list.size() - 1; i >= 0; i--) { String s = list.get(i); if (i >= 0 && i < list.size() - 1) { sql2 = sql2 + " union select * from " + s; } else if (i == list.size() - 1) { sql2 = sql2 + " select * from " + s; } } jdbcTemplate.execute(sql2); } catch (Exception e) { e.printStackTrace(); } }