我正在使用PHP和MySQL.
I am using PHP and MySQL.
将新行插入具有自动递增id列的表中. 插入此新行时,我想将新插入的行的自动增量ID#复制到同一行/条目中的另一列中. 创建后,新的条目/行应具有自动递增的id列,以及具有相同编号的另一列.
Insert a new row into a table with an auto-increment id column. While inserting this new row, I would like to copy the new inserted row's auto-increment id # into another column in the same row/entry. Upon creation, the new entry/row should have the auto-incremented id column and another column with the same number.
我最大的担忧: 即使在服务器上可能发生其他连接的其他事务,我也需要所有这些正确且可靠地发生.
My biggest concern: I need all of this to happen correctly and reliably, even while other transactions from other connections could be taking place on the server.
我对last_insert_id()知之甚少,我担心无法可靠地使用它来满足我的需求... 我是否会遇到过自动递增ID#已经增加的情况(也许是由于来自另一个连接的某些其他插入查询)而现在我得不到正确的ID#的情况? (我不太完全了解按每个连接向客户端提供last_insert_id()的含义.)
I know only a little about last_insert_id()... and I am afraid of being able to use this reliably for my needs... Will I ever run into a situation where the auto-increment id # will have already incremented (due to some other insert query from another connection perhaps) and now I will not get the correct id #? (I did not quite fully understand what it means when the last_insert_id() is given to the client on a per-connection basis).
last_insert_id()是否可以与事务配合使用,因为在使事务回滚时它们变得不确定? (如果回滚了另一笔交易,然后我立即运行此脚本,那么我的脚本会为last_insert_id()返回NULL吗?)
Will last_insert_id() play nice with transactions since they become undefined when the transaction is made to rollback? (If another transaction is rolled back and then I run this script immediately after, will my script return NULL for last_insert_id()?)
我不了解mysql_insert_id();如何使用它以及它是否对我有帮助.
I do not understand mysql_insert_id(); how to use it and whether or not it will help me.
到目前为止,我已经考虑过:
So far, I have thought about:
当我将所选值插入自动增量列时会发生什么?自动增量生成器会从我插入的数字开始计数吗?如果我使用以前使用过的值(但现在不存在)了,并且存在ID编号在该值之后的记录,该怎么办?
What happens when I insert a chosen value into the auto-increment column? Will the auto-increment generator start counting from the number I insert? What if I use a value that has been used before (but doesn't exist anymore) and there exists records with id # that come after that value?
表或行锁定是否可以实现我想要的行为?
Will table or row locking allow me to achieve my desired behavior?
做这样的事情的正确/正确方法是什么?
What is the proper/correct way to do something like this?
last_insert_id与客户端无关,并将返回该ID. 该客户端最后插入的行,因此您不必担心 关于另一连接上的用户与 数据库.
last_insert_id is client independent and will return the ID for the last inserted row from that client, therefore you do not need to worry about the case that a user on another connection transacts with the database.
我仍然不完全了解那是什么意思...
I still do not fully understand what that means...
对于基本场景:
INSERT row where id = 1; SELECT last_insert_id(); outputs 1 Person A makes a connection to the db; SELECT last_insert_id(); outputs 1. Person B makes a connection to the db; SELECT last_insert_id(); outputs 1? Person A INSERT another row where id = 2; Person A SELECT last_insert_id(); outputs 2? Person B SELECT last_insert_id(); outputs... 1 or 2??这是怎么回事? 还有一个让我真正担心的场景:
What happens here? And a SCENARIO that really concerns me:
Person A makes a connection with the db; Person A SELECT last_insert_id(); outputs 1 Person A INSERT row where id = 2; Person A SELECT last_insert_id(); outputs 2 Person B makes a connection with the db; Person B SELECT last_insert_id(); outputs 2 Person B INSERT row where id = 3; Person A SELECT last_insert_id(); outputs 2?? Person B SELECT last_insert_id(); outputs 3??在这种情况下,人员A的last_insert_id()落后一个计数. 如果是这样,那么我将无法使用我的#3方法.
In this case, Person A's last_insert_id() is one count behind. If this is true, then I will not be able to use my #3 method.
请在可能出现错误的地方为我更正输出内容.
Please correct my outputs for me wherever I may be wrong.
推荐答案您应该查看本文关于MySQL last_insert_id
You should check out this article regarding MySQL last_insert_id
last_insert_id是与客户端无关的,它将返回该客户端最后插入的行的ID,因此您不必担心其他连接上的用户与数据库进行事务处理的情况.
last_insert_id is client independent and will return the ID for the last inserted row from that client, therefore you do not need to worry about the case that a user on another connection transacts with the database.
更多推荐
SQL插入行并将插入的自动增量ID复制到另一列
发布评论