将海量数据插入Mysql数据库的最快方法[重复]

Question

This question already has an answer here:

• How to insert 20 million record into MySql database as fast as possible 4 answers

I actually have a list of 100,000 records which I'd like to insert into the MySQL database.

I have tried to insert them with foreach and simple INSERT INTO however it took a lot of time to insert even 100 row. Like 1 second / row.

IS there any method to insert these rows much faster?

</div>

Answer 1

Using one INSERT statement with multiple rows is faster than one INSERT statement per row. This will reduce calls to the database.

Example:

 INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Answer 2

Make packages:

INSERT INTO `table_name`(`column`) VALUES('value'),VALUES('value1'),VALUES('value2'), ..., VALUES('valuen');

documentation for insert

or export data to csv or other text format and use LOAD DATA, look here: load data by mysql client

Answer 3

fastest way to load large dataset is to use bulk loading interface. Refer Mysql docs: Bulk loader

Answer 4

The fastest way to use command line interface. You can use mysqldump util.

Answer 5

Group several similar INSERTs in one long INSERT with multiple VALUES lists to insert several rows at a time: query will be quicker due to fact that connection + sending + parsing a query takes 5-7 times of actual data insertion (depending on row size). If that is not possible, use START TRANSACTION and COMMIT, if your database is InnoDB, otherwise use LOCK TABLES — this benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed; in this case unlock your tables each 1000 rows or so to allow other threads access to the table.

From this article: http://www.ajaxline.com/node/2099

Answer 6

(only for mysql)

This makes a function to return random names. you can change it to fit yourself

CREATE FUNCTION `get_name`() RETURNS varchar(5) CHARSET utf8
BEGIN
    DECLARE chars varchar(255) default '里对非肺乔额及爱我动物地位三次幂动物';
    DECLARE str varchar(255) default '';
    DECLARE i int default 0;
    while i<3 do
        set str=concat(str,substring(chars, FLOOR(1 + RAND()*62), 1));
        set i=i+1;
    END while;
RETURN str;
END

make a procedure to achieve making lots of data

CREATE PROCEDURE `make_data`(size int)
BEGIN
    declare i int default 0;
    while i<size do
        insert table_name(name)value(get_name());
    end while;
END

call make_data(1000)[done]

If you use MySQL Workbench, you need to add function and procedure manually. If you use MySQL console, then you should add DELIMITER like below

DELIMITER $$
$$
CREATE PROCEDURE `make_data`(size int)
BEGIN
    declare i int default 0;
    while i<size+1 do
        insert table_name(name)value(get_name());
    end while;
END$$

DELIMITER ;