PHP MySQLi-插入或更新后的insert_id 0-我应该先阅读吗? [英] PHP MySQLi - insert_id 0 after insert or update - should I read first?
问题描述
我还是一个新手，一些正确的编码实践使我无所适从.关于这种特殊情况的文档很薄弱，因此，我想从您的专家那里获得一些建议/建议:).
I'm still new and some of the right coding practices escape me. Documentation on this particular situation is weak, so I would like to get some advice/suggestions from you experts :) on the following.
我有一个API，允许用户在一个调用中更新2个表.一个是 SUMMARY 表，另一个是 DETAIL 表，其FK指向 SUMMARY 表.
I have an API that allows users to update 2 tables in one call. One is a SUMMARY table and the other is a DETAIL table with an FK to the SUMMARY table.
我要执行的代码是对 SUMMARY 表进行UPSERT(插入/更新)，获取insert_id，然后从 DETAIL 表，然后插入我需要的表(当然，将fk引用 SUMMARY ).
What I have my code doing is I do an UPSERT (insert/update) to the SUMMARY table, grab the insert_id and then delete the records from the DETAIL table, then insert the ones I need (referencing SUMMARY with the fk of course).
但是，在没有对Summary数据进行任何更改的情况下-insert_id返回0.这似乎是预期的，因为没有行被更新/插入.
However, in the instance that there are no changes to SUMMARY data - insert_id returns 0. This seems expected as no row was updated/inserted.
这是我的问题:
在进行此更新/删除/插入尝试之前，我应该完整读取表并比较数据吗?还是有另一种巧妙的方法来获取 SUMMARY 的id，而这是UPSERT尝试的重复?我觉得我的用户将几乎总是在使用此API时更改 SUMMARY 和 DETAIL 数据.
Should I be doing a full read of the tables and comparing data prior to this update/delete/insert attempt? Or is there another nifty way of grabbing the id of the SUMMARY that was a duplicate of the UPSERT attempt? I feel that my users will 'almost' ALWAYS be changing the SUMMARY and DETAIL data when using this API.
这里正确的编码做法是什么?每次都值得额外阅读吗?还是只有insert_id = 0才应该阅读?
What is the correct coding practice here? Is the extra read worth it every time? Or should I read only if insert_id = 0?
有什么想法吗?我最大的问题是，我不知道读写的幅值差在哪里-尤其是因为我不相信在不更改值的情况下API不会被调用太多.
Thoughts? My biggest problem is that I don't know what the magnitude difference of a read vs a write is here - especially since I don't believe the API will be called much without having changed values.
同样，我的选择是:
-
- 读取db并进行比较以查看是否存在差异
- 相应地插入/更新
- Read db and compare to see if there is a diff
- Insert/Update accordingly
- 尝试插入/更新.
- 如果(insert_id = 0)，则读取db以获取详细信息表的摘要ID
- 复制过程
- 尝试插入/更新
- 使用?获取重复记录的摘要摘要ID(并防止插入/更新)
- 使用ID完成步骤.
推荐答案
如果您需要的id是一个auto_increment字段，则选项100(用1个执行动作执行DB内的所有操作)100％的时间.这是您需要的常规SQL结构:
If the id you need is an auto_increment field, option 4 (do everything inside DB with 1 execute action) 100% of the time. This is the general SQL structure you need:
Insert into summary (primaryKey, fieldA, fieldB) values (NULL, valueA, valueB) on duplicate key update primaryKey=LAST_INSERT_ID(primaryKey), fieldA = fieldA, fieldB=fieldB;
如果您随后执行SELECT LAST_INSERT_ID()，它将为您提供成功插入的ID或(如果有重复的话)重复的条目的ID.因此，请执行以下操作:
If you then do SELECT LAST_INSERT_ID() it'll give you either the successful inserted id or ,if duplicate, the duplicate entrie's id. So do something like:
delete from detail where summary_id = LAST_INSERT_ID();
这篇关于PHP MySQLi-插入或更新后的insert_id 0-我应该先阅读吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
