Auto Increment column on SELECT Mysql 8-CodePudding

MySql 8

I have this table:

id	name	count
1	name1	3
2	name2	4

I'm looking for a way to increment count each time I get the count value.

Something like:

SELECT count FROM table WHERE id = 1 AND /*increase count value*/;

So this request will return: 3

Then if I do:

SELECT * FROM table;

I got:

id	name	count
1	name1	4
2	name2	4

I'm assuming I have to use an Index but I can't figure out how.

Thank you

CodePudding user response：

BEGIN
    DECLARE x INT;
    SELECT count INTO x FROM table WHERE id = p_id;
    IF (x = 1) THEN
        UPDATE table SET count = count   1 WHERE id = p_id;
        CALL increment(p_id);
    END IF;
END;
CREATE PROCEDURE increment(IN p_id INT)
BEGIN
    DECLARE x INT;
    SELECT count INTO x FROM table WHERE id = p_id;
    IF (x = 1) THEN
        UPDATE table SET count = count   1 WHERE id = p_id;
        CALL increment(p_id);
    END IF;
END;

Send each id into CALL IncrementCount(id) and it will increment the count

SELECT * FROM table;
CALL IncrementCount(1);
CALL IncrementCount(2);
SELECT * FROM table;

CodePudding user response：

As @Xammax didn't suit my need, I change my database from mysql to postgre and use this answer: Return updated row attributes on UPDATE