Inserting multiple rows in mysql

Question

Is the database query faster if I insert multiple rows at once:

like

INSERT....

UNION

INSERT....

UNION

(I need to insert like 2-3000 rows)

Answer 1

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

Example:

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Source

Answer 2

If you have your data in a text-file, you can use LOAD DATA INFILE.

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

Optimizing INSERT Statements

You can find more tips on how to speed up your insert statements on the link above.

Answer 3

Just use a SELECT statement to get the values for many lines of the chosen columns and put these values into columns of another table in one go. As an example, columns "size" and "price" of the two tables "test_b" and "test_c" get filled with the columns "size" and "price" of table "test_a".

BEGIN;
INSERT INTO test_b (size, price)
  SELECT size, price
  FROM   test_a;
INSERT INTO test_c (size, price) 
  SELECT size, price
  FROM   test_a;
COMMIT;

The code is embedded in BEGIN and COMMIT to run it only when both statements have worked, else the whole run up to that point gets withdrawn.

Answer 4

Here is a PHP solution ready for use with a n:m (many-to-many relationship) table :

// get data
$table_1 = get_table_1_rows();
$table_2_fk_id = 123;

// prepare first part of the query (before values)
$query = "INSERT INTO `table` (
   `table_1_fk_id`,
   `table_2_fk_id`,
   `insert_date`
) VALUES ";

//loop the table 1 to get all foreign keys and put it in array
foreach($table_1 as $row) {
    $query_values[] = "(".$row["table_1_pk_id"].", $table_2_fk_id, NOW())";
}

// Implode the query values array with a coma and execute the query.
$db->query($query . implode(',',$query_values));

EDIT : After @john's comment I decided to enhance this answer with a more efficient solution :

• divides the query to multiple smaller queries
• use rtrim() to delete last coma instead of implod()

// limit of query size (lines inserted per query)
$query_values  = "";
$limit         = 100;
$table_1       = get_table_1_rows();
$table_2_fk_id = 123;

$query = "INSERT INTO `table` (
   `table_1_fk_id`,
   `table_2_fk_id`,
   `insert_date`
) VALUES ";

foreach($table_1 as $row) {
    $query_values .= "(".$row["table_1_pk_id"].", $table_2_fk_id, NOW()),";
    
    // entire table parsed or lines limit reached :
    // -> execute and purge query_values
    if($i === array_key_last($table_1) 
    || fmod(++$i / $limit) == 0) {
        $db->query($query . rtrim($query_values, ','));
        $query_values = "";
    }
}

Answer 5

// db table name / blog_post / menu /  site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO product_cate (site_title, sub_title) 
  VALUES ('$site_title', '$sub_title')";

// db table name / blog_post / menu /  site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO menu (menu_title, sub_menu)
  VALUES ('$menu_title', '$sub_menu', )";

// db table name / blog_post /  menu /  site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO blog_post (post_title, post_des, post_img)
  VALUES ('$post_title ', '$post_des', '$post_img')";