如何得到刚插入数据库的那条记录的 ID

很多情况下，我们会有一些带有自增 ID 的数据库表，比如用户数据表、单位信息表、设备信息表等等，当往这类表内插入记录后，有时需要拿到该记录对应的 ID 去做一些操作，比如返回给前端页面。如何拿到这个 ID 呢，对于不同的数据库、不同的数据库中间件、不同的生产环境，方法也各不相同；有些简便高效的方法利用了特定数据库专属的不符合标准 SQL 规范的特性，使用这些方法时就需要考虑到后期数据库迁移可能带来的不便。下面就以主流的 MySQL 、MS SQL、Oracle、PostgreSQL 数据库来看一下它们获取 ID 比较常用又简便的方法。

MySQL

MySQL 使用 LAST_INSERT_ID() 函数获得刚插入的记录的第一个自增列的值，适用于插入一条记录；当插入多条记录时，只会返回插入多条记录中的第一条记录的自增列的 ID。参见下面取自官网的示例：

mysql> USE test;

mysql> CREATE TABLE t (
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
       name VARCHAR(10) NOT NULL
       );

mysql> INSERT INTO t VALUES (NULL, 'Bob');

mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

mysql> INSERT INTO t VALUES
       (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');

mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

MS SQL

MS SQL 提供了 SCOPE_IDENTITY、IDENT_CURRENT、和 @@IDENTITY 来获取自增 ID，适用场景和范围需要仔细斟酌，参见取自官网的示例：

CREATE TABLE TZ (  
   Z_id  int IDENTITY(1,1)PRIMARY KEY,  
   Z_name varchar(20) NOT NULL);  
  
INSERT TZ  
   VALUES ('Lisa'),('Mike'),('Carla');  

SELECT * FROM TZ;  
Z_id   Z_name  
-------------  
1      Lisa  
2      Mike  
3      Carla  

INSERT TZ VALUES ('Rosalie');

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];  

/*SCOPE_IDENTITY returns the last identity value in the same scope. This was the insert on table TZ.*/`  
SCOPE_IDENTITY  
4

Oracle

Oracle 支持 DML 语句的 RETURNING INTO 子句语法，可以方便的获取刚操作记录的任意字段值，参见取自官网的示例：

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name 
       FROM employees;
DECLARE
  emp_id          employees_temp.employee_id%TYPE;
  emp_first_name  employees_temp.first_name%TYPE;
  emp_last_name   employees_temp.last_name%TYPE;
BEGIN
   INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry');
   UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299;
   DELETE FROM employees_temp WHERE employee_id = 299 
     RETURNING first_name, last_name INTO emp_first_name, emp_last_name;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE( emp_first_name  || ' ' || emp_last_name);
END;

PostgreSQL

PostgreSQL 和 Oracle 类似，同样提供了 RETURNING 子句来返回刚插入记录的某个字段值，参见取自官网的示例：

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

总结

PostgreSQL 提供了与 Oracle 类似的扩展支持，也是解决了一个痛点，管中窥豹，PostgreSQL 渐入大众视野，跻身数据库排名第四位也是实至名归，难怪是所有数据库中增长幅度最大的数据库。

参考链接

• https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id
• https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017
• https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm
• https://www.postgresql.org/docs/current/sql-insert.html
• https://db-engines.com/en/ranking_trend