Using “Affected Rows” in MySQL

Overview

Have you ever thought about using “Affected Rows” in your application?

As you all know, “Affected Rows” is the number of data rows that actually affected by DML. It is usally used to check to see how many rows has been changed since DML(insert/update/delete) was executed.

In this post I would like to talk about how to use “Affected Rows” to solve various requirements in a fun way.

Affected rows in MySQL

As I mentioned before, “Affected Rows” in MySQL means the row that has really changed.

mysql> insert into test values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> update test set j = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> update test set j = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This looks nothing in your application, but if you think a little differently, you can get a free lunch, reducing amounts of queries. Let me explain about this. 🙂

Service requirements

If you have received the following requirements from the service planner:

  • Reset count every day at 00:00
  • Participate only 5 times in a certiain cycle

Normally, it would be implemented by bundling transactions as shown below.(This is just psudo code.)


try{
    execute("BEGIN");
    row = execute("SELECT * FROM user_event WHERE user_id = 100 FOR UPDATE");

    // 1. Check Time
    if(last_applied_time == CURRENT_DATE){

        // 2. Check Count
        if(apply_count < 5){
            execute("UPDATE user_event SET apply_count = apply_count + 1 WHERE user_id = 100");
        }
    }else{

        // 3. Reset Count
        execute("UPDATE user_event SET apply_count = 1 WHERE user_id = 100");
    }
    execute("COMMIT");
}catch(Exception e){
    execute("ROLLBACK");
}

Of course, this is not wrong, but I want to solve this service requirement more fun aspect with “affected rows”.

My own solution

Let’s create the following table before I explain it.

CREATE TABLE `user_event` (
  `user_id` int(11) NOT NULL,
  `apply_count` int(11) NOT NULL,
  `last_applied_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB

It’s just a very simple table with an event apply count based on user with last event applied time (timestamp).Now, let’s create the query as shown below to suit planner’s requirements.

First, there must be no data, so we can make INSERT query as below.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1);

Second, let’s add more meaning to the query as follows to increase Count every time user participate event.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1)
    ON DUPLICATE KEY UPDATE apply_count = apply_count + 1

By the way, the planner order to initialize counter value every day.
If the last entry date is the same as today’s date, increase the apply_count. In order to satisfy a date requirement (1. Check Time), branch processing is performed by if as follows.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE
       apply_count = if(date(last_applied_time) = current_date, apply_count + 1, 1)

Let’s set the event participate limit condition as follows. The “apply_count” is only incremented when the current value is less than 5. So, we make the final query, as below.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE
       apply_count = if(date(last_applied_time) = current_date, if(apply_count < 5, apply_count + 1, apply_count), 1)

Now, we solved these complex requirements in just a single line only. Then, let’s do some fun tests.:-)

I’m not a patient guy who could not wait some hours to change date 🙂 , so manipulated my code for changing a reset condition from daily change to every 10secs, as shown below.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE
       apply_count = if(timestampdiff(second, last_applied_time, now()) < 10, if(apply_count < 5, apply_count + 1, apply_count), 1)

And we can get the pretty results as below. Note that, the application can determine whether or not this user is applying (1 or more) or restricted (0) according to the Affected Rows value that is received in the query result.

1. First Insert

1 row affected means there’s no target rows in this table, so this is the first INSERT.

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count |   last_applied_time |
+---------+-------------+---------------------+
|       1 |           1 | 2018-03-25 23:05:38 |
+---------+-------------+---------------------+
1 row in set (0.00 sec)

2. Second Insert in 10s

2 rows affected means, target row exists and row has been updated because of unique constraints. Does it make sense, so far?

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 2 rows affected (0.00 sec)

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count |   last_applied_time |
+---------+-------------+---------------------+
|       1 |           2 | 2018-03-25 23:05:41 |
+---------+-------------+---------------------+
1 row in set (0.00 sec)

3. Insert more than 5 times

Affected rows are returned as 2, and 0 is returned after apply_count is 5.

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count |   last_applied_time |
+---------+-------------+---------------------+
|       1 |           5 | 2018-03-25 23:05:46 |
+---------+-------------+---------------------+
1 row in set (0.00 sec)

4. After 10 seconds

After 10 seconds the limited time, affected rows is returned to 2 and apply_count changed to 1. The meaning of affected rows is 2, regardless of whether initialization or increase, or whatever the user’s apply_count has been increased successfully.

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 2 rows affected (0.00 sec)

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count |   last_applied_time |
+---------+-------------+---------------------+
|       1 |           1 | 2018-03-25 23:05:56 |
+---------+-------------+---------------------+
1 row in set (0.00 sec)

We don’t need to exectue another query to get the result, we can distinguish the result status and make variety message to users in application level. (No transaction, No several queries, Just 1 Query, Free lunch box)

In other words, to summarize, the following behavior can be expected.

Query OK, 1 row affected (0.00 sec)  <= Insert 1 (First)
Query OK, 2 rows affected (0.00 sec) <= Update to 2
Query OK, 2 rows affected (0.00 sec) <= Update to 3
Query OK, 2 rows affected (0.00 sec) <= Update to 4
Query OK, 2 rows affected (0.00 sec) <= Update to 5
Query OK, 0 rows affected (0.00 sec) <= No action
Query OK, 2 rows affected (0.00 sec) <= Update to 1 (Initialized)

However, in Java, you need to add jdbc parameter useAffectedRows=true to get this nice result. (Check! https://bugs.mysql.com/bug.php?id=39352)

Conclusion

If there are no changes to the data, then there is only 0 Affected Rows. This feature is depended on the DBMS, it is also (probably) dependent on MySQL.

However, if you understand and use these behaviors, you can solve some kind of problems of getting more comfortable. In addition to limiting the number of event “apply count” for a certain period of time, it may be also necessary to keep “summarized data” on a daily or monthly basis. If you think about it, you can easily implement an initialized sequence using affected rows. There is already something that I made roughly, but I will arrange it next time.

First posting in English, it’s so tough.
Thanks, have a nice day~ 🙂