mysql 动态sql foreach 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

最终结果