Use load data Infile in mysql, mysqlinfile

Use load data Infile in mysql, mysqlinfile
The speed is 20 times faster than insert. Share java program operations.

package com.mysql.csv;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;public class Competition { static Connection  conn = null;     static String driver = "com.mysql.jdbc.Driver";     static String url = "jdbc:mysql://127.0.0.1:3306/nerd_1";      public static void main(String[] args) throws ClassNotFoundException, SQLException {     Class.forName(driver);         conn =  DriverManager.getConnection(url, "root", "1234");         String sql = "LOAD DATA INFILE 'E://baidu//cp9.csv' REPLACE INTO TABLE baidu_34000_0_competition CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' ignore 1 lines";         PreparedStatement  pstmt = conn.prepareStatement(sql);     pstmt.execute();       conn.close(); } }

Batch insert conditions for specific reference documentation: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data
How to load data infile in mysql ????????????????????????

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,...]

Mysql load data infile Efficiency

In fact, your problem is a core issue in mysql, that is, the backup and recovery of mysql Data.
You can use three methods
1. Use SQL statements to import and export data
2. Use mysqldump and mysqlimport tools
3. copy the data file for cold backup
If you are talking about the details, just give the points. Then I will talk about the details.

I. use SQL statements to back up and restore mysql

You can use the select into outfile statement to back up DATA and use the load data infile statement to restore DATA. This method can only export data content, excluding the table structure. If the structure file of the table is damaged, you must restore the structure of the original table first.
Syntax:
SELECT * INTO 'file _ name' FROM tbl_name
Load data [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt '[REPLACE | IGNORE]
Into table tbl_name
SELECT... into outfile 'file _ name' SELECT statement writes the selected row INTO a file. Files are created on the server host and cannot already exist (no matter what it is, this can prevent database tables and files such as "/etc/passwd" from being damaged ). SELECT... into outfile is the inverse operation of load data infile.
The load data infile statement reads a table from a text file at a high speed. If you specify the LOCAL keyword, read the file from the client host. If LOCAL is not specified, the file must be on the server. (LOCAL is available in MySQL 3.22.6 or a later version .)
For security reasons, when reading text files on the server, the files must be in the database directory or can be read by everyone. In addition, to use load data infile for files on the server, you must have the file permission on the server host. To use this select into outfile statement, you must have the FILE permission on the server host.
To avoid repeated records, you need a primary key or UNIQUE index in the table. When a new record on a unique index value is duplicated with an old record, the REPLACE keyword allows the old record to be replaced by a new record. If you specify IGNORE, skip the input of duplicate rows of existing rows with unique indexes. If you do not specify any option, an error occurs when the duplicate index value is found, and the remaining part of the text file is ignored.
If you specify the keyword LOW_PRIORITY, the execution of the load data statement is postponed until no other customers read the table.
Using LOCAL will be slower than allowing the server to directly access files, because the file content must be transmitted from the client host to the server host. On the other hand, you do not need the file Permission to load local files. If you use the LOCAL keyword to load data from a LOCAL file, the server cannot stop file transmission during the operation. Therefore, the default behavior is as if IGNORE was specified.
When searching for files on the server host, the server uses the following rules:
If the server returns an absolute path name, the server uses this path name.
If the relative path name of one or more front parts is provided, the server searches for files in the data directory of the server relative to the server.
If a file name without a front-end component is provided, the server searches for a file in the database directory of the current database.
Assume that the table tbl_name has a primary key or UNIQUE index. The process of backing up a data table is as follows:
1. Lock the data table to avoid updating the table during the backup process.
Mysql> lock tables read tbl_name;
For more information about table locking, see the next chapter.
2. Export data
Mysql & g ...... remaining full text>