oracle查找刚插入的id,如何得到刚插入数据库的那条记录的 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 渐入大众视野，跻身数据库排名第四位也是实至名归，难怪是所有数据库中增长幅度最大的数据库。

参考链接