Javamysql batch import data automatic update date_mysql

Javamysql batch import data automatic update date bitsCN.com

During this period, we have been using hadoop and pig to operate on some data. The new data volume generated every week is more than 1 million. for large volumes of data (more than 10 million), insert and update operations, if the insert operation is slow, I tested that it would take more than 20 hours to insert 30 million records using insert (no cluster is used ).

To speed up data insertion and update, mysql provides the load command to greatly speed up data insertion. on my machine, the load operation is about 10 times faster than insert.

The official load command format is as follows:

 LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'    [REPLACE | IGNORE]    INTO TABLE tbl_name    [CHARACTER SET charset_name]    [{FIELDS | COLUMNS}        [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,...]

1. if you want to insert data locally, run the following command:

Load data local infile 'name of the data source to be inserted 'into table name of the table to be inserted

2. if you update an existing record, run the following command:

Load data local infile 'name of the data source to be inserted 'replace into table name of the table to be inserted

In some scenarios, the related time needs to be updated when each piece of data is updated. If no update is made, the original time is saved. There are two ways to solve this problem:

Method 1. this method is more common, that is, when inserting or updating, the current time is passed into the database and updated together.

Method 2: When creating a table, run the following command to create a column named "time ":

`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

BitsCN.com