How Can I Efficiently Insert Multiple Rows into a MySQL Table?

Bulk Insertion into MySQL Tables

Inserting multiple rows into a MySQL table in a single query can enhance efficiency and reduce database load. This article explores a solution to achieve this, addressing the challenge of inserting the same query multiple times.

Let's consider the provided MySQL query for inserting registered users into the pxlot table:

mysql_query("INSERT INTO `pxlot` (realname,email,address,phone,status,regtime,ip) 
VALUES ('$realname','$email','$address','$phone','0','$dateTime','$ip')")
or die (mysql_error()); // Inserts the user.

To insert this query multiple times, you can utilize the following approach:

INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);

This query allows you to specify multiple sets of values to be inserted into the table. Each set should be enclosed in parentheses and separated by commas.

For instance, to insert three rows of data using the pxlot query, you can use:

INSERT INTO pxlot (realname, email, address, phone, status, regtime, ip) 
VALUES ('realname1', 'email1', 'address1', 'phone1', '0', 'dateTime1', 'ip1'),
       ('realname2', 'email2', 'address2', 'phone2', '0', 'dateTime2', 'ip2'),
       ('realname3', 'email3', 'address3', 'phone3', '0', 'dateTime3', 'ip3');

This query will insert three rows of user data into the pxlot table in a single operation.

Refer to the MySQL documentation for more information on bulk inserts: http://dev.mysql.com/doc/refman/5.5/en/insert.html

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!