blocks|key|1245339|text|嗯，我很惊讶，在这些答案中，我没有看到最简单的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245340|假设item_id是items表中的整数标识列，您使用以下语句更新行：|offset|length|style|CODE|1245341|UPDATE+items
SET+qwe+=+'qwe'
WHERE+asd+=+'asd';|code-block|syntax|javascript|1245342|然后，要知道语句后面最新受影响的行，您应该稍微将语句更新为以下内容：|1245343|UPDATE+items
SET+qwe+=+'qwe',
++++item_id=LAST_INSERT_ID(item_id)
WHERE+asd+=+'asd';
SELECT+LAST_INSERT_ID();|1245344|如果只需要更新实际更改的行，则需要通过LAST_INSERT_ID检查行中的数据是否要更改来添加item_id的条件更新。|1245345|entityMap^0|0|2|7|A|5|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]|$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

Hm, I am surprised that among the answers I do not see the easiest solution.

Suppose, <code>item_id</code> is an integer identity column in <code>items</code> table and you update rows with the following statement:

<pre><code>UPDATE items
SET qwe = 'qwe'
WHERE asd = 'asd';
</code></pre>

Then, to know the latest affected row right after the statement, you should slightly update the statement into the following:

<pre><code>UPDATE items
SET qwe = 'qwe',
 item_id=LAST_INSERT_ID(item_id)
WHERE asd = 'asd';
SELECT LAST_INSERT_ID();
</code></pre>

If you need to update only really changed row, you would need to add a conditional update of the item_id through the LAST_INSERT_ID checking if the data is going to change in the row.

blocks|key|1245225|text|这是与Salman+A的答案相同的方法，但这是您实际需要的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245226|首先，编辑您的表，使其在行被修改时自动跟踪。如果只想知道行最初插入的时间，请删除最后一行。|1245227|ALTER+TABLE+mytable
ADD+lastmodified+TIMESTAMP+
++++DEFAULT+CURRENT_TIMESTAMP+
++++ON+UPDATE+CURRENT_TIMESTAMP;|code-block|syntax|javascript|1245228|然后，要找出最后更新的行，可以使用以下代码。|1245229|SELECT+id+FROM+mytable+ORDER+BY+lastmodified+DESC+LIMIT+1;|1245230|这段代码全部取自MySQL+vs+PostgreSQL:+Adding+a+'Last+Modified+Time'+Column+to+a+Table和MySQL+Manual:+Sorting+Rows。我刚组装好的。|offset|length|1245231|entityMap|0|LINK|mutability|MUTABLE|url|http://pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-a-last-modified-column-to-a-table.html|1|http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html^0|0|0|0|0|0|8|1W|0|25|Q|1|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|15|8|@]|9|@$O|16|P|17|1|18]|$O|19|P|1A|1|1B]]|A|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]]]

This is the same method as Salman A's answer, but here's the code you actually need to do it.

First, edit your table so that it will automatically keep track of whenever a row is modified. Remove the last line if you only want to know when a row was initially inserted.

<pre><code>ALTER TABLE mytable
ADD lastmodified TIMESTAMP 
 DEFAULT CURRENT_TIMESTAMP 
 ON UPDATE CURRENT_TIMESTAMP;
</code></pre>

Then, to find out the last updated row, you can use this code.

<pre><code>SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1;
</code></pre>

This code is all lifted from <a href="http://pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-a-last-modified-column-to-a-table.html" rel="noreferrer">MySQL vs PostgreSQL: Adding a 'Last Modified Time' Column to a Table</a> and <a href="http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html" rel="noreferrer">MySQL Manual: Sorting Rows</a>. I just assembled it.

blocks|key|1245361|text|查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245362|$sqlQuery+=+"UPDATE+
++++++++++++update_table+
++++++++SET+
++++++++++++set_name+=+'value'+
++++++++WHERE+
++++++++++++where_name+=+'name'
++++++++LIMIT+1;";|code-block|syntax|javascript|1245363|PHP函数：|1245364|function+updateAndGetId($sqlQuery)
{
++++mysql_query(str_replace("SET",+"SET+id+=+LAST_INSERT_ID(id),",+$sqlQuery));
++++return+mysql_insert_id();
}|1245365|这是我的工作;)|1245366|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Query :

<pre><code>$sqlQuery = "UPDATE 
 update_table 
 SET 
 set_name = 'value' 
 WHERE 
 where_name = 'name'
 LIMIT 1;";
</code></pre>

PHP function:

<pre><code>function updateAndGetId($sqlQuery)
{
 mysql_query(str_replace("SET", "SET id = LAST_INSERT_ID(id),", $sqlQuery));
 return mysql_insert_id();
}
</code></pre>

It's work for me ;)

blocks|key|1245288|text|SET+@uids+:=+"";
UPDATE+myf___ingtable
+++SET+id+=+id
+++WHERE+id+<+5
++AND+(+SELECT+@uids+:=+CONCAT_WS(',',+CAST(id+AS+CHAR+CHARACTER+SET+utf8),+@uids)+);
SELECT+@uids;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1245289|我不得不转换身份(不知道为什么)...或者我得不到@uids的内容(这是一个blob)，不过非常感谢你对我的回答！|unstyled|1245290|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SET @uids := "";
UPDATE myf___ingtable
 SET id = id
 WHERE id &lt; 5
 AND ( SELECT @uids := CONCAT_WS(',', CAST(id AS CHAR CHARACTER SET utf8), @uids) );
SELECT @uids;
</code></pre>

I had to CAST the id (dunno why)... or I cannot get the @uids content (it was a blob)
Btw many thanks for Pomyk answer!

blocks|key|1245071|text|最后更新的行的ID与您在'updateQuery‘中查找和更新该行所使用的ID相同。因此，只需将该ID保存(调用)到您想要的任何位置。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245072|last_insert_id()依赖于AUTO_INCREMENT，但上次更新的ID不是。|offset|length|style|CODE|1245073|entityMap^0|0|0|G|J|E|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|P|8|@]|9|@]|A|$]]]|I|$]]

ID of the last updated row is the same ID that you use in the 'updateQuery' to found &amp; update that row. So, just save(call) that ID on anyway you want.

<code>last_insert_id()</code> depends of the <code>AUTO_INCREMENT</code>, but the last updated ID not.

blocks|key|1459904|text|嘿，我只是需要一个这样的技巧--我用一种不同的方法解决了它，也许它对你有用。请注意，这不是一个可伸缩的解决方案，对于大型数据集来说，这将是非常糟糕的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1459905|将你的查询分成两部分-|1459906|首先，选择要更新的行的in，并将其存储在临时表中。|1459907|其次，使用update语句中的条件更改为where+id+in+temp_table进行原始更新。|offset|length|style|CODE|1459908|为了确保并发性，您需要在这两个步骤之前对表执行lock操作，然后在结束时释放锁。|1459909|同样，对于以limit+1结尾的查询，这也适用于我，因此我甚至不使用临时表，而只是使用一个变量来存储第一个select的结果。|1459910|我更喜欢这种方法，因为我知道我总是只更新一行，而且代码很简单。|1459911|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html^0|0|0|0|K|M|0|N|4|0|0|6|7|1H|6|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|11|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|12|8|@$H|13|I|14|J|K]]|9|@]|A|$]]|$1|L|3|M|5|6|7|15|8|@]|9|@$H|16|I|17|1|18]]|A|$]]|$1|N|3|O|5|6|7|19|8|@$H|1A|I|1B|J|K]|$H|1C|I|1D|J|K]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1E|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]]]

Hey, I just needed such a trick - I solved it in a different way, maybe it'll work for you. Note this is not a scalable solution and will be very bad for large data sets.

Split your query into two parts - 

first, select the ids of the rows you want to update and store them in a temporary table. 

secondly, do the original update with the condition in the update statement changed to <code>where id in temp_table</code>.

And to ensure concurrency, you need to <a href="http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html" rel="nofollow noreferrer">lock</a> the table before this two steps and then release the lock at the end.

Again, this works for me, for a query which ends with <code>limit 1</code>, so I don't even use a temp table, but instead simply a variable to store the result of the first <code>select</code>. 

I prefer this method since I know I will always update only one row, and the code is straightforward.

blocks|key|1245418|text|我的解决方案是，首先用select命令决定"+id“(+@uids+)，然后用@uids更新这个id。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245419|SET+@uids+:=+(SELECT+id+FROM+table+WHERE+some+=+0+LIMIT+1);
UPDATE+table+SET+col+=+1+WHERE+id+=+@uids;SELECT+@uids;|code-block|syntax|javascript|1245420|它对我的项目起作用了。|1245421|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

My solution is , first decide the "id" ( @uids ) with select command and after update this id with @uids .

<pre><code>SET @uids := (SELECT id FROM table WHERE some = 0 LIMIT 1);
UPDATE table SET col = 1 WHERE id = @uids;SELECT @uids;
</code></pre>

it worked on my project.

blocks|key|1459741|text|如果您只执行插入操作，并且希望从同一会话中插入一个，请按照peirix的回答进行操作。如果您正在进行修改，则需要修改数据库架构以存储最近更新的条目。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1459742|如果你想要上一次修改的id，它可能来自不同的会话(例如，不是由当前运行的PHP代码完成的，而是响应不同请求完成的)，你可以向名为last_modified的表中添加一个时间戳列(有关信息，请参阅http://dev.mysql.com/doc/refman/5.1/en/datetime.html+)，然后在更新时设置last_modified=CURRENT_TIME。|offset|length|1459743|设置之后，您可以使用如下查询:+SELECT+id+FROM+table+ORDER+BY+last_modified+DESC+LIMIT+1；以获取最近修改过的行。|1459744|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/datetime.html^0|0|2P|1G|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

If you are only doing insertions, and want one from the same session, do as per peirix's answer. If you are doing modifications, you will need to modify your database schema to store which entry was most recently updated.

If you want the id from the last modification, which may have been from a different session (i.e. not the one that was just done by the PHP code running at present, but one done in response to a different request), you can add a TIMESTAMP column to your table called last_modified (see <a href="http://dev.mysql.com/doc/refman/5.1/en/datetime.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.1/en/datetime.html</a> for information), and then when you update, set last_modified=CURRENT_TIME.

Having set this, you can then use a query like:
 SELECT id FROM table ORDER BY last_modified DESC LIMIT 1;
to get the most recently modified row.

blocks|key|1459849|text|不需要这么长的Mysql代码。在PHP中，查询应该如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1459850|$updateQuery+=+mysql_query("UPDATE+table_name+SET+row='value'+WHERE+id='$id'")+or+die+('Error');
$lastUpdatedId+=+mysql_insert_id();|code-block|syntax|javascript|1459851|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

No need for so long Mysql code. In PHP, query should look something like this:

<pre><code>$updateQuery = mysql_query("UPDATE table_name SET row='value' WHERE id='$id'") or die ('Error');
$lastUpdatedId = mysql_insert_id();
</code></pre>

How do I get the ID of the last updated row in MySQL using PHP?

How to get ID of the last updated row in MySQL?

数据库

MySQL

如何使用PHP获取MySQL中最后更新的行的ID？

问如何获取MySQL中最后一次更新的行的ID？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取MySQL中最后一次更新的行的ID？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取MySQL中最后一次更新的行的ID？
EN