数据库分表

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