内容逐个用户：具有相同名称或连接列的两个表？ [英] Content-by-user: two tables with columns of the same name or join?

问题描述

标题几乎说明了一切。我有用户发布内容，我想将用户表中的用户标识附加到帖子。我应该在posts表中添加一个userid列，我应该通过DB语言加入，还是应该使用脚本语言轮询每个表？如果我要加入，我应该加入什么样的方式？

样本内容如下所示：我店内的泰迪熊比泰迪山羊柔软，但是泰迪山羊更可爱。 - 由詹姆斯·泰德曼在下午3:36发表。

解决方案

这种情况下经常使用的概念是通过用户表和一个表格，将它们与唯一的标识符相连。这个标识符可以是任何东西 - 序列化的id，用户名，邮件地址等 - 只要它是唯一的。链接使用外键约束完成。在MySQL中我完全不知道这样做，但是在Postgres中，这样做是这样的：

  CREATE TABLE users $ b id serial PRIMARY KEY，
 name text 
）; 
 
 CREATE TABLE posts（
 content text，
 user_id integer REFERENCES users（id）NOT NULL 
）; 
  

然后使用连接合并表。这可以通过几种方式完成，但是在插入一些值后，这里是一个交叉连接：

  @> INSERT INTO users（name）VALUES（'James'）; 
 @> INSERT INTO users（name）VALUES（'Jones'）; 
 @> INSERT INTO的帖子（content，user_id）VALUES（你好，从詹姆斯，1）; 
 @> INSERT INTO的帖子（content，user_id）VALUES（来自琼斯的问候，2）; 
 @> SELECT U.id AS user_id，U.name，P.content \ 
 FROM users U，posts P \ 
 WHERE U.id = P.user_id; 
 
 user_id |名称|内容
 --------- + ------- + ----------------------- 
 1 |詹姆斯|詹姆斯你好
 2 |琼斯|琼斯问候
  

MySQL中的YMMV，但我认为上述结构将直接工作。

（编辑：添加INSERT以进行澄清）

The title pretty much says it all. I have users posting content, and I would like to attach the user ID that is in the users table to the posts. Should I add a userid column in the posts table, should I join via the DB language, or should I use the scripting language to poll each table? If I should join, what kind of join should I do?

Sample content would look like this: "The teddy bears at my store are softer than the teddy goats, but the teddy goats are more lovable." - Posted by James Teddyman at 3:36 PM.

解决方案

A very often-used concept in situation like this is by having a users table and a post table, linking them together with a unique identifier. This identifier can be anything - a serialized id, a user name, mail address, etc - as long as it's unique. Linking is done using a foreign key constraint. Exactly how this is achieved in MySQL I do not know, but in Postgres it's done like this:

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text
);

CREATE TABLE posts (
  content text,
  user_id integer REFERENCES users(id) NOT NULL
);

The tables are then merged using a join. This can be done in several ways, but here is a cross join after insertion of some values to play with:

@> INSERT INTO users (name) VALUES ('James');
@> INSERT INTO users (name) VALUES ('Jones');
@> INSERT INTO posts (content, user_id) VALUES ('Hello from James.', 1);
@> INSERT INTO posts (content, user_id) VALUES ('Greetings from Jones.', 2);
@> SELECT U.id AS user_id, U.name, P.content \
   FROM users U, posts P \
   WHERE U.id = P.user_id;

 user_id | name  |        content
---------+-------+-----------------------
       1 | James | Hello from James.
       2 | Jones | Greetings from Jones.

YMMV in MySQL, but I think the constructions above will work straight off.

(edit: Added INSERTs for clarification)

这篇关于内容逐个用户：具有相同名称或连接列的两个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！