SQL插入行并将插入的自动增量ID复制到另一列 [英] SQL Insert Row and copy inserted Auto-Increment id to another column

问题描述

我正在使用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:

1. 将column设置为last_insert_id()的INSERT行;
2. INSERT行；使用SELECT last_insert_id()来更新column;
3. SELECT last_insert_id();将auto-increment column和column设置为last_insert_id()+ 1
的INSERT行

1. INSERT row with column set as last_insert_id();
2. INSERT row; UPDATE column with SELECT last_insert_id();
3. SELECT last_insert_id(); INSERT row with auto-increment column and column set as last_insert_id()+1

当我将所选值插入自动增量列时会发生什么?自动增量生成器会从我插入的数字开始计数吗?如果我使用以前使用过的值(但现在不存在)了，并且存在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复制到另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！