How Can You Insert Rows and Retrieve Data in a Single MySQL Query?

Inserting Rows and Retrieving New Data in MySQL

Many tasks require adding new records to a database and subsequently accessing them. Traditionally, this involves separate insertion and retrieval operations. But did you know it's possible to accomplish both tasks in a single query?

Inserting and Selecting Values Atomically

The key lies in utilizing the LAST_INSERT_ID() function. After executing an insert statement, this function returns the ID generated for the newly inserted row.

Example:

Let's consider the example provided in the question:

<code class="sql">INSERT INTO `items` (`item`, `number`, `state`) 
(SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3')</code>

To get the ID of the inserted row and retrieve its other values, you can execute the following query:

<code class="sql">SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID()</code>

This query will fetch the entire row from the items table that corresponds to the last inserted row.

Benefits:

• Single query for both tasks: Improves performance and reduces code complexity.
• Atomic operation: The two steps occur in a single transaction, ensuring data integrity.
• Simplifies data retrieval: Developers don't need to worry about memorizing or managing row IDs outside of the query context.

The above is the detailed content of How Can You Insert Rows and Retrieve Data in a Single MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!