模型下一个可用的主键 [英] Model next available primary key

问题描述

我如何知道某些模型的下一个免费主键？

How can i know the next free primary key of some model?

推荐答案

即使您可以查询下一个可用的主键关键的价值，它不会帮助你。除非你锁定表，否则在某些其他数据库客户端可能抓住它的插入之前，不能使用该值。

Even if you can query the next available primary key value, it wouldn't help you. Unless you lock the table, you can't use that value before some other database client might grab it for their insert.

相反，你应该插入你的行，那么您可以查询在当前会话期间生成的最近的键值。支持自动生成的主键的每个数据库都提供了一种方法来检索在会话期间插入的最新密钥。

Instead, you should just insert your row, and then you can query the most recent key value generated during your current session. Every database that supports auto-generated primary keys provides a method to retrieve the most recent key inserted during your session.

在会话期间部分是重要的，因为它屏蔽您的任何插入的会话由其他客户端同时完成。他们可以生成关键值，您的会话将继续报告最近插入的相同值 。

The "during your session" part is important because it shields your session from any inserts being done concurrently by other clients. They can generate key values and your session will continue to report the same value it inserted most recently.

@Stuart Childs 建议 MySQL生成下一个ID， MAX（column_name）+ 1 但这是不正确的。假设您插入一行，并生成ID值。但是您回滚此插入，或随后 DELETE 该行。下次插入时，MySQL会生成一个全新的ID值。因此，ID值比任何客户端生成的最后一个ID值大一个，而不管当前存储在表中的行。

@Stuart Childs supposes that MySQL generates the next ID with MAX(column_name)+1 but this is incorrect. Say you insert a row and an ID value is generated. But you rollback this insert, or subsequently DELETE that row. The next time you insert, MySQL will generate a brand new ID value. So the ID value is one greater than the last ID value generated by any client, regardless of what rows are currently stored in the table.

同样，如果你插入但不要立即提交。在您提交之前，其他一些客户端插入。您的会话和其他客户端的会话都将具有自己唯一的ID值生成。自动生成的主键操作而不考虑事务隔离，以确保唯一性。

Likewise if you insert but don't commit immediately. Before you commit, some other client does an insert. Both your session and the other client's session will have their own unique ID value generated. Auto-generated primary keys operate without regard to transaction isolation, to ensure uniqueness.

自动生成的主键值不会重新使用或分配给多个会话，即使您尚未提交插入，或者您回滚插入，或者您删除该行。

Auto-generated primary key values are not re-used or allocated to more than one session, even if you have not yet committed your insert, or if you rollback the insert, or if you delete the row.

这篇关于模型下一个可用的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！