When working in relational databases like MySQL, it is a common practice to use auto-generated IDs for each record using tools such as auto_increment. This delegates the column sequence to the database engine instead of manually counting and ensuring we have consistent values across data.
Once we insert a new record, we may need to know it's ID. This can be useful such as fetching the relationship between tables or simply getting feedback to the application or debugging purposes.
Sample Table Structure
Before diving into the meat of the post, let us create a basic table for demonstration purposes. We will create a basic table that stores users as shown:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
Method 1 - Using LAST_INSERT_ID() Function
The most common method we can use to get the ID of the last inserted column is the last_insert_id() function. This function retrieves the ID generated from an auto_increment column by the previous insert statement.
To demonstrate, let us start by inserting a record into the users table as shown:
INSERT INTO users (name, email) VALUES ('GeekBits', '[email protected]');
Once inserted, we can selected the ID of the previously inserted column as shown:
SELECT LAST_INSERT_ID() AS LastID;
Method 2 - Using Transaction:
In
If you have multiple operations and want to ensure that we are retrieving the ID from our specific insert (especially in a multi-user environment), we can use transactions.
An example is as shown:
START TRANSACTION;
INSERT INTO users (name, email) VALUES ('Geekbits', '[email protected]');
SELECT LAST_INSERT_ID() AS LastID;
COMMIT;
Conclusion
When managing databases, it's crucial to know how to retrieve the ID of the last inserted record in MySQL. With the techniques discussed above, you're now equipped to handle this with ease.
