通过MySQL多表联查实现类似朋友圈动态的实例

前言

这篇文章，主要用最基础的方式进行多表联查，实现类似朋友圈好友动态的那种案例
这里只是做了最简单的动态的列表展示，锻炼编写SQL思路

需求分析

有这么个项目，类似朋友圈，需要展示用户好友和自己发布的动态。

1. 每条动态需要展示头像、昵称、内容(文字、图片、视频)
2. 最新发布的在最前面展示
3. 动态列表中包括用户自己的动态
4. 朋友圈需要动态分页

创建数据库表

需要三个数据表：用户表 users，用户好友关联表 friends，用户动态表 dynamic

用户表 users：用户id、昵称、头像
用户-好友关联表 friends：关联id、用户id、好友id
用户动态表 dynamic：动态id、发布人id、文字内容、视频内容、图片内容

• 用户表 users

  SET NAMES utf8mb4;
  SET FOREIGN_KEY_CHECKS = 0;
   
  -- ----------------------------
  -- Table structure for users
  -- ----------------------------
  DROP TABLE IF EXISTS `users`;
  CREATE TABLE `users`  (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
    `nick_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户昵称',
    `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户头像',
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表'
   
  -- ----------------------------
  -- Records of users
  -- ----------------------------
  INSERT INTO `users` VALUES (1, '大娃', '1.jpg');
  INSERT INTO `users` VALUES (2, '二娃', '2.jpg');
  INSERT INTO `users` VALUES (3, '三娃', '3.jpg');
  INSERT INTO `users` VALUES (4, '四娃', '4.jpg');
   
  SET FOREIGN_KEY_CHECKS = 1;

• 用户-好友关联表 friends

  SET NAMES utf8mb4;
  SET FOREIGN_KEY_CHECKS = 0;
   
  -- ----------------------------
  -- Table structure for friends
  -- ----------------------------
  DROP TABLE IF EXISTS `friends`;
  CREATE TABLE `friends`  (
    `id` int(11) NOT NULL COMMENT '用户和好友的关联id',
    `user_id` int(11) DEFAULT NULL COMMENT '用户id',
    `friend_id` int(11) DEFAULT NULL COMMENT '好友id',
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户-好友关联表'
   
  -- ----------------------------
  -- Records of friends
  -- ----------------------------
  INSERT INTO `friends` VALUES (1001, 1, 2);
  INSERT INTO `friends` VALUES (1002, 2, 1);
  INSERT INTO `friends` VALUES (1003, 1, 3);
  INSERT INTO `friends` VALUES (1004, 3, 1);
  INSERT INTO `friends` VALUES (1005, 2, 3);
  INSERT INTO `friends` VALUES (1006, 3, 2);
  INSERT INTO `friends` VALUES (1007, 2, 4);
  INSERT INTO `friends` VALUES (1008, 4, 2);
  INSERT INTO `friends` VALUES (1009, 3, 4);
  INSERT INTO `friends` VALUES (1010, 4, 3);
   
  SET FOREIGN_KEY_CHECKS = 1;

• 用户动态表 dynamic

  SET NAMES utf8mb4;
  SET FOREIGN_KEY_CHECKS = 0;
   
  -- ----------------------------
  -- Table structure for dynamic
  -- ----------------------------
  DROP TABLE IF EXISTS `dynamic`;
  CREATE TABLE `dynamic`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL COMMENT '发布人id',
    `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '文字内容',
    `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图片内容',
    `video` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '视频内容',
    `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '发布动态时间',
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 1011 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '动态表'
   
  -- ----------------------------
  -- Records of dynamic
  -- ----------------------------
  INSERT INTO `dynamic` VALUES (1001, 1, '我是大娃，发布第1条动态', NULL, NULL, '2020-06-30 16:39:25');
  INSERT INTO `dynamic` VALUES (1002, 2, '我是二娃，发布第1条动态', NULL, NULL, '2020-06-30 16:39:28');
  INSERT INTO `dynamic` VALUES (1003, 3, '我是三娃，发布第1条动态', NULL, NULL, '2020-06-30 16:39:30');
  INSERT INTO `dynamic` VALUES (1004, 4, '我是四娃，发布第1条动态', NULL, NULL, '2020-06-30 16:39:33');
  INSERT INTO `dynamic` VALUES (1005, 1, '我是大娃，发布第2条动态', NULL, NULL, '2020-06-30 16:39:36');
  INSERT INTO `dynamic` VALUES (1006, 1, '我是大娃，发布第3条动态', NULL, NULL, '2020-06-30 16:39:40');
  INSERT INTO `dynamic` VALUES (1007, 2, '我是二娃，发布第2条动态', NULL, NULL, '2020-06-30 16:39:44');
  INSERT INTO `dynamic` VALUES (1008, 3, '我是三娃，发布第2条动态', NULL, NULL, '2020-06-30 16:39:47');
  INSERT INTO `dynamic` VALUES (1009, 4, '我是四娃，发布第2条动态', NULL, NULL, '2020-06-30 16:39:49');
  INSERT INTO `dynamic` VALUES (1010, 2, '我是二娃，发布第3条动态', NULL, NULL, '2020-06-30 16:39:52');
   
  SET FOREIGN_KEY_CHECKS = 1;

关联思路

• 朋友圈每条动态需要展示：用户昵称、头像、内容，发布时间
• 用户昵称和头像存储在用户表；
• 内容和发布时间存储在动态表；
• 列表展示与当前用户及好友的动态；

用户表：存储每个用户的基本信息，例如：姓名、头像、手机号、年龄、家庭住址……
用户-好友关联表：存储用户与用户之间的好友关系
动态表：存储用户发布的动态信息，包括：文字内容、图片内容、视频内容

解决方案

查询user_Id = 1 的朋友圈，分页查询

由于，个人动态与好友的动态获取逻辑是不同的，所以给出下面两种思路

方案一

先查用户好友的动态，再查自己的动态，然后使用 union函数，合并列表

SELECT
    id, user_id, content, video, image, nick_name, avatar, create_time
FROM (
    SELECT
        i.id, i.user_id, i.content, i.video, i.image, u.nick_name, u.avatar, i.create_time
    FROM
        dynamic i
    LEFT JOIN users u ON u.id = i .user_id
    LEFT JOIN friends f ON f.friend_id = u.id
    WHERE
        f.user_id = 1
    UNION
    SELECT
        i.id, i.user_id, i.content, i.video, i.image, u.nick_name, u.avatar, i.create_time
    FROM
        dynamic i
    LEFT JOIN users u ON u.id = i.user_id
    WHERE
        i.user_id = 1
) dynamic
ORDER BY create_time DESC
LIMIT 1,5

方案二

直接查询用户好友的动态和自己的动态

SELECT
    i.id, i.user_id, i.content, i.video, i.image, u.nick_name, u.avatar, i.create_time
FROM
    dynamic i
LEFT JOIN users u ON u.id = i .user_id
LEFT JOIN friends f ON f.friend_id=u.id
WHERE
    f.user_id = 1
OR
    f.friend_id = 1
ORDER BY i.create_time DESC
LIMIT 1, 5

最终结果