Returns the last value inserted into an AUTO_INCREMENT column.

LAST_INSERT_ID()

Return type

Integer

Examples

After a multi-insert statement, the value returned by LAST_INSERT_ID() is the first value inserted in that group of records.

memsql> create table persons (
      -> id bigint primary key auto_increment,
      -> firstname varchar(64),
      -> lastname varchar(64));

memsql> insert into persons values
      -> (NULL, 'Eponymous', 'Bach');

memsql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

memsql> insert into persons values
      -> (NULL, 'Ping', 'Baudot'),
      -> (NULL, 'Count', 'Modulo'),
      -> (NULL, 'Hugh', 'Rustic');

memsql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

This feature can be combined with views to enable row-level permissions. In this example, persons with the ‘employee’ role can only see themselves, while ‘managers’ can see all records:

memsql> create table employees (
    ->    id int primary key,
    ->    fullname varchar(64),
    ->    account_name varchar(64),
    ->    role enum('employee', 'manager'),
    ->    salary int
    -> );

memsql> create table permissions (
    ->    account_name varchar(64),
    ->    emp_id int,
    ->    primary key (account_name, emp_id)
    -> );

memsql> create view employee_view as
    ->    select e.* from employees e, permissions p
    ->    where e.id = p.emp_id
    ->    and p.account_name = current_user();

memsql> insert into employees values
    -> (1, 'SYSTEM', 'root@%', 'manager', 0),
    -> (2, 'Kevin Kelvin', 'kelvin@%', 'manager', 5000),
    -> (3, 'Oliver Heavyside', 'oliver@%', 'employee', 3000);

memsql> insert into permissions values
    -> ('root@%', 1), ('root@%', 2), ('root@%', 3),
    -> ('kelvin@%', 2), ('kelvin@%', 3),
    -> ('oliver@%', 3);

-- as root:
memsql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| root@%         |  1 | SYSTEM           | root@%       | manager  |      0 |
| root@%         |  2 | Kevin Kelvin     | kevin@%      | manager  |   5000 |
| root@%         |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+

-- as kelvin:
mysql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| kelvin@%       |  2 | Kevin Kelvin     | kevin@%      | manager  |   5000 |
| kelvin@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+

-- as oliver:
mysql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname         | account_name | role     | salary |
+----------------+----+------------------+--------------+----------+--------+
| oliver@%       |  3 | Oliver Heavyside | oliver@%     | employee |   3000 |
+----------------+----+------------------+--------------+----------+--------+

Was this Article Helpful?

Thanks For Your Feedback!