Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to insert 10 million pieces of data into the MySQL database table in 88 seconds. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The database I use is mysql Database version 5.7.

First of all, prepare your own database tables.

In fact, I encountered some problems when inserting 10 million pieces of data. Now let's solve them first. At the beginning, I reported an error when I inserted 1 million pieces of data. The message on the console is as follows:

Com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4232009 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.

The above error occurs because the max_allowed_packet configuration of the database table is not large enough, because the default is 4m. Later, I adjusted it to 100m and did not report it wrong.

Set global max_allowed_packet = 100,1024,1024 *

Remember, the set value that you can only see after you log in to the database after you set it up.

Show VARIABLES like'% max_allowed_packet%'

The code is as follows:

Package insert;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Date;import com.mysql.jdbc.PreparedStatement;public class InsertTest {public static void main (String [] args) throws ClassNotFoundException, SQLException {final String url = "jdbc:mysql://127.0.0.1/teacher"; final String name = "com.mysql.jdbc.Driver"; final String user = "root"; final String password = "123456" Connection conn = null; Class.forName (name); / / specify connection type conn = DriverManager.getConnection (url, user, password); / / get connection if (connate = null) {System.out.println ("get connection successful"); insert (conn);} else {System.out.println ("get connection failed") } public static void insert (Connection conn) {/ / start time Long begin = new Date () .getTime (); / / sql prefix String prefix = "INSERT INTO t_teacher (id,t_name,t_password,sex,description,pic_url,school_name,regist_date,remark) VALUES"; try {/ / Save sql suffix StringBuffer suffix = new StringBuffer () / / set transaction to non-autocommit conn.setAutoCommit (false); / / it is better to PreparedStatement pst = (PreparedStatement) conn.prepareStatement ("") than st,pst; / / prepare to execute statement / / outer loop, total number of committed transactions for (int I = 1; I)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.