根据年月动态分表

Mysql存储过程动态建表

DELIMITER $
CREATE PROCEDURE test_create_table (
	IN dept_emp_test  VARCHAR (30) # 表名
)
BEGIN
	# 利用concat拼接字符串函数，将复制表的sql赋值到变量test_create_table
	#  sql 语句里面的dept_emp  是作为被复制的表  
	set @test_create_table = concat('CREATE TABLE ',dept_emp_test," SELECt * FROM dept_emp WHERe 1=2;");
	# 预处理建表
	PREPARE test_create_table FROM @test_create_table;   
	# 执行sql
	EXECUTE test_create_table; 
END $

	@Scheduled(cron = " 0  */1  *  *  *  ?")
    public void testInsert()  {
        String curName = Thread.currentThread().getName() ;
        System.out.println("当前时间:"+ LocalDateTime.now(ZoneOffset.of("+8")).format(df)+" 开始插入数据 ===   线程名: "+curName);
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement    statement=null;
        System.out.println("开始插入数据");
        Long startTime = System.currentTimeMillis();
        try {
            String sql="INSERT INTO dept_emp(emp_no, dept_no,from_date,to_date) VALUES (?,?,?,?)";
            statement=connection.prepareStatement(sql);
            //批量操作必须关闭自动提交
            connection.setAutoCommit(false);
            for (int i = 1; i <=100000 ; i++) {
                statement.setInt(1,  i);
                statement.setString(2, "d00" + i);
                statement.setDate(3, new Date(System.currentTimeMillis()) );
                statement.setDate(4, new Date(System.currentTimeMillis()));
                statement.addBatch();//把数据放入缓存区
//            if(i%10000==0){
//                //刷新缓存区
//                statement.executeBatch();
//                statement.clearBatch();//清除之前的数据
//            }
        }
        //刷新缓存区
        statement.executeBatch();
        statement.close();
        connection.commit();//执行完后，手动提交事务
        connection.setAutoCommit(true);//在把自动提交打开
        Long endTime = System.currentTimeMillis();
        System.out.println("插入完毕,用时：" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源,倒关
            JDBCUtils.release(connection, statement, null);
        }
    }


 // @Scheduled(cron = "0 58 23 L * ? ")
    @Scheduled(cron = " 0  */5  *  *  * ?")
    public void testCreate() {
        System.out.println("开始复制表");
        String curName = Thread.currentThread().getName() ;
        System.out.println("当前时间:"+ LocalDateTime.now(ZoneOffset.of("+8")).format(df)+" 数据库    复制    ==========  线程名: "+curName);
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement    statement=null;
        Long startTime = System.currentTimeMillis();
        DateTimeFormatter sdf = DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss");
        String s= LocalDateTime.now(ZoneOffset.of("+8")).format(sdf);
        s="dept_emp_"+s;
        String newTableName="dept_emp_copy";
        
        String sql = "{call test_create_table(?)}";
        Connection conn = null;
        CallableStatement call = null;
        PreparedStatement ps =null;
        try {
            //得到一个数据库连接
            conn = JDBCUtils.getConnection();
            //通过连接创建出statement
            call = conn.prepareCall(sql);
            call.setString(1, newTableName);
            call.execute();
            
            String sql1 = " rename table dept_emp  to "+ s;
            ps=  conn.prepareStatement(sql1);
            ps.execute();
            
            Thread.sleep(1000);
            String sql2 ="rename table "+newTableName +" to dept_emp";
            ps=  conn.prepareStatement(sql2);
            ps.execute();
            Long endTime = System.currentTimeMillis();
            System.out.println("复制完毕,用时：" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //关闭连接，释放资源
            JDBCUtils.release(conn, ps, null);
        }

    }