JIYIK CN >

There are many articles about finding the last inserted ID of a table in MySQL. However, it is hard to find any tutorials about finding the last inserted ID using two tables.

In this article, we will try to solve this special case, and of course, we will also explore when to use LAST_INSERT_ID()the command. First, let's see AUTO_INCREMENTwhat ID is and how it is LAST_INSERT_ID()related to .

AUTO_INCREMENTWhat is ID in MySQL ?

When dealing with databases, we often need the ID of a particular table to serve as the primary key. Because the primary key identifies a record in the database, it must be unique.

But how do we guarantee that the primary key is always unique? Another option is to construct the primary key using a formula that verifies the key's existence in the database before adding data.

This strategy may work, but it is difficult and not foolproof. We can take advantage of MySQL's AUTO_INCREMENT()functions to create primary keys to avoid this complexity and ensure that they are always unique.

For INTdata type, auto increment is used. INTData type supports both signed and unsigned values.

无符号The data type can only store positive numbers. Autoincrement allows unique numbers to be created automatically when new records are entered into the database.

Let’s look at an example.

CREATE TABLE cars (
     carID INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (carID)
);

After we create the table now, we can insert values. We also don't need to carIDspecify values ​​for the columns.

INSERT INTO cars (name)
VALUES ('BMW'),('Mercedes'),('Audi');
SELECT * FROM cars;

Output:

+----+---------+
| carID | name |
+----+---------+
|  1 | BMW     |
|  2 | Mercedes|
|  3 | Audi    |
+----+---------+

By default it will start with 1as the value, incrementing by 1. If we need to start from another value, we can change the new value table.

ALTER TABLE cars AUTO_INCREMENT=32;

Finally, we need to be careful when defining the data type of an auto-incrementing number. For example, if we set it to TINYINT, it will limit the number of records to 255.

We can use INTthe data type because it's restrictive enough. So now that we auto_incrementknow a lot about functions, we can move on to last_insert_id()functions.

AUTO_INCREMENTFind the last inserted ID of a column in MySQL

Returns the first generated ID inserted into the column as a result of the most recently executed INSERTstatement . If the insert fails, the value of the last inserted ID is not changed.LAST_INSERT_IDAUTO_INCREMENTint

We will execute it for the table created above.

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

As you can see, even though INSERTthe statement carsinserted three rows into the table, the first row successfully inserted was ID=1the row of . Suppose we need insertthe last ID from the statement (in this case: 3).

We will make use of MySQL's MAX_MAX maxfunction, which returns the current maximum value for a particular column.

SELECT MAX( carID ) FROM cars;

Output:

+-----------+
| MAX(carID)|
+-----------+
|         3 |
+-----------+

Now let's analyze another situation. When we insert data into two tables one by one, we need to get the last inserted ID of the first table.

CREATE TABLE cars (
     carID INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (carID)
);

CREATE TABLE carModels (

         otherID INT NOT NULL ,
          modelID INT NOT NULL AUTO_INCREMENT,
          name CHAR(30) NOT NULL,
           PRIMARY KEY (modelID)
);

ALTER TABLE carModels AUTO_INCREMENT=4;
INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
select LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

First, let's understand the code we have executed. We created two tables, namely carsand carModels.

We then changed carModelsthe starting ID of to differentiate the IDs of the two tables and set it to 4 using the method we learned above. We can see from the output that it returns the last inserted ID of the second table.

carsWhat if we need the last inserted table ID?

There are a number of ways we can do this, depending on the situation. We will look at each of them in light of our circumstances.

First method in MySQL: Store the ID in a variable

In this approach, we will store the last inserted ID of the first table in a variable and then use it in the second table instead of calling it directly LAST_INSERT_ID.

INSERT INTO cars (name) VALUES ('BMW');
SET @last_id_in_cars = LAST_INSERT_ID();
INSERT INTO carModels (otherID,name) VALUES (@last_id_in_cars,'F30');
SELECT @last_id_in_cars;

Output:

+------------------+
| @last_id_in_cars |
+------------------+
|               33 |
+------------------+

Before we can select variables, we need to use @symbols.

Second method: Retrieve the last inserted ID from the second table in MySQL

This is a special case in our example because we are using the last inserted ID of the first table in the second table. So we will retrieve it from the second table.

INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
SELECT otherID from carModels where modelID = LAST_INSERT_ID();

carsGet from the table MAX(carID):

We have already learned about functions maxand can use it in this example.

INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
SELECT MAX(carID) FROM cars;

However, senior engineers do not recommend this approach because it can fail in race conditions. Race conditions occur when multiple threads can access shared data at the same time.