Java uses MySQL load data local infile to import large quantities of data into mysql

Use of MySQL load data

In the database, the most common way to write data is through SQL insert. In addition, the database is restored through backup files. This backup file is an SQL script in mysql, which actually executes batch insert statements.

In practice, we often encounter two kinds of problems: one is data import, such as importing data from word, Excel or TXT documents (these data are generally from documents entered by non-technical personnel through office tools); A kind of data exchange, such as data exchange between mysql, Oracle and DB2 databases.

There is a problem: there are differences in database SQL scripts, and SQL exchange is troublesome. However, almost all databases support text data import (load) and export (export). Using this, we can solve the data exchange and import problems mentioned above.

The load datainfo statement of MySQL is used to read rows from a text file at high speed and load them into a table. The file name must be a text string. Next, take mysql5 as an example to illustrate how to use the loaddata command of Mysql to import text data.

Note: the text mentioned here is a text with a certain format, for example, text branches, separated by the same symbol in each line, etc. There are many ways to obtain such text, such as saving word and excel tables as text, or a CSV file.

In the project, the environment used is to upload a CSV file quickly, the DB2 database used in the original system, and then invoke a function sysproc. similar to the loaddata of MysqL. db2load。 However, the stored procedure of loaddata in MySQL cannot be used. The method is invoked in Java code.

Examples of implementation:

Prepare test sheet

The SQL is as follows:

The Java code is as follows:

Tips:

The code in the example uses the setlocalinfileinputstream method, which will directly ignore the file name and directly import the IO stream into the database. In the actual implementation, you can also upload the file to the server, read the file, and then import the file. At this time, the local parameter of load data should be removed, and the file name should be the name of the complete absolute path.

Finally, the load data infile syntax is attached