如何获取刚刚插入的文件的主键 ID？
今天早些时候，我问这个问题源于A-我糟糕的计划和B-我完全无视数据库规范化的实践。我花了 8 个小时阅读有关规范化数据库和 JOIN 细节的内容，并学习了 SQLZoo.com 教程。
我恍然大悟。我了解数据库规范化的目的以及它如何适合我。只是我不完全确定如何从程序的角度执行该愿景。
这是我的旧愿景：1 个名为“文件”的表，其中包含文件 id 和文件 url 以及该文件的适当等级。
新愿景！：1 个“文件”表，1 个“成绩”表，以及一个用于调解的连接表。
但这不是我的问题。这是一个非常基本的问题，我确信有一个明显的答案 - 当我在“文件”中创建记录时，它会自动分配递增的主键（file_id）。但是，从现在开始，我还需要将该 file_id 写入其他表。因为我没有手动分配该 id，所以我怎么知道它是什么？
如果我上传 text.doc 并且它得到 file_id 123，我如何知道它得到 123 以便将其写入“grades”和连接表？我无法执行 max(file_id) 因为如果您有并发用户，您可能会获得不同的 id。我只是不知道如何在没有手动分配的情况下获取 file_id 值。
Earlier today I asked this question which arose from A- My poor planning and B- My complete disregard for the practice of normalizing databases. I spent the last 8 hours reading about normalizing databases and the finer points of JOIN and worked my way through the SQLZoo.com tutorials.
I am enlightened. I understand the purpose of database normalization and how it can suit me. Except that I'm not entirely sure how to execute that vision from a procedural standpoint.
Here's my old vision: 1 table called "files" that held, let's say, a file id and a file url and appropos grade levels for that file.
New vision!: 1 table for "files", 1 table for "grades", and a junction table to mediate.
But that's not my problem. This is a really basic Q that I'm sure has an obvious answer- When I create a record in "files", it gets assigned the incremented primary key automatically (file_id). However, from now on I'm going to need to write that file_id to the other tables as well. Because I don't assign that id manually, how do I know what it is?
If I upload text.doc and it gets file_id 123, how do I know it got 123 in order to write it to "grades" and the junction table? I can't do a max(file_id) because if you have concurrent users, you might nab a different id. I just don't know how to get the file_id value without having manually assigned it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可能需要使用
LAST_INSERT_ID()，如下例所示：事务确保操作保持原子性：这可以保证两次插入都成功完成，或者根本没有插入。这是可选的，但建议这样做以保持数据的完整性。
来源和进一步阅读：
You may want to use
LAST_INSERT_ID()as in the following example:The transaction ensures that the operation remains atomic: This guarantees that either both inserts complete successfully or none at all. This is optional, but it is recommended in order to maintain the integrity of the data.
Source and further reading:
在 PHP 中，要获取自动生成的 MySQL 记录 ID，请使用 mysqli- mysqli 对象的 >insert_id 属性。
In PHP to get the automatically generated ID of a MySQL record, use mysqli->insert_id property of your mysqli object.
当你的程序忘记了last_insert_id()的值之后，明天你将如何找到该条目呢？
使用代理键很好，但是您的表仍然代表一个实体，您应该能够回答这个问题：哪些可测量属性定义了这个特定实体？这些属性的集合是表的自然键，即使您使用代理键，这样的自然键也应该始终存在，并且您应该使用它从表中检索信息。使用代理键来强制引用完整性，用于索引目的并使连接更容易观察。但是不要让它们从数据库中逃脱
How are you going to find the entry tomorrow, after your program has forgotten the value of last_insert_id()?
Using a surrogate key is fine, but your table still represents an entity, and you should be able to answer the question: what measurable properties define this particular entity? The set of these properties are the natural key of your table, and even if you use surrogate keys, such a natural key should always exist and you should use it to retrieve information from the table. Use the surrogate key to enforce referential integrity, for indexing purpuses and to make joins easier on the eye. But don't let them escape from the database