Quickly Insert Rows in Bulk with MySQL

Let's dive into the world of bulk data insertion in MySQL. Imagine you're a software developer building a brand new application. You've got a fantastic feature that relies on a massive dataset. But before launching it to the real world, you need to test how your system handles a massive data.

This is where bulk data insertion comes in handy. It's like a turbocharged way to populate your database with lots of information quickly.

You can simulate real-world scenarios by inserting thousands or even millions of records to see how your database and application perform under pressure.

Bulk inserts are essential for benchmarking your database and application performance, allowing you to compare different configurations and optimize for speed.

Let's dive into some examples of scenarios where you might use bulk inserts for testing:

You might use a bulk insert to add users, products, orders, or even customer reviews to your database at once. This is much faster than adding each order individually. This lets you test how your application handles large amounts of data.

You need to populate your database with a large number of products to test search functionality and filtering.

So, if you're working with a system that handles large volumes of data, bulk inserts are a powerful tool to have in your toolbox.

In the next part, we'll explore some specific examples of how bulk insertion can be used to test your software.

To generate 10,000 rows of data for your wp_postmeta table in a single INSERT statement:

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

 SELECT

 nums.n,

 CONCAT('Ten Thousand Key ', CAST(nums.n AS CHAR)),

 CONCAT('Ten Thousand Value ', CAST(nums.n AS CHAR))

 FROM (

 SELECT 

 d1.N + d2.N * 10 + d3.N * 100 + d4.N * 1000 + 1 AS n

 FROM

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1

 ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2

 ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3

 ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4

 ORDER BY n

 ) nums;

Let's break it down step by step:

• Subquery (nums): This subquery is responsible for generating the sequence of numbers.

  • It uses a SELECT statement to combine four identical subqueries (labeled d1, d2, d3, and d4) using UNION ALL.
  • Each subquery contains the numbers 0 to 9.
  • The expression d1.N + d2.N * 10 + d3.N * 100 + d4.N * 1000 + 1 AS n combines the values from the four subqueries to create a sequence of numbers from 1 to 10,000.
  • ORDER BY n ensures the numbers are generated in ascending order.

• Main Query: This is the INSERT INTO statement.

  • It uses the SELECT statement to specify the data to be inserted.
  • post_id: The nums.n variable (which holds the generated numbers) is used directly as the post_id.
  • meta_key: The CONCAT('Ten Thousand Key ', CAST(nums.n AS CHAR)) expression generates a unique key for each row.
  • meta_value: The CONCAT('Ten Thousand Value ', CAST(nums.n AS CHAR)) expression creates a unique value for each row.

Data Produced: The query will insert 10,000 rows into the wp_postmeta table. Each row will have:

• post_id: A number from 1 to 10,000.
• meta_key: A unique key like "Ten Thousand Key 1", "Ten Thousand Key 2", etc.
• meta_value: A unique value like "Ten Thousand Value 1", "Ten Thousand Value 2", etc.

This MySQL query can be used for ten thousand row entries, but for larger data entries it is necessary to advance the data generation approach.

The following MySQL query is a clever way to generate one million rows of data for your wp_postmeta table using user-defined variables and a series of CROSS JOIN operations

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

 SELECT

 (@rn := @rn + 1) as rn,

 CONCAT('One Million Key ', CAST(@rn AS CHAR)),

 CONCAT('One Million Value ', CAST(@rn AS CHAR))

 FROM

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d5 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d6 CROSS JOIN

 (SELECT @rn := 0) params

Let's break it down this extented MySQL query:

• User-Defined Variable (@rn):

  • The query introduces a user-defined variable called @rn. This variable is used to keep track of the row number as we generate data.
  • Initialization: The variable is initialized to 0 in the last SELECT statement: (SELECT @rn := 0) params.

• CROSS JOIN:

  • The query uses six CROSS JOIN operations to generate a large number of rows.
  • Each CROSS JOIN combines the results of two tables. In this case, we are using six identical subqueries (labeled d1 to d6) containing the numbers 0 to 9.
  • Each CROSS JOIN multiplies the number of rows by 10. Since we have six CROSS JOIN operations, the total number of rows generated is 10 * 10 * 10 * 10 * 10 * 10 = 1,000,000.

• SELECT Statement:

  • The SELECT statement retrieves the following data for each row:
    • (@rn := @rn + 1) as rn: This expression increments the @rn variable by 1 for each row, effectively creating a row number. The as rn clause gives the generated row number the alias rn.
    • CONCAT('One Million Key ', CAST(@rn AS CHAR)): This expression generates a unique key for each row. It combines the string "One Million Key " with the current row number (@rn) converted to a character.
    • CONCAT('One Million Value ', CAST(@rn AS CHAR)): This expression generates a unique value for each row. It combines the string "One Million Value " with the current row number (@rn) converted to a character.

Data Produced:

• The query will insert one million rows into the wp_postmeta table. Each row will have:
  • post_id: A row number from 1 to 1,000,000.
  • meta_key: A unique key like "One Million Key 1", "One Million Key 2", etc.
  • meta_value: A unique value like "One Million Value 1", "One Million Value 2", etc.

Key Points:

• User-Defined Variables are powerful tools for manipulating data within a query.
• CROSS JOIN is a great way to generate a large number of rows by combining multiple tables or subqueries.
• This method is more efficient than using nested UNION ALL operations for generating a large number of rows.

This query demonstrates a clever and efficient approach to generating a massive amount of test data for your database.



Keywords: Bulk insert MySQL,MySQL large data insertion,Insert multiple rows MySQL,MySQL bulk insert best practices,Generate test data MySQL,MySQL insert many rows efficiently,CROSS JOIN MySQL for data generation,Bulk insert examples MySQL,Batch insert in MySQL,MySQL data generation techniques