互相关注业务场景数据库表设计

目录

业务场景

业务问题：

数据库表设计：

like（关注表）：

friend（朋友表） 

并发场景下，SQL语句执行逻辑

比较 A 和 B 的大小，如果 A执行下面的逻辑：<>

如果 A>B,则执行下面的逻辑：

SQL写法详解：

参考内容：

---

业务场景

业务上有这样的需求，A、B两个用户，如果互相关注，则成为好友。

业务问题：

在并发场景下，同时有两个人，设置为关注对方，就可能导致无法成功加为朋友关系。如下：

session（a喜欢b）	session（b喜欢a）
begin; select * from friend_like where user_id = B andliker_id = A;(返回空)
	begin; select * from  friend_like where user_id = B andliker_id = A;(返回空)
	insert into  friend_like  (user_id, liker_id) values(B,A);
insert into  friend_like  (user_id, liker_id) values(A,B);
commit;
	commit;

数据库表设计：

like（关注表）：

CREATE TABLE `friend_like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `liker_id` int(11) NOT NULL,
  `relation_ship` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;

friend（朋友表） 

CREATE TABLE `friend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_1_id` int(11) NOT NULL,
  `friend_2_id` int(11) NOT NULL,
  UNIQUE KEY `uk_friend` (`friend_1_id`,`friend_2_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 relation_ship: 

1，表示 user id 关注 liker id;

2，表示liker id 关注 user id;

3，表示互相关注。

并发场景下，SQL语句执行逻辑

比较 A 和 B 的大小，如果 A<B执行下面的逻辑：

begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/*代码中判断返回的 relation_ship，
  如果是1，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
  */
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;/*提交事务*/

如果 A>B,则执行下面的逻辑：

begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/*代码中判断返回的 relation_ship，
  如果是2，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;

SQL写法详解：

这个设计里，让“friend_like”表里的数据保证 user id < liker id，这样不论是A关注B，还是B关注A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突
然后，insert...on duplicate 语句，确保了在事务内部，执行了这个 SQL语句后，就强行占住了这个行锁，之后的 select 判断 relation ship 这个逻辑时就确保了是在行锁保护下的读操作。
操作符“|” 是按位或，连同最后一句 insert 语句里的 ignore，是为了保证重复调用时的幂等性
这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是 like 表里面有一条关于 A和B的记录，而且 relation ship 的值是3，并且 friend 表里面也有了A和B的这条记录。

参考内容：

15 | 答疑文章（一）：日志和索引相关问题 (geekbang.org)