Use of Mysql load data
In databases, the most common way to write data is to write through SQL INSERT, and in addition, to restore the database through backup files. This backup file is a SQL script in MySQL, but it is actually executed in batch INSERT statements.
In practice, two types of problems are often encountered: one is data import, such as importing data from word, excel table or txt documents (this data generally comes from documents entered by non-technical personnel through OFFICE tools); the other is data exchange, such as data exchange between MySQL, Oracle, and DB2 databases.
This faces a problem: there are differences in database SQL scripts, and SQL exchange is more troublesome. However, almost all databases support text data import (LOAD) export (EXPORT). Using this, we can solve the data exchange and import problems mentioned above.
MySQL's LOAD DATAINFILE statement is used to read rows from a text file at high speed and load them into a table. The file name must be a literal string. The following is a sample description of MySQL5, which illustrates how to use MySQL's LOADDATA command to implement text data import.
Note: The text mentioned here is text with a certain format, such as text branches, each line is separated by the same symbol, etc. So, there are many ways to obtain such text, such as saving word and excel tables into text, or a csv file.
In the project, the environment used is to quickly upload a csv file. The original system used the db2 database, and then a function similar to mysql's loaddata is called sysproc.db2load. However, loaddata cannot be used in mysql stored procedures. This method is called in java code when taking the method.
Implementation example:
Prepare the test form
SQL is as follows:
USE test;CREATE TABLE `test` (`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,`a` int(11) NOT NULL,`b` bigint(20) UNSIGNED NOT NULL,`c` bigint(20) UNSIGNED NOT NULL,`d` int(10) UNSIGNED NOT NULL,`e` int(10) UNSIGNED NOT NULL,`f` int(10) UNSIGNED NOT NULL,PRIMARY KEY (`id`),KEY `a_b` (`a`, `b`)) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8
The Java code is as follows:
package com.seven.dbTools.DBTools;import org.apache.log4j.Logger;import org.springframework.jdbc.core.JdbcTemplate;import java.io.ByteArrayInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import javax.sql.DataSource;/** * @author seven * @since 07.03.2013 */public class BulkLoadData2MySQL { private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class); private JdbcTemplate jdbcTemplate; private Connection conn = null; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public static InputStream getTestDataInputStream() { StringBuilder builder = new StringBuilder(); for (int i = 1; i <= 10; i++) { for (int j = 0; j <= 10000; j++) { builder.append(4); builder.append("/t"); builder.append(4 + 1); builder.append("/t"); builder.append(4 + 2); builder.append("/t"); builder.append(4 + 3); builder.append("/t"); builder.append(4 + 4); builder.append("/t"); builder.append(4 + 5); builder.append("/n"); } } byte[] bytes = builder.toString().getBytes(); InputStream is = new ByteArrayInputStream(bytes); return is; } /** * * load bulk data from InputStream to MySQL */ public int bulkLoadFromInputStream(String loadDataSql, InputStream dataStream) throws SQLException { if (dataStream == null) { logger.info("InputStream is null ,No data is imported"); return 0; } conn = jdbcTemplate.getDataSource().getConnection(); PreparedStatement statement = conn.prepareStatement(loadDataSql); int result = 0; if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) { com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement.class); mysqlStatement.setLocalInfileInputStream(dataStream); result = mysqlStatement.executeUpdate(); } return result; } public static void main(String[] args) { String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)"; InputStream dataStream = getTestDataInputStream(); BulkLoadData2MySQL dao = new BulkLoadData2MySQL(); try { long beginTime = System.currentTimeMillis(); int rows = dao.bulkLoadFromInputStream(testSql, dataStream); long endTime = System.currentTimeMillis(); logger.info("importing " + rows + " rows data into mysql and cost " + (endTime - beginTime) + " ms!"); } catch (SQLException e) { e.printStackTrace(); } System.exit(1); }}hint:
The code in the example uses the setLocalInfileInputStream method, which will directly ignore the file name and import the IO stream into the database. In actual implementation, you can also upload the file to the server, then 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, LOAD DATA INFILE syntax is attached
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...]]]
Summarize
LOADDATA is a very useful command. It is faster to import data from a file than an insert statement. MySQL documentation says it is about 20 times faster. However, there are many options for commands, but most of them cannot be used. If you really need it, just read the official documentation when using it.