The INCR function increments the column's value given as a parameter for a SELECT statement by 1. The DECR function decrements the value of the column by 1.
SELECT [ qualifier ] select_expression
[ { TO | INTO } variable [ {, variable }...; ] ]
...;
select_expression :
*
table_name. *
[expression | counter_expression] [ {, expression |
counter_expression}...]
counter_expression :
INCR(path_expression)
The INCR and DECR functions are called "click counters" and can be effectively used to increase the number of post views for a Bulletin Board System (BBS) type of web service. In a scenario where you want to SELECT a post and immediately increase the number of views by 1 using an UPDATE statement, you can view the post and increment the number at once by using the INCR function in a single SELECT statement.
The INCR function increments the column value specified as an argument. Only integer type numbers can be used as arguments. If the value is NULL, the INCR function returns the NULL. That is, a value must be valid in order to be incremented by the INCR function. The DECR function decrements the column value specified as a parameter.
If an INCR function is specified in the SELECT statement, the COUNTER value is incremented by 1 and the query result is displayed with the values before the increment. Furthermore, the INCR function does not increment the value of the tuple affected by the query process but rather the one affected by the final result.
Suppose that the following three rows of data are inserted into the 'board' table.
CREATE TABLE board (
id INT, title VARCHAR(100), content VARCHAR(4000), read_count INT );
INSERT INTO board VALUES (1, 'aaa', 'text...', 0);
INSERT INTO board VALUES (2, 'bbb', 'text...', 0);
INSERT INTO board VALUES (3, 'ccc', 'text...', 0);
The following example shows how to increment the value of the 'read_count' column in a data whose 'id' value is 1 by using the INCR function.
SELECT content, INCR(read_count) FROM board WHERE id = 1;
content read_count
===================================
'text...' 0
In the example, the column value becomes read_count + 1 as a result of the INCR function in the SELECT statement. You can check the result using the following SELECT statement.
SELECT content, read_count FROM board WHERE id = 1;
content read_count
===================================
'text...' 1