mysql returning_Postgres用returning实现mysql的last_insert_id

ゞ浴缸里的玫瑰 2023-01-12 07:59 105阅读 0赞

今天开发问到，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;

\----

(1 row)

INSERT 0 1

postgres=\# insert into t\_kenyon(id,vname) select generate\_series(1,5),'Kenyon here' returning id;

\----

(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值，此不再叙述。

mysql returning_Postgres用returning实现mysql的last_insert_id

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL 索引的用处和实现方法

相关牛B，用MySQL实现搜索引擎

相关 mysql returning_Postgres用returning实现mysql的last_insert_id

相关 mysql 好用_mysql 好用的sql语句

相关【MySQL】MySQL的常用升级方法

相关【PDO扩展】lastInsertId函数返回0的原因

相关 JS中return、return false、return true 的区别与用法

相关 MHA+mysql实现高可用

相关用mysql实现rank（）排序

相关 MySQL如何实现高可用