Mysql大表数据迁移

最新推荐文章于 2024-07-10 15:36:37 发布

探子

最新推荐文章于 2024-07-10 15:36:37 发布

阅读量542

点赞数

分类专栏：工具数据库文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_33335577/article/details/129176966

版权

数据库同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

工具

4 篇文章 0 订阅

订阅专栏

该代码示例展示了一个使用Java进行多线程数据迁移的程序，依赖于MySQL的JDBC驱动。程序将数据从一个表（sys_api_log_bar）分批迁移到另一个表（sys_api_log），利用CountDownLatch进行同步，并通过MysqlUtil工具类执行SQL更新操作。

摘要由CSDN通过智能技术生成

依赖

  <dependencies>
	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>8.0.32</version>
	</dependency>
  </dependencies>

项目类

 
import java.math.BigDecimal;
import java.sql.Connection;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
 
public class Test1 {
	
	// 线程数
	public static int thread = 1;
	// 批量执行条数
	public static int limit = 100000;
	// 迁移总数量
	public static int total = 2300000;
	private static LinkedList<String>[] sqlThread = new LinkedList[thread];
	
	private static CountDownLatch downLatch = new CountDownLatch(thread);
	
	
    public static void main(String[] args) throws Exception {
    	int index = 0;
    	int size = (total /limit)+1;
    	for (int i = 0; i < size; i++) {
    		String sql = getSql(index);
    		index+=limit;
    		int sqlThreadIndex = i%thread;
    		if (sqlThread[sqlThreadIndex] == null) {
    			sqlThread[sqlThreadIndex] = new LinkedList<String>();
			}
    		sqlThread[sqlThreadIndex].addLast(sql);
		}
    	
    	MysqlUtil.showLog("数据迁移 准备开始");
		long begin = System.currentTimeMillis();
    	for (int i = 0; i < thread; i++) {
    		Runnable runnable = getRunnable(sqlThread[i]);
			Thread thread = new Thread(runnable);
			thread.start();
		}
    	downLatch.await();
		long end = System.currentTimeMillis();
		MysqlUtil.showLog("数据迁移 消耗时间 [" + new BigDecimal(((end - begin)/1000)/60f) + "]分钟 [" + (end - begin)/1000 + "]秒 [" + (end - begin) + "]毫秒");
    	System.err.println();
    	
    }
    
    
    public static Runnable getRunnable(final List<String> sqls) {
    	return new Runnable() {
    		public void run() {
    			try {
      				Connection connection = MysqlUtil.getTmpConnection();
      				for (String sql : sqls) {
      					MysqlUtil.executeUpdate(connection, sql);
					}
      				connection.close();
				} catch (Exception e) {
					e.printStackTrace();
				}finally {
					downLatch.countDown();
				}
    		}
    	};
    }
    
    public static String getSql(int index) {
    	String sql = "INSERT INTO sys_api_log "
    			+ "(id, tenant_id, user_id, user_name, ip, city, module, url, `year`, `month`, `day`, `time`, status) "
    			+ "(select id, tenant_id, user_id, user_name, ip, city, module, url, `year`, `month`, `day`, `time`, status from sys_api_log_bar "
    			+ "limit "+index+","+limit+")";
    	return sql;
    }
    
}


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class MysqlUtil {

	private static final String TMP = "jdbc:mysql://127.0.0.1:3306/db_temp?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8";
	private static final String user = "root";
	private static final String password = "root";
	
	public static Connection getTmpConnection() throws SQLException, ClassNotFoundException {
		return getConnection(TMP);
	}
	
	
	public static void executeUpdate(Connection connection, String sql) throws SQLException {
		Statement statement = connection.createStatement();
		long begin = System.currentTimeMillis();
		showLog("执行SQL[" + sql + "]");
		int i = statement.executeUpdate(sql);
		long end = System.currentTimeMillis();
		showLog("影响行数[" + i + "]  消耗时间 [" + (end - begin)/1000 + "]秒 [" + (end - begin) + "]毫秒");
		statement.close();
	}
	
	private static Connection getConnection(String url) throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		// 第二步连接数据库
		return DriverManager.getConnection(url, user, password);
	}

	public static void showLog(String msg) {
		Date currentTime = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = formatter.format(currentTime);
		System.err.println("["+dateString+"][" + Thread.currentThread().getName() + "] "+msg);
	}

}