怎样在SQL存储过程中获取INSERT操作生成的自增主键-SQL-PHP中文网

mysql存储过程中必须用last_insert_id()获取刚插入的自增id，它只返回当前连接最近一次成功insert生成的第一个值，不依赖表名、不查数据、不受并发干扰；批量插入时返回首条id，显式指定自增列值时仍返回该值。

怎样在sql存储过程中获取insert操作生成的自增主键

MySQL 存储过程中必须用 `LAST_INSERT_ID()`

在 MySQL 存储过程里，INSERT 后想拿自增 ID，唯一可靠方式是调用 LAST_INSERT_ID()。它不依赖表名、不查数据、不拼 ORDER BY，只返回当前连接中最近一次成功 INSERT（或 REPLACE、LOAD DATA）生成的第一个自增值。

常见错误是写 SELECT MAX(id) FROM table 或 SELECT id FROM table ORDER BY id DESC LIMIT 1 —— 并发时大概率拿到别人插入的 ID，不是你刚插的那条。

批量插入 INSERT INTO t(x) VALUES (1),(2),(3) 时，LAST_INSERT_ID() 返回的是第一条记录的 ID，不是最后一条
如果 INSERT 显式指定了自增列值（如 INSERT INTO t(id,x) VALUES (100,'a')），LAST_INSERT_ID() 仍会返回 100
该函数只在 INSERT 实际触发自增时更新；若因唯一键冲突失败，值不变
存储过程中用事务包裹 INSERT 和 LAST_INSERT_ID() 调用即可，无需额外锁表

SQL Server 存储过程优先用 `SCOPE_IDENTITY()`

SQL Server 中，SCOPE_IDENTITY() 是获取本作用域内最后插入 ID 的最安全选择。它和 @@IDENTITY 最大区别在于：前者只认“当前作用域”（比如同一个存储过程、同一批语句），后者会跨触发器污染。

典型翻车场景：Orders 表有 INSERT 触发器，往 AuditLog 插日志（AuditLog.id 是 IDENTITY）。此时 SELECT @@IDENTITY 返回的是 AuditLog.id，而不是你想要的 OrderID。

SCOPE_IDENTITY() 必须和 INSERT 在同一作用域：不能被 GO 分割，不能拆成两次 ExecuteNonQuery() 调用
推荐写法是单条语句：INSERT INTO users (name) VALUES ('Alice'); SELECT SCOPE_IDENTITY();
返回类型是 numeric(38,0)，强类型上下文（如某些 ORM 参数绑定）中需注意隐式转换风险
即使 INSERT 因约束失败回滚，只要 identity 值已被分配，SCOPE_IDENTITY() 仍返回那个未落库的 ID —— 这是 SQL Server 设计行为，不是 bug

PostgreSQL 应该直接用 `RETURNING`，别碰 `currval()`

PostgreSQL 没有“全局最后插入 ID”的概念。currval('seq_name') 看似方便，但要求当前会话**已显式调用过 nextval()**。如果 INSERT 用的是默认值（如 id SERIAL），底层确实调用了 nextval()，但这个调用对用户不可见，currval() 就可能报错。

RETURNING 是与 INSERT 绑定的原子操作，天然规避竞态和会话状态依赖。

写法简单：INSERT INTO users (name) VALUES ('alice') RETURNING id;
可一次返回多列：RETURNING id, created_at, uuid
在存储过程（即 PL/pgSQL 函数）中，可用 RETURNING INTO 赋值给变量：INSERT INTO t(x) VALUES (1) RETURNING id INTO new_id;
不依赖序列名，不关心是否手动指定主键值，也不怕并发覆盖

MyBatis 或 JDBC 应用层别依赖存储过程返回，用 `useGeneratedKeys` 更稳

如果你的应用走的是 MyBatis 或原生 JDBC，不要在存储过程中设输出参数再层层返回，而是让框架直接对接数据库的生成键机制。MySQL 和 SQL Server 都支持 Statement.getGeneratedKeys()，MyBatis 的 useGeneratedKeys="true" 就是封装了它。

关键点是：这个机制不改变 Mapper 方法的返回值，而是**修改传入的实体对象本身**。

XML 中配置：<insert usegeneratedkeys="true" keyproperty="id">INSERT INTO users (name) VALUES (#{name})</insert>
Java 层调用后，user.getId() 就已有值；别误以为 insert(user) 的返回值是 ID（实际是影响行数，通常是 1）
MySQL 下需确保 JDBC URL 包含 rewriteBatchedStatements=true（非必需但建议），避免批量插入时取错 ID
PostgreSQL 下 RETURNING 会被自动识别，无需额外配置

真正容易被忽略的是连接生命周期：无论用哪种方式，INSERT 和取 ID 必须落在同一个数据库连接上。连接池场景下，手动控制事务或确保两次操作不跨连接，比选哪个函数更重要。