Postgres用returning实现mysql的last_insert_id

Postgres用returning实现mysql的last_insert_id 今天开发问到，postgres里面有没有像mysql那样插入一个值后返回插入的值，这个是有的，而且有更强的扩展性。 示例： [postgres@localhost ~]$ psql psql (9.2.4) Type help for help. postgres=# create table

Postgres用returning实现mysql的last_insert_id

 

  今天开发问到，postgres里面有没有像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

 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

 id |     remark      

----+-----------------

  5 | kenyon bad boy!

  6 | kenyon bad boy!

(2 rows)

 

UPDATE 2

mysql的last_insert_id使用有诸多限制和注意的地方，如字段需要auto_increment，一个SQL插入多个值的时候只会返回第一个id值，此不再叙述。