PostgresSQL用returning实现mysql的last_insert_id
2021/12/3 2:09:37
本文主要是介绍PostgresSQL用returning实现mysql的last_insert_id,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
今天开发问到,postgressql里面有没有像mysql那样插入一个值后返回插入的值,这个是有的,而且有更强的扩展性。
示例:
[postgres@localhost ~]$ psql psql (9.2.4) Type "help" for help. postgres=# create table t_kenyon(id int,vname varchar(30),remark text); CREATE TABLE postgres=# insert into t_kenyon(id,vname) values(1,'test_kenyon') returning id; id ---- 1 (1 row) INSERT 0 1 postgres=# insert into t_kenyon(id,vname) select generate_series(1,5),'Kenyon here' returning id; id ---- 1 2 3 4 5 (5 rows) INSERT 0 5
扩展:
a.返回更多的insert内容
postgres=# insert into t_kenyon(id,vname) select generate_series(6,8),'Kenyon here' returning id,vname; id | vname ----+------------- 6 | Kenyon here 7 | Kenyon here 8 | Kenyon here (3 rows) INSERT 0 3 postgres=# insert into t_kenyon(id,vname,remark) select generate_series(9,11),'Kenyon here','KENYON GOOD BOY!' returning *; id | vname | remark ----+-------------+------------------ 9 | Kenyon here | KENYON GOOD BOY! 10 | Kenyon here | KENYON GOOD BOY! 11 | Kenyon here | KENYON GOOD BOY! (3 rows) INSERT 0 3
b.返回delete掉的数据
postgres=# select * from t_kenyon; id | vname | remark ----+-------------+------------------ 1 | test_kenyon | 1 | Kenyon here | 2 | Kenyon here | 3 | Kenyon here | 4 | Kenyon here | 5 | Kenyon here | 6 | Kenyon here | 7 | Kenyon here | 8 | Kenyon here | 9 | Kenyon here | KENYON GOOD BOY! 10 | Kenyon here | KENYON GOOD BOY! 11 | Kenyon here | KENYON GOOD BOY! (12 rows) postgres=# delete from t_kenyon where id >9 returning id,vname; id | vname ----+------------- 10 | Kenyon here 11 | Kenyon here (2 rows) DELETE 2 postgres=# delete from t_kenyon where id <5 returning *; id | vname | remark ----+-------------+-------- 1 | test_kenyon | 1 | Kenyon here | 2 | Kenyon here | 3 | Kenyon here | 4 | Kenyon here | (5 rows) DELETE 5 postgres=# select * from t_kenyon; id | vname | remark ----+-------------+------------------ 5 | Kenyon here | 6 | Kenyon here | 7 | Kenyon here | 8 | Kenyon here | 9 | Kenyon here | KENYON GOOD BOY! (5 rows)
c.返回update掉的数据
postgres=# update t_kenyon set remark = 'kenyon bad boy!' where id <7 returning id,remark; id | remark ----+----------------- 5 | kenyon bad boy! 6 | kenyon bad boy! (2 rows) UPDATE 2
mysql的last_insert_id使用有诸多限制和注意的地方,如字段需要auto_increment,一个SQL插入多个值的时候只会返回第一个id值,此不再叙述。
这篇关于PostgresSQL用returning实现mysql的last_insert_id的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-16MySQL资料:新手入门教程
- 2024-11-16MySQL资料:新手入门教程
- 2024-11-15MySQL教程:初学者必备的MySQL数据库入门指南
- 2024-11-15MySQL教程:初学者必看的MySQL入门指南
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程