How the LAST_INSERT_ID() function works in Mariadb?

The LAST_INSERT_ID() function is a information function that returns the value of the AUTO_INCREMENT column for the last row that was inserted or updated by the current session. It can be used to get the unique identifier of the newly inserted or updated row, or to generate sequential numbers for other purposes. In this article, we will learn how the LAST_INSERT_ID() function works in Mariadb, what are its syntax and parameters, and how to use it with some examples. We will also explore some related functions that can be used with the LAST_INSERT_ID() function.

Syntax

The syntax of the LAST_INSERT_ID() function is as follows:

LAST_INSERT_ID([expr])

The function takes one optional argument:

expr: The expression to assign to the LAST_INSERT_ID() value. It can be any valid numeric expression. If omitted, the function returns the current value of the LAST_INSERT_ID().

The function returns a numeric value that represents the value of the AUTO_INCREMENT column for the last row that was inserted or updated by the current session. If no such row exists, the function returns 0. If the argument is NULL, the function returns NULL. If the argument is invalid, the function returns an error.

Examples

Let’s see some examples of how to use the LAST_INSERT_ID() function in Mariadb.

Example 1: Using the function without an argument

In this example, we will use the LAST_INSERT_ID() function without an argument. We will create a table named students with an AUTO_INCREMENT column named id, and insert some rows into it. Then we will use the LAST_INSERT_ID() function to get the value of the id column for the last inserted row.

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

INSERT INTO students (name) VALUES ('Alice'), ('Bob'), ('Charlie');

SELECT LAST_INSERT_ID();

The function returns the value 3, which is the value of the id column for the last inserted row.

Example 2: Using the function with an argument

In this example, we will use the LAST_INSERT_ID() function with an argument. We will create a table named orders with an AUTO_INCREMENT column named order_id, and insert some rows into it. Then we will use the LAST_INSERT_ID() function with an argument of 100 to set the value of the LAST_INSERT_ID() to 100. Then we will insert another row into the table and use the LAST_INSERT_ID() function again to get the value of the order_id column for the last inserted row.

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO orders (customer_id, amount) VALUES (1, 10.00), (2, 20.00), (3, 30.00);

SELECT LAST_INSERT_ID(100);

INSERT INTO orders (customer_id, amount) VALUES (4, 40.00);

SELECT LAST_INSERT_ID();

100
101

The first function call returns the value 100, which is the value of the LAST_INSERT_ID() after setting it to 100. The second function call returns the value 101, which is the value of the order_id column for the last inserted row.

Example 3: Using the function to generate sequential numbers

In this example, we will use the LAST_INSERT_ID() function to generate sequential numbers for a table that does not have an AUTO_INCREMENT column. We will create a table named products with a column named product_code, and insert some rows into it. Then we will use the LAST_INSERT_ID() function with an argument of 0 to reset the value of the LAST_INSERT_ID() to 0. Then we will use the LAST_INSERT_ID() function with an argument of LAST_INSERT_ID() + 1 to generate sequential numbers for the product_code column.

CREATE TABLE products (
  product_code INT NOT NULL,
  product_name VARCHAR(50) NOT NULL
);

INSERT INTO products (product_name) VALUES ('Laptop'), ('Mouse'), ('Keyboard');

SELECT LAST_INSERT_ID(0);

UPDATE products SET product_code = LAST_INSERT_ID(LAST_INSERT_ID() + 1);

SELECT * FROM products;

product_code | product_name
-------------|-------------
1            | Laptop
2            | Mouse
3            | Keyboard

The first function call returns the value 0, which is the value of the LAST_INSERT_ID() after resetting it to 0. The second function call updates the product_code column with sequential numbers generated by the LAST_INSERT_ID() function. The third function call returns the updated table.

There are some related functions that can be used with the LAST_INSERT_ID() function in Mariadb. Here are some of them:

LASTVAL(): This is a sequence function that returns the value of the last generated sequence number. It can be used to get the value of the AUTO_INCREMENT column for a table that uses a sequence as the default value. For example:
```
CREATE SEQUENCE seq START WITH 100;

CREATE TABLE invoices (
  invoice_id INT PRIMARY KEY DEFAULT seq.NEXTVAL,
  customer_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO invoices (customer_id, amount) VALUES (1, 50.00), (2, 60.00), (3, 70.00);

SELECT LASTVAL();
```
```
102
```
This statement creates a sequence named seq and sets the starting value to 100. Then it creates a table named invoices with an AUTO_INCREMENT column named invoice_id, and sets the default value to the next value of the sequence. Then it inserts some rows into the table and generates sequence numbers for the invoice_id column. The last statement returns the value of the last generated sequence number.

Conclusion

In this article, we learned how the LAST_INSERT_ID() function works in Mariadb, what are its syntax and parameters, and how to use it with some examples. We also explored some related functions that can be used with the LAST_INSERT_ID() function. The LAST_INSERT_ID() function is a useful tool for getting the value of the AUTO_INCREMENT column for the last row that was inserted or updated by the current session, or for generating sequential numbers for other purposes.