mysql - 跨表的JSON_CONTAINS

Closed. This question is off-topic。它当前不接受答案。
                            
                        
                    
                
                            
                                
                
                        
                            
                        
                    
                        
                            想改善这个问题吗？ Update the question，所以它是on-topic，用于堆栈溢出。
                        
                        2年前关闭。
                                                                                            
                
        
我正在为学校开发一种社交网络。

数据库中有两个表，有两个表：


users，其中包含（varchar）username和（json）friends（其中包含用户与之成为朋友的用户名列表），以及
posts，其中包含（varchar）publisher字段。


如何编写查询以返回发布者与给定用户成为朋友的所有帖子？



发布示例：

| id  | publisher  | originalPublisher | postdate   | content                                                                       | likes |
| 113 | pvaqueiroz | NULL              | 2017-03-13 | {"contentType":"text","content":"DAB \\o\\","attatchments":[]} |     0 |

用户示例：

| id | username   | password                         | email                | fullname      | likes                                         | friends                                               | profpic                                                 |
|  1 | pvaqueiroz | 827ccb0eea8a706c4c34a16891f84e7b | [email protected] | Paulo Queiroz | [31, 30, 0, 63, 68, 85, 89, 91, 92, 109, 114] | ["hacker", "girassol_l", "Dment", "Leel", "Mr Dibre"] | /carlos/posts_res/d11109ac342482457f87611483d661a6.jpeg |

最佳答案

因此，正确的解决方案。

创建语句（跳过字段，因为不需要解释）

CREATE TABLE `users` (
  `id` INT  UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `posts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `post` VARCHAR(45) NULL,
  `user_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_posts_1_idx` (`user_id` ASC),
  CONSTRAINT `fk_posts_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `friends` (
  `user_id` INT UNSIGNED NULL,
  `friend_id` INT UNSIGNED NULL,
  UNIQUE INDEX `user_friend_idx` (`user_id` ASC, `friend_id` ASC),
  INDEX `fk_friends_f_users_idx` (`friend_id` ASC),
  CONSTRAINT `fk_friends_u_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_friends_f_users`
    FOREIGN KEY (`friend_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

每次向用户添加油炸食品时，您只需在friends表中记录一对ID

查询将是：

-- Selecting user friends
SELECT * FROM friends f
JOIN users u ON u.id = f.friend_id
WHERE f.user_id = 1 -- Id of user you need friends for

-- Selecting friends posts
SELECT * FROM friends f
JOIN posts p ON p.user_id = f.friend_id
WHERE f.user_id = 1 -- Id of user you need friends' posts for

关于mysql - 跨表的JSON_CONTAINS ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42798755/