MySQL UPDATE 和 SELECT 一次完成

MySQL UPDATE and SELECT in one pass

提问by Paul Oyster

I have a MySQL table of tasks to perform, each row having parameters for a single task.
There are many worker apps (possibly on different machines), performing tasks in a loop.
The apps access the database using MySQL's native C APIs.

我有一个要执行的 MySQL 任务表，每行都有一个任务的参数。
有许多工作应用程序（可能在不同的机器上），循环执行任务。
应用程序使用 MySQL 的本机 C API 访问数据库。

In order to own a task, an app does something like that:

为了拥有一项任务，应用程序会执行以下操作：

• Generate a globally-unique id (for simplicity, let's say it is a number)

• UPDATE tasks
SET guid = %d
WHERE guid = 0 LIMIT 1

• SELECT params
FROM tasks
WHERE guid = %d

• If the last query returns a row, we own it and have the parameters to run

• 生成一个全局唯一的 id（为简单起见，假设它是一个数字）

• UPDATE tasks
SET guid = %d
WHERE guid = 0 LIMIT 1

• SELECT params
FROM tasks
WHERE guid = %d

• 如果最后一个查询返回一行，我们拥有它并拥有要运行的参数

Is there a way to achieve the same effect (i.e. 'own' a row and get its parameters) in a single call to the server?

有没有办法在对服务器的一次调用中实现相同的效果（即“拥有”一行并获取其参数）？

回答by charles

try like this

像这样尝试

UPDATE `lastid` SET `idnum` =  (SELECT `id` FROM `history` ORDER BY `id` DESC LIMIT 1);

above code worked for me

上面的代码对我有用

回答by Quassnoi

You may create a procedure that does it:

您可以创建一个执行此操作的过程：

CREATE PROCEDURE prc_get_task (in_guid BINARY(16), OUT out_params VARCHAR(200))
BEGIN

  DECLARE task_id INT;

  SELECT id, out_params
  INTO task_id, out_params
  FROM tasks
  WHERE guid = 0
  LIMIT 1
  FOR UPDATE;

  UPDATE task
  SET guid = in_guid
  WHERE id = task_id;

END;

BEGIN TRANSACTION;

CALL prc_get_task(@guid, @params);

COMMIT;

回答by Paulo

If you are looking for a single query then it can't happen. The UPDATE function specifically returns just the number of items that were updated. Similarly, the SELECT function doesn't alter a table, only return values.

如果您正在寻找单个查询，则它不可能发生。UPDATE 函数专门返回已更新的项目数。同样，SELECT 函数不会更改表，只会返回值。

Using a procedure will indeed turn it into a single function and it can be handy if locking is a concern for you. If your biggest concern is network traffic (ie: passing too many queries) then use the procedure. If you concern is server overload (ie: the DB is working too hard) then the extra overhead of a procedure could make things worse.

使用过程确实会将它变成一个单一的函数，如果您关心锁定，它会很方便。如果您最关心的是网络流量（即：传递太多查询），则使用该过程。如果您担心服务器过载（即：数据库工作太辛苦），那么过程的额外开销可能会使事情变得更糟。

回答by Daniel

I don't know about the single call part, but what you're describing is a lock. Locks are an essential element of relational databases.

我不知道单次调用部分，但你所描述的是一个锁。锁是关系数据库的基本要素。

I don't know the specifics of locking a row, reading it, and then updating it in MySQL, but with a bit of reading of the mysql lock documentationyou could do all kinds of lock-based manipulations.

我不知道锁定一行的具体细节，读取它，然后在 MySQL 中更新它，但是通过阅读一些mysql lock 文档，您可以执行各种基于锁的操作。

The postgres documenation of lockshas a great example describing exactly what you want to do: lock the table, read the table, modify the table.

锁的 postgres 文档有一个很好的例子，描述了你想要做什么：锁定表，读取表，修改表。

回答by jogaco

UPDATE tasks
SET guid = %d, params = @params := params
WHERE guid = 0 LIMIT 1;

It will return 1 or 0, depending on whether the values were effectively changed.

它将返回 1 或 0，具体取决于值是否有效更改。

SELECT @params AS params;

This one just selects the variable from the connection.

这只是从连接中选择变量。

From: here

来自：这里

回答by Marco Roy

I have the exact same issue. We ended up using PostreSQL instead, and UPDATE ... RETURNING:

我有完全相同的问题。我们最终改用了 PostreSQL，并且UPDATE ... RETURNING：

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.

可选的 RETURNING 子句使 UPDATE 根据实际更新的每一行计算并返回值。可以计算使用表的列和/或 FROM 中提到的其他表的列的任何表达式。使用表列的新（更新后）值。RETURNING 列表的语法与 SELECT 的输出列表的语法相同。

Example: UPDATE 'my_table' SET 'status' = 1 WHERE 'status' = 0 LIMIT 1 RETURNING *;

例子： UPDATE 'my_table' SET 'status' = 1 WHERE 'status' = 0 LIMIT 1 RETURNING *;

Or, in your case: UPDATE 'tasks' SET 'guid' = %d WHERE 'guid' = 0 LIMIT 1 RETURNING 'params';

或者，就您而言： UPDATE 'tasks' SET 'guid' = %d WHERE 'guid' = 0 LIMIT 1 RETURNING 'params';

Sorry, I know this doesn't answer the question with MySQL, and it might not be easy to just switch to PostgreSQL, but it's the best way we've found to do it. Even 6 years later, MySQL still doesn't support UPDATE ... RETURNING. It might be added at some point in the future, but for now MariaDB only has it for DELETE statements.

抱歉，我知道这不能回答 MySQL 的问题，而且切换到 PostgreSQL 可能并不容易，但这是我们找到的最佳方法。即使 6 年后，MySQL 仍然不支持UPDATE ... RETURNING. 它可能会在未来的某个时候添加，但目前MariaDB 仅将它用于 DELETE 语句。

Edit: There is a task (low priority) to add UPDATE ... RETURNINGsupport to MariaDB.

编辑：有一个任务（低优先级）来添加UPDATE ... RETURNING对 MariaDB 的支持。