为什么我在多次执行查询后收到一条成功消息说“插入了2行”?

dfty9e19  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(213)

这个问题在这里已经有答案了

防止重复键上的innodb自动递增(5个答案)
两年前关门了。
我有两个一对一关系的表:

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

                   posts table
    __________________________________________
   |        |           |          |         |
   |   id   |   title   |   text   |    ..   |
   |________|___________|__________|_________|
``` `post_id` 从 `post_views` 表与 `id` 从 `posts` table。
这个 `id` 在这两个表中都是主的并且是自动递增的,并且 `post_id` 是独一无二的。
以下是索引的屏幕截图 `post_views` : https://prnt.sc/k6no10
每一篇文章中应该只有一行 `post_views` table。
我运行此查询以插入新行或增加视图(如果需要) `post_id` 存在:

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

已成功执行并插入新行:

| | | |
| id | post_id | views |
||_____|_____|
| | | |
| 1 | 1 | 1 |
| | | |
|
__|_________|_______|

然后,当我再次运行相同的查询以增加视图时,会收到一条成功消息 `2 rows inserted` 现在行是:

| | | |
| id | post_id | views |
||_____|_____|
| | | |
| 1 | 1 | 2 |
| | | |
|
__|_________|_______|

这就是我想要的,但是如果我用一个新的 `post_id` :

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

我明白了:

| | | |
| id | post_id | views |
||_____|_____|
| | | |
| 1 | 1 | 2 |
|
|_________|___|
| | | |
| 3 | 2 | 1 |
|
|_____|_______|

这个 `id` 是3而不是2,所以每次运行查询时 `post_id` 就像我在插入一个id为的新行。
如果我用 `post_id = 3` 三次,新闻id是7。
这正常吗?
fcg9iug3

fcg9iug31#

这不是问题。这个 id 表中的符号不应该是连续的,没有间隙。确保这样的逻辑是非常昂贵的。它需要把整个table都锁起来才能装上插件。数据库引擎明智地不这样做。
在单线程环境(没有并发事务)中,可以通过单独执行 update 以及 insert 命令:

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);

这个 where 防止 insert 即使尝试更新,也不会生成新的id。不过,我强烈建议你不要采取这种做法。它不是线程安全的。在并发处理的世界中,它不能保证您想要什么。
你的案子更奇怪。你不需要这个 id 中的列 post_views . 这个 post_id 可以是主键和外键:

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)
);

如果这样设置数据,就不会有id,也就不会有任何问题。或者,你可以加上 views 进入 posts 和一张table打交道。

相关问题