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

代码

?

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';

     */
}

Mysql8Instance类如下

mysql(30) : java管理mysql8(开发用轻量版)_java mysql8-CSDN博客