1

I need to execute large amount of the inserts to the mysql table over mysql client/server protocol via prepared statement. How to do it better? Is there only one way to do it to build a query, something like this:

insert into my_table (?, ?), VALUES (1,2), (..., ...)....

and send it or maybe mysql client/server protocol support batch inserts? I've found COM_STMT_SEND_LONG_DATA in the mysql documentation, but I'm not sure that it is what i need.

I saw example on java: Java: Insert multiple rows into MySQL with PreparedStatement and there is addBatch method. Does this method just builds one insert request with many VALUES clauses or it uses something from the mysql protocol?

Thank you.

Community
  • 1
  • 1
0xAX
  • 20,957
  • 26
  • 117
  • 206
  • 1
    [LOAD DATA LOCAL INFILE](https://dev.mysql.com/doc/refman/5.1/en/load-data.html) might be helpful – zedfoxus Jun 11 '15 at 13:10

2 Answers2

2

MySQL server supports batch inserts, yes. You can do it like that:

INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
Thanos Markou
  • 2,587
  • 3
  • 25
  • 32
  • What if i need to execute 1K inserts, 10K inserts? – 0xAX Jun 11 '15 at 13:13
  • I think that the limit is 1 million inserts, but that can be changed somehow, if you want even more. – Thanos Markou Jun 11 '15 at 13:15
  • max_allowed_packet is the variable that puts an upper bound on the size of your query. The actual query limit will be slightly less due to the protocol overhead. Default is 16M, you can set it locally per connection with ```set max_allowed_packet=``` from your client code. – Sasha Pachev Nov 06 '15 at 19:15
0

Try something like this:

insert into members(memberID,CompanyName,Location) values (1,'John Doe','Pittsburgh'), (2,'Jane Sikes','New York'), (3,'Bob Smith','Los Angeles');

glennw
  • 58
  • 5