检索刚刚插入 SQL 表中的特定行
我有一个名为 BUILD_INFO 的表，它具有以下列：
BUILD_IDBUILD_NAMEDATEUSER
BUILD_ID 是一个自动增量字段，因此我在插入时不会设置它。它是用于将该表中的该行与其他表中的其他行相关联的主键。
当我在 BUILD_INFO 表中插入新行时，我想知道设置的 BUILD_ID，以便在其他表中添加行时可以检索并使用它。
我无法真正通过其他列查询它，因为其他行可能会重复这些列值。我不能简单地重新查询表并提取最大的 BUILD_ID，因为另一个用户可能在我之后插入了一行。如果有像 Row Number 这样的东西，我可以通过它来检索和查询行，那就太好了。存在这样的东西吗？
我正在使用 DBI 模块在 Perl 中编写脚本，我的数据库可以是 Oracle 或 MySql。
如何检索刚刚在 SQL 中或使用 Perl DBI 模块插入的行的信息？
I have a table called BUILD_INFO and it has the following columns:
BUILD_IDBUILD_NAMEDATEUSER
The BUILD_ID is an autoincrement field, so I don't set it when I do an insert. It's the primary key that is used to relate this row in this table to other rows in other tables.
When I insert a new row in the BUILD_INFO table, I want to know the BUILD_ID that was set, so I can retrieve it and use it when I add rows in other tables.
I can't really query it by the other columns because other rows could duplicate these column values. I can't simply requery the table and pull up the largest BUILD_ID because another user might have inserted a row after I did. It be nice if there was something like a Row Number that I could retrieve and query rows by. Does something like that exist?
I'm writing my script in Perl using the DBI module and my database could be Oracle or MySql.
How can I retrieve the information of the row I just inserted in SQL or in using the Perl DBI module?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
但请参阅 http://p3rl.org/DBI#last_insert_id 中的注意事项
But see the caveats in http://p3rl.org/DBI#last_insert_id
听起来您想要数据库句柄上的 last_insert_id 方法。但是，实际上，如果您查看 DBIx::Class，您的生活会容易得多并停止编写原始 SQL。
Sounds like you want the last_insert_id method on the database handle. But, really, your life would be far easier if you looked at DBIx::Class and stopped writing raw SQL.
感谢 davorg 指出
中的包。我是DBI->last_inserted_row()方法DBIDBI包的新手，不知何故错过了它，尽管事实上它在 HTML 索引中的selectrow_hashref方法上方清楚地列出了两个条目。这听起来像是我正在寻找的。至于你关心的问题，我实际上正在创建一个包，这样我们就可以摆脱编写原始 SQL 查询的麻烦。由于多种原因，我讨厌原始 SQL 查询。首先，对于大多数开发人员来说，编写它们并不容易，而且理解起来也很棘手。另外，它们通常是根据数据库布局及其连接方式的假设进行硬编码的。最重要的是，大多数开发人员都不擅长编写它们。
我希望我之前就了解 DBIx。那本来可以节省我很多工作。问题是我们的开发机器无法访问互联网，这使得安装软件包变得非常痛苦。我必须阅读 DBIx 文档。现在，它看起来比我们需要的要复杂一些。
也感谢 ysth 也指出了
DBI->last_insert_row()方法（以及随之而来的警告）。不知何故，我在阅读 DBI 文档时错过了这个方法。我必须看看它在 Oracle 中是否有效。它将在 MySql 中工作，因为我们索引表所依据的列是自动增量列。Thanks davorg for pointing out the
DBI->last_inserted_row()method in theDBIpackage. I'm new to theDBIpackage and somehow missed it despite the fact it's clearly listed just two entries aboveselectrow_hashrefmethod in the HTML index. That sounds like what I'm looking for.As for your concern, I am actually creating a package, so we can get away from writing raw SQL queries. I hate raw SQL queries for a variety of reasons. First of all, they're not exactly easy for most developers to write and they can be tricky to understand. Plus, they're usually hard coded to assumptions of the database layout and how it is connected. Most importantly of all, most developers suck at writing them.
I wish I knew about DBIx before. That would have saved me a lot of work. The problem is that our development machine has no Internet access which makes installing packages a big pain. I have to go through the DBIx documentation. Right now, it looks a bit more complex than what we need.
Thanks too to ysth for also pointing out the
DBI->last_insert_row()method too (and the caveats that go with it). Somehow, I missed this method while reading theDBIdocumentation. I'll have to see if it works in Oracle It'll work with MySql because the column we index our table by is an autoincrement column.