Cara menggunakan mysql bulk insert limit

Many times a lot of entries are to be made in the database using the same query, for example, to make a result card of students, instead of inserting every student’s result record separately which will take a lot of time, it is recommended to update the record of all the students using the single query.

There are two different ways for bulk inserting data in MySQL.

Method 1: Using LOAD DATA statement with CSV file

Another method is inserting data from a CSV file, to understand this, we will create a table of Teachers names with their subjects which they are teaching in a class using a command:

CREATE TABLE teacher_names (teacher_id INT, teacher_name VARCHAR(50), subject VARCHAR(50));

Cara menggunakan mysql bulk insert limit

Open a text file and type the following data:

teacher_id,teacher_name,subject

1,”John”,”English”

2,”Sophia”,”Science”

3,”Paul”,”Arts”

Cara menggunakan mysql bulk insert limit

Save the text file by name “teacher_names.csv”. You may encounter an error of –secure-file-priv option while loading the data, as shown in the image below:

LOAD DATA INFILE '/home/teacher_names.csv' INTO TABLE teacher_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

Cara menggunakan mysql bulk insert limit

To resolve this issue you need to move the teacher_names.csv to the secure_file_priv variable folder. Execute the command to locate the path of variable secure_file_priv:

SHOW VARIABLES LIKE "secure_file_priv";

Cara menggunakan mysql bulk insert limit

Now move the csv file to the /var/lib/mysql-myfiles folder:

Cara menggunakan mysql bulk insert limit

Run the following command to import all the data from the teacher_names.csv file to the teacher_names table of MySQL:

LOAD DATA INFILE '/var/lib/mysql-files/teacher_names.csv' INTO TABLE teacher_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

Cara menggunakan mysql bulk insert limit

To open and verify the file:

SELECT * FROM teacher_names;

Cara menggunakan mysql bulk insert limit

Method 2: Using INSERT INTO statement

The first method is using the insert command for the insertion of bulk data. Let us discuss the general syntax of using the command to insert bulk data in MySQL.

Syntax to insert bulk data in MySQL

The general syntax of inserting bulk values in a table in MySQL is:

INSERT INTO table_name VALUES (data), (data), (data);

The explanation to the above general syntax is simple:

  • Type the clause INSERT INTO and the table name in which you want to insert the data
  • Use the clause VALUES and then in the brackets write the data of the first row, close the brackets, and after the put the comma
  • After the comma use the brackets and enter the data of the other row and so on

To understand how this works, let us consider an example, we will create a table of “class_result”, using the command:

CREATE TABLE class_result (st_id INT, st_name VARCHAR(50), st_grade CHAR(25));

Cara menggunakan mysql bulk insert limit

We will insert the result of five students using the single command:

INSERT INTO class_result VALUES (1,’John’,’A’),(2,’Elsa’,’D’),(3,’Sophia’,’B’),(4,’Paul’,’B’),(5,’Saira’,’A’);

Cara menggunakan mysql bulk insert limit

To display the contents of the table:

SELECT*FROM class_result;

Cara menggunakan mysql bulk insert limit

From the above output, we see that we have inserted a bulk of data using a single query instead of inserting the data by different queries.

Conclusion

It saves a lot of time for inserting a bulk of data using a single query in MySQL. In this post, we learn the way of inserting the bulk of values in the table of MySQL using a single command. We created a table, inserted multiple rows of records in the tables by using a single MySQL query, and tried to explain how bulk data can be inserted into the table of MySQL. We also explain inserting the data from the CSV format file in the MySQL table using the query of the LOAD TABLE.

I recently wrote a node.js script to iterate over millions of files per day and insert their contents into a MySQL database. Rather than process one record at a time, the script stored file contents in memory and then ran an INSERT statement every 1000 files. To do that, I used the bulk insert form of the INSERT statement. Depending on your particular requirements, you may opt to go with a different solution. In today's blog, we'll go over a few alternatives.

The INSERT statement supports several syntax variations, one of which is for inserting multiple rows at the same time. To do that, we simply need to enclose each value list in parentheses and separate them using a comma:

INSERT INTO table_name (column_list) 
VALUES 
    (value_list_1), 
    (value_list_2), 
    ... 
    (value_list_n); 

Simple enough. Here's a sample statement shown in :

Cara menggunakan mysql bulk insert limit

While the above statement is formatted for readability, you don't have to concern yourself with that when generating the SQL dynamically. As long as the syntax is semantically correct, it will work just fine. Finally, note that 1000 is the maximum number of rows that can be inserted at one time using an INSERT statement.

Another option, for those of you who aren't thrilled about writing scripting code, is to use something like LOAD DATA INFILE. That's a MySQL-specific command, but most other database systems (DBMS) support something similar. It can import a variety of delimited file formats, including commas (CSV), Tabs (TDV), and others.

Here's the statement for importing data from the "c:\tmp\discounts.csv" file into the discounts table:

LOAD DATA INFILE 'c:/tmp/discounts.csv'  
INTO TABLE discounts  
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS; 

In the above statement, the IGNORE 1 ROWS option is employed to ignore headers.

I would have liked to have used this method for importing data, but the files that we were importing from utilized a highly specialized and complex format that required a lot of front-end logic.

Still another approach would be to use an import utility such as Navicat's Import Wizard. It supports just about any format that you can imagine, including CSV, Excel, HTML, XML, JSON, and many other formats:

Cara menggunakan mysql bulk insert limit

There is a screen for choosing the record delimiter, field delimiter, and text qualifier:

Cara menggunakan mysql bulk insert limit

Navicat shows you the progress in real time:

Cara menggunakan mysql bulk insert limit

Once your done, you can save all your settings for later use, which is not only useful for running the same on a regular basis, but it also allows you to automate it, so that imports happen without any additional intervention required on your part!

In today's blog, we covered a few alternatives for performing bulk inserts into MySQL and other DBMS.