mysql - 如何批量插入数据库10W条数据

Question

sql语句能实现吗

Answer 1

Is there any other way besides sql? Generate the sql file and import it directly. 100,000 pieces of data are not too much.

Answer 2

Navicat Import Wizard, you deserve it

Answer 3

Use insert to batch insert, for example:

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

But you can’t insert too many items each time. For example, insert 100 items at a time, and then insert 1,000 times in total, which will be 100,000 items, and the speed will not be very slow

Answer 4

1. SQL statements can definitely be implemented, but inserting so much data will definitely be very slow.
2. It can also be achieved using the client tool SQLyog, just import it.

Answer 5

Let me give you what I summarized before:
--Declare a stored procedure (understood as a function)

delimiter ;;
create procedure myproc ()

begin
declare num int ;
set num = 1 ;
while num < 10 do
    insert into user (id, `name`, sex)
values
    ('', concat("name", num), 1) ;
set num = num + 1 ;
end
while ;

end;;

--Execute this function
call myproc();
--View the results of inserting data
select * from user;
--Delete this stored procedure
drop procedure myproc;