mysql快速从文件中导入数据load data infile,以及相应的导出文件.(java代码自动生成文件)

导出文件

登录mysql后
这个路径需要mysql有操作权限

SELECT * FROM xxx_history
INTO OUTFILE "/var/lib/mysql-files/xxx_history.txt" 

导入文件至mysql

登录mysql后
这个路径需要mysql有操作权限

load data infile '/var/lib/mysql-files/xxx_history.txt'
replace into table xxx_history

使用IGNORE对于冲突的数据丢弃掉,比如可以把txt中主键重复的数据过滤掉

sql load data infile "/test.txt" ignore into table1 lines
terminated by '\r\n' (colunm1,colunm2,...) 

基本用法（导入文件test.txt到table1表中，txt文件中的行分隔符为\r\n，默认tab键为字段分隔符，txt文件中的每个字段按顺序对应column1、column2，。。。导入表中）
如果字段分隔符不是tab，可加入：fields terminated by ‘分隔符’

load data infile "/test.txt" into table1 
lines terminated by '\r\n' (colunm1,colunm2,...)

如果txt文件中有某一列不想导入表中，可以添加@+自定义变量（如@abandon）来丢弃这一列的数据：

load data infile "/test.txt" ignore into table1 
lines terminated by '\r\n' (colunm1，@abandon,colunm2,...)

如果需要对txt文件中的某一列进行加工后再导入表中，可以使用@column，set 的方式进行操作：

load data infile "/test.txt" ignore into table1 
lines terminated by '\r\n' (colunm1，@abandon,colunm2,...)

下面来介绍一下上面的操作

启动mysql的时候使用了--secure-file-priv这个参数，这个参数的主要目的就是限制LOAD DATA INFILE或者SELECT INTO OUTFILE之类文件的目录位置。可以使用

SELECT @@global.secure_file_priv;

查看当前设置的路径，默认为

/var/lib/mysql-files

上面介绍了手动的操作,作为开发人员对与这种重复的事情,当然要让计算机来代劳了

我的思路是引入JDBC包,然后用在代码里查出库中所有的表,然后让代码代替我们去生成这些个文件,再用代码把文件导入进去

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>

开始代码自动生成文件

package com.felix.springlifedemo.service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GenerateSqlFile {

    public static void main(String[] args) {
        try {
            //加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            //URL指向要访问的数据库名
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncodeing=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "10086");
            if(!connection.isClosed()){
                System.out.println("Succeeded connecting to the Database!");
            }else {
                return;
            }

            //创建statement类对象，用来执行SQL语句！！
            Statement statement = connection.createStatement();
            String sql = "show tables;";
            ResultSet resultSet = statement.executeQuery(sql);

            List<String> tableNames = new ArrayList<>();
            //因为我这demo的数据库是test所以这里列名是Tables_in_test
            while (resultSet.next()){
                String tableName = resultSet.getString("Tables_in_test");
                System.out.println(tableName);
                tableNames.add(tableName);
            }

            //使用 SELECT @@global.secure_file_priv; 查看路径,默认是将文件生成到 /var/lib/mysql-files 路径下
            tableNames.forEach(el -> {
                String generateFileSql = "SELECT * FROM "+ el +" INTO OUTFILE \"/var/lib/mysql-files/"+ el +".txt\" ";
                try {
                    statement.executeQuery(generateFileSql);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });

            resultSet.close();
            statement.close();
            connection.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

执行的时候可能会报没有权限的错误,这个时候一般就是默认的/var/lib/mysql-files路径的权限不对了,将权限改成mysql的权限,这里大家不要照着固定copy,要看下自己mysql文件的权限是哪个用户的

修改用户和用户组的权限demo

chown mysql:mysql ./mysql-files/

文件生成成功

下面代码自动导入文件

先把上面生成的文件批量copy到需要导入数据的数据库的默认路径/var/lib/mysql-files下一般都是这个路径
查看路径sql语句

SELECT @@global.secure_file_priv;

导入前先把数据库中的表结构用navicat先同步一遍

然后执行代码

package com.felix.springlifedemo.service.mysql;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LoadFile {

    public static void main(String[] args) {
        //加载驱动程序
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            //URL指向要访问的数据库名
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncodeing=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "10086");
            if(!connection.isClosed()){
                System.out.println("Succeeded connecting to the Database!");
            }else {
                return;
            }

            //创建statement类对象，用来执行SQL语句！！
            Statement statement = connection.createStatement();
            String sql = "show tables;";
            ResultSet resultSet = statement.executeQuery(sql);

            List<String> tableNames = new ArrayList<>();
            //因为我这demo的数据库是test所以这里列名是Tables_in_test
            while (resultSet.next()){
                String tableName = resultSet.getString("Tables_in_test");
                System.out.println(tableName);
                tableNames.add(tableName);
            }

            resultSet.close();

            tableNames.forEach(el -> {

                String generateFileSql = "load data infile '/var/lib/mysql-files/"+ el +".txt' replace into table "+ el;
                try {
                    statement.executeQuery(generateFileSql);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });

            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

导入成功,搞定.然后消灭之前到导入文件.嘻嘻