更新表，然后在mySQL中返回更新的行 [英] Update a table then return updated rows in mySQL

问题描述

我有以下查询:

SELECT script FROM d_stages WHERE id IN
(SELECT g.stage + :i FROM games g INNER JOIN sessions s ON
g.expired = 0 AND s.user = g.user AND s.token = :token)

1. 内部SELECT通过给定的令牌在会话"表中查找用户ID，然后为该用户寻找未过期的游戏.
2. 它返回已修改的游戏阶段(根据:i值递增，递减或不做任何修改).
3. 外部SELECT为字典中找到的阶段返回一个唯一的脚本.

很明显，由于游戏已经发展，修改后的阶段编号(g.stage +:i)应该写回到数据库.我可以将SELECT script替换为SELECT script, g.stage + :i，并在另一个(UPDATE)查询中使用修改后的数字，但是也许我可以在单个查询中执行相同的操作?

Obviously, the modified stage number (g.stage + :i) should be written back to the DB, since the game has evolved. I can replace SELECT script with SELECT script, g.stage + :i, and use the modified number in another (UPDATE) query, but maybe I can do the same within a single query?

我的DBMS是mySQL 5.5.

My DBMS is mySQL 5.5.

此致

推荐答案

我认为，只有一种方法:

I think, there is only one way:

CREATE DEFINER=`root`@`localhost` PROCEDURE `goto_stage`(IN `inc` INT, IN `session_token` VARCHAR(64))
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE new_stage INT;
    SELECT g.stage + inc INTO new_stage FROM games g INNER JOIN sessions s ON
            g.expired = 0 AND s.user = g.user AND s.token = session_token;
    UPDATE games SET stage = new_stage;
    SELECT script FROM d_stages WHERE id = new_stage;
END

这篇关于更新表，然后在mySQL中返回更新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！