The `LAST_INSERT_ID()` function in MySQL is used to retrieve the last automatically generated `AUTO_INCREMENT` value that was inserted into an `AUTO_INCREMENT` column. This function is very useful in situations where you have a table with an `AUTO_INCREMENT` primary key and you need to know the primary key of the last inserted record for various reasons, such as for inserting related records in another table. Here is a breakdown of the aspects involved in using `LAST_INSERT_ID()` in MySQL:
You should have MySQL installed and a basic understanding of SQL syntax.
Suppose we have a table named `students` with the following structure:
CREATE TABLE students (
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT,
PRIMARY KEY (id)
);
In this example, the `id` column is an `AUTO_INCREMENT` column.
After inserting a record into the `students` table, you can retrieve the last inserted ID like this:
-- Insert a new student
INSERT INTO students (name, age) VALUES ('John', 25);
-- Get the ID of the last inserted student
SELECT LAST_INSERT_ID();
If you're using multiple statements, `LAST_INSERT_ID()` still returns the ID generated by the last `INSERT` operation:
-- Insert multiple students
INSERT INTO students (name, age) VALUES ('John', 25);
INSERT INTO students (name, age) VALUES ('Jane', 30);
-- Get the ID of the last inserted student (this will be Jane's ID)
SELECT LAST_INSERT_ID();
Within a transaction, `LAST_INSERT_ID()` is also valid:
START TRANSACTION;
INSERT INTO students (name, age) VALUES ('Tom', 22);
-- This will return Tom's ID, even within the transaction
SELECT LAST_INSERT_ID();
COMMIT;
`LAST_INSERT_ID()` is connection-specific. This means that if you have two different database connections, they won't interfere with each other when using `LAST_INSERT_ID()`.
In programming languages like PHP, Python, etc., you can use database-specific functions or methods to get the last inserted ID. PHP
Example:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO students (name, age) VALUES ('John', 25)";
if ($conn->query($sql) === TRUE) {
$last_id = $conn->insert_id;
echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
By understanding these aspects of `LAST_INSERT_ID()`, you'll be able to better manage relational data in your applications.
UPCET Exam
Click Here
SAAT Exam
Click Here
MHT CET Exam
Click Here
IPU CET Exam
Click Here
KCET Exam
Click Here
COMEDK UG Exam
Click Here
VITEEE Exam
Click Here
BITSAT
Click Here
DSAT: Dayanand Sagar Admission Test
Click Here
Career In Animation in india
Click Here
Merchant Navy Courses in india
Click Here
Interior Design Career in india
Click Here
UGC NET Exam
Click Here
B. Ed Exam
Click Here
AFCAT - Air Force Common Admission Test
Click Here
GATE Exam
Click Here
Joint Entrance Examination (JEE)
Click Here
Common Admission Test (CAT)
Click Here
CDS - Combined Defence Services Exam
Click Here