mysql从更新返回结果 [英] mysql return results from update

问题描述

我想从mysql数据库中选择一堆行，并更新那些被选中的行的属性(这是一种我已经读过这些"标志).

I want to select a bunch of rows from a mysql database and update the viewed attribute of those once selected (this is a kind of 'I have read these' flag).

最初我做了这样的事情:

Initially I did something like this:

update (
        select a, b, c
        from mytable
        where viewed = '0'
        )
set viewed = '1';

这将很好地选择行并根据需要更新其已查看属性.但是它不会从子查询中返回选定的行.

This selects the rows nicely and updates their viewed attribute as required. But it does not return the selected rows from the subquery.

是否可以添加一个子句，或者是否需要存储子查询等?我确实考虑过交易，但最终还是遇到了同样的问题.我没有尝试过存储过程...

Is there a clause I can add, or perhaps I need to store the subquery, etc...? I did consider a transaction but I ended up with the same problem. I have not tried a stored procedure...

请有人为我做上面的事情提供建议/正确的方向，但还可以从子查询中返回选定的表吗?

Please can someone advise / point me in the right direction on how to do what I do above but in addition return the selected tables from the subquery?

谢谢.

更新:

@ Barmar，@ a_horse_with_no_name，@ fancyPants和@George Garchagudashvil指出...

As pointed out by @Barmar, @a_horse_with_no_name, @fancyPants and @George Garchagudashvil...

在MySQL中，如果要返回所选的行，则必须使用两个语句进行选择和更新，而不是像我最初的文章中所述的嵌套语句.

In MySQL you have to use two statements to select and update, and not a nested statement as in my initial post, if you want to return the selected rows.

例如

begin;
  select a, b, c
    from mytable
    where viewed = '0';
  update mytable
    set viewed = '1'
    where viewed = '0';
commit;

谢谢大家.

推荐答案

我将创建一个简单的函数:

I would create a simple function:

DELIMITER $$

DROP FUNCTION IF EXISTS `mydb`.`updateMytable`$$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `mydb`.`updateMytable`() RETURNS TEXT 

    BEGIN

        SET @updated := '';

        UPDATE mytable
            SET viewed = 1
        WHERE viewed = 0
            AND ( 
                SELECT @updated := CONCAT_WS(',', @updated, id) 
            ) != ''
        ;

        RETURN TRIM(LEADING ',' FROM @updated); 

    END$$
DELIMITER ;

这将更新表并返回串联的ID.

which updates tables and returns concatenated ids.

从php中，您称之为:

From php you call this:

SELECT mydb.updateMytable()

您会得到ID的搅动:1,2,7,54,132等...

and you get ids in a stirng: 1,2,7,54,132 etc...

更新:

我的函数返回的字符串包含逗号分隔的ID: '1,5,7,52,...'这些ID仅是在函数调用期间已更新的

my function is returning string containing comma separated ids: '1,5,7,52,...' these ids are only which would have been updated during the function call,

更好的php-mysql示例是(您可能会使用PDO):

better php-mysql example would be (you may and would use PDO):

$query = "SELECT mydb.updateMytable()";
$res = mysql_query($query);
$arr = mysql_fetch_array($res);

$ids = explode(',', $arr[0]);

// now you can do whatever you want to do with ids
foreach ($ids as $id) 
{
    echo "Hoorah: updated $id\n";
}

还请记住根据您的数据库名称更改mydb和mytable

also remember to change mydb and mytable according to your database names

最终

由于您需要更复杂的功能，因此只需运行两个查询:

because you need more complex functionality, simply run two query:

首次运行:

SELECT a, b, c
FROM mytable
WHERE viewed = 0

下一次运行:

UPDATE mytable 
    SET viewed = 1 
WHERE viewed = 0

这篇关于mysql从更新返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！