English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

How to perform batch inserts in MySQL?

To perform batch insertion, we need to use all column names enclosed in parentheses and separated by commas.

Let's look at an example. First, we will create a table. The following is the CREATE command for creating a table.

mysql> CREATE table MultipleRecordWithValues
   - > (
   - > id int,
   - > name varchar(100)
   - > );

The following is the syntax for batch insertion.

INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;

Insert batch records using the above syntax.

mysql> insert into MultipleRecordWithValues values(,1,'John'),(,2,'Carol'),(,3,'Bob'),(,4,'Smith');
Records: 4  Duplicates: 0 Warnings: 0

Because it affected4rows, which means we have successfully inserted the records. To check if all records exist in the table, use the SELECT command.

mysql> select *from MultipleRecordWithValues;

The following is the output.

+------+-------+
| id | name |
+------+-------+
|    1 | John |
|    2 | Carol |
|    3 | Bob |
|    4 | Smith |
+------+-------+
4 rows in set (0.00 sec)