当前位置：首页 > news >正文

mysql(51) : 大数据导出为insert

news 2025/9/17 23:27:45

代码


import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;public class 大数据导出为insert {public static Mysql8Instance m;public static List<String> tables = new ArrayList<>();private static Integer onCount = 10000;public static String search = "";public static void main(String[] args) throws Exception {大数据导出为insert t = new 大数据导出为insert();t.m = new Mysql8Instance("127.0.0.1", 3306, "test", "root", "123456");t.m.setReturnColumnName(true);t.tables.add("test");t.search = " where 1=1 ";t.handle();}public static void handle() throws Exception {long t = System.currentTimeMillis();long totals = 0;for (String table : tables) {long currentTimeMillis = System.currentTimeMillis();File file = new File(table + ".sql");// TODO 先删除再创建文件，避免文件有其他内容file.delete();file.createNewFile();BufferedWriter output = new BufferedWriter(new FileWriter(file, true));// true,则追加写入text文本// 字段List<List<String>> fs = m.query("select\n" +"column_name  from information_schema.columns\n" +"where table_schema = '" + m.getDatabase() + "'\n" +"and table_name = '" + table + "' ; ");fs.remove(0);StringBuffer fileds = new StringBuffer();for (List<String> f : fs) {fileds.append("`").append(f.get(0)).append("`,");}fileds.delete(fileds.length() - 1, fileds.length());String title = "insert into `" + m.getDatabase() + "`.`" + table + "` (" + fileds + ")values ";// 内容long total = 0;int start = 0;List<List<String>> rs;while ((rs = getData(m, "SELECT " + fileds + " FROM    " + table + " " + search + "  limit " + start + "," + onCount)).size() > 1) {rs.remove(0);System.out.println("导出数据 ,limit:[" + start + "," + onCount + "]");List<List<List<String>>> lists = null;if (rs.size() > 900) {lists = averageAssign(rs, 900);} else {lists = new ArrayList<>();lists.add(rs);}for (List<List<String>> list : lists) {output.write(title);output.write("\r\n");// 换行for (int i = 0; i < list.size(); i++) {StringBuffer c = new StringBuffer();for (String s : list.get(i)) {if (s == null) {c.append("null,");} else {c.append("'").append(s).append("',");}}c.delete(c.length() - 1, c.length());output.write("(" + c + ")");if (i == (list.size() - 1)) {output.write(";\r\n");// 换行} else {output.write(",\r\n");// 换行}}}total += rs.size();start += onCount;}output.flush();output.close();totals += total;System.out.println("[" + table + "]数据导出完成,数据量:" + total + ", 耗时:" + getHaoShi(System.currentTimeMillis() - currentTimeMillis));}m.close();System.out.println("所有表数据导出完成,表数:" + tables.size() + ",总数量:" + totals + ", 耗时:" + getHaoShi(System.currentTimeMillis() - t));}public static List<List<String>> getData(Mysql8Instance m, String sql) {return m.query(sql);}public static <T> List<List<T>> averageAssign(List<T> source, int n) {List<List<T>> result = new ArrayList<List<T>>();int remainder = source.size() % n;  //先计算出余数int number = source.size() / n;  //然后是商int offset = 0;//偏移量（用以标识加的余数）for (int i = 0; i < n; i++) {List<T> value;if (remainder > 0) {value = source.subList(i * number + offset, (i + 1) * number + offset + 1);remainder--;offset++;} else {value = source.subList(i * number + offset, (i + 1) * number + offset);}result.add(value);}return result;}/*** 计算耗时** @param t 毫秒* @return*/public static String getHaoShi(double t) {double d7 = t / 1000.0 / 60 / 60 / 24 / 30 / 12 / 100;if (d7 > 1) return round(d7, 1) + "纪元";double d6 = t / 1000.0 / 60 / 60 / 24 / 30 / 12;if (d6 > 1) return round(d6, 1) + "年";double d5 = t / 1000.0 / 60 / 60 / 24 / 30;if (d5 > 1) return round(d5, 1) + "月";double d4 = t / 1000.0 / 60 / 60 / 24;if (d4 > 1) return round(d4, 1) + "天";double d3 = t / 1000.0 / 60 / 60;if (d3 > 1) return round(d3, 1) + "小时";double d2 = t / 1000.0 / 60;if (d2 > 1) return round(d2, 1) + "分钟";double d1 = t / 1000.0;if (d1 > 1) return round(d1, 1) + "秒";return t + "毫秒";}public static String join(List<String> list, String separator) {Iterator<String> iterator = list.iterator();if (iterator == null) {return null;} else if (!iterator.hasNext()) {return "";} else {Object first = iterator.next();if (!iterator.hasNext()) {return Objects.toString(first, "");} else {StringBuilder buf = new StringBuilder(256);if (first != null) {buf.append(first);}while (iterator.hasNext()) {if (separator != null) {buf.append(separator);}Object obj = iterator.next();if (obj != null) {buf.append(obj);}}return buf.toString();}}}public static Double round(Double data, int amount) {if (data == null)return null;//利用BigDecimal来实现四舍五入.保留一位小数double result = new BigDecimal(data).setScale(amount, BigDecimal.ROUND_HALF_UP).doubleValue();//1代表保留1位小数,保留两位小数就是2,依此累推//BigDecimal.ROUND_HALF_UP 代表使用四舍五入的方式return result;}/**CREATE TABLE test.`test1` (`pkid` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',`sys_create_time` datetime DEFAULT NULL COMMENT '创建时间',`sys_modify_time` datetime DEFAULT NULL COMMENT '修改时间',`lng` double DEFAULT NULL COMMENT '经度',`name` varchar(100) DEFAULT NULL COMMENT '名称',`time` bigint DEFAULT NULL COMMENT '时间',`age` varchar(100) DEFAULT NULL COMMENT '年龄',`speed` double DEFAULT NULL COMMENT '速度',`lat` double DEFAULT NULL COMMENT '维度',`sdate` int(11) NOT NULL COMMENT '小时(分区键)',PRIMARY KEY (`pkid`,`sdate`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表1';*/
}