Why I get a success message saying `2 rows inserted` after executing the query more than once?

Question

I have 2 tables with one-to-one relationship:

               post_views table
    ___________________________________
   |        |             |           |
   |   id   |   post_id   |   views   |
   |________|_____________|___________|




                   posts table
    __________________________________________
   |        |           |          |         |
   |   id   |   title   |   text   |    ..   |
   |________|___________|__________|_________|

post_id from post_views table is joined with id from posts table.

The id in both tables is primary and auto incremented, And the post_id is unique.

Here is a screenshot of the indexes for post_views: https://prnt.sc/k6no10

Each post should has only one row in post_views table.

I run this query to insert a new row or increase the views, If that post_id exists:

INSERT INTO post_views (`post_id`, `views`) VALUES (1, 1) ON DUPLICATE KEY UPDATE `views` = `views`+1

It's executed successfully and a new row is inserted:

 ____________________________________
|          |             |           |
|    id    |   post_id   |   views   |
|__________|_____________|___________|
|          |             |           |
|    1     |     1       |    1      |
|          |             |           |
|__________|_____________|___________|

Then when I run the same query again to increase the views, I get a success message saying that 2 rows inserted and the row is now:

____________________________________
|          |             |           |
|    id    |   post_id   |   views   |
|__________|_____________|___________|
|          |             |           |
|    1     |     1       |    2      |
|          |             |           |
|__________|_____________|___________|

And that's what I want, but if I run the query with a new post_id:

INSERT INTO post_views (`post_id`, `views`) VALUES (2, 1) ON DUPLICATE KEY UPDATE `views` = `views`+1

I get that:

____________________________________
|          |             |           |
|    id    |   post_id   |   views   |
|__________|_____________|___________|
|          |             |           |
|    1     |     1       |    2      |
|__________|_____________|___________|  
|          |             |           |
|    3     |     2       |    1      |    
|__________|_____________|___________|

The id is 3 instead of 2, So each time I run the query with the same post_id is like I'm inserting a new row with an id.

So if I run the query with post_id = 3 three times, The news id will be 7.

Is that's normal?

Answer 1

This is a non-issue. The ids in a table are not intended to be sequential with no gaps. Ensuring such logic is very expensive. It requires locking the whole table for the inserts. Database engines wisely do not do this.

In a single threaded environment -- no concurrent transactions -- you can get around this by doing separate update and insert commands:

update post_views
    set views = views + 1
    where post_id = 1;

insert into post_views (post_id, views)
    select post_id, 1
    from (select 1 as post_id) x
    where not exists (select 1 from post_views pv where pv.post_id = x.post_id);

The where prevents the insert from even attempting an update, so no new id is generated. However, I strongly advise you not to take this approach. It is not thread-safe. In a concurrent processing world, it will not guarantee what you want.

Your case is even stranger. You have no need for the id column in post_views. The post_id can be both a primary key and a foreign key:

create table post_views (
    post_id int primary key,
    views int default 0,
    constraint fk_post_views_post_id foreign key (post_id) references posts(id)
);

If you set the data up this way, you won't have the id, and you won't have the problem at all. Or, you could just add views into the posts table and deal with one table.