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

参考链接

分类:PostgreSQL

Tagged as: ,

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

You are commenting using your WordPress.com account. Log Out /  更改 )

Google photo

You are commenting using your Google account. Log Out /  更改 )

Twitter picture

You are commenting using your Twitter account. Log Out /  更改 )

Facebook photo

You are commenting using your Facebook account. Log Out /  更改 )

Connecting to %s

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理