MySQL - Update view column every time the row is SELECTed

Question

Hi.. I have a table that has a "view_count" column.

I would like to increment said column every time I select the row (even with a generic SELECT, such as SELECT * FROM table WHERE 1.. This would increment by 1 the view_count of all rows).

Is there a way to do it automatically "server-side" (where mysql is the server and my application is the client), ie without an UPDATE every time?

EDIT: Since a couple of people asked me why I wanted to do this, and a few misunderstood my request, imagine that's a forum software, and the table is the thread table. Each thread has a view count, and you want to update it every time you display the thread in the main page (I know usually the threads' view count are only updated when you actually view it, but it's the best example i could come up with, my particular case is kinda long and complicated T_T)

Answer 1

You can do it in the session scope:

SET @view_count := 0;

SELECT  v.*,
        @view_count := @view_count + 1 AS view_count
FROM    view

You will have an incrementing value for each row viewed:

SELECT  q.*,
        @view_count := @view_count + 1 AS view_count
FROM    (
        SELECT  1 AS col
        UNION ALL
        SELECT  2 AS col
        ) q;

-- -- 
 1  1
 2  2

SELECT  q.*,
        @view_count := @view_count + 1 AS view_count
FROM    (
        SELECT  1 AS col
        UNION ALL
        SELECT  2 AS col
        ) q;

-- -- 
 1  3
 2  4

This, however, will only count the views within your session, not the total views.

Answer 2

Can't be done. You basically want a trigger on SELECT, and triggers are only supported for INSERT, UPDATE, and DELETE. The closest you could get would be to run all your interactions with the table through a stored procedure, and it sounds like you want this behavior to be enforced under all conditions.

Answer 3

I don't believe there's a simple way to do this server-side; triggers only execute on UPDATEs, INSERTs, and DELETEs.

Answer 4

Why not just issue one SQL statement like so:

UPDATE tableName SET viewCount = viewCount + 1; SELECT tableName.col1, tableName.col2;

That could even be dynamically created with ease in any language.

Answer 5

Stored procedures, but it's a pain. I would implement security elsewhere.