没有ID的MySQL数据库关系

Hi * community,

您好*社区,

I have these two tables:

我有这两个表:

tbl_users

• ID_user (PRIMARY KEY)

ID_user(PRIMARY KEY)

• Username (UNIQUE)
• Password
• ...

tbl_posts

• ID_post (PRIMARY KEY)

ID_post(PRIMARY KEY)

• Owner (UNIQUE)
• Description
• ...

Why always everybody make database relationships with foreign keys? What about if I want to relate Username with Owner instead of doing ID_user with ID_user in both tables?

为什么每个人都总是与外键建立数据库关系?如果我想将Username与Owner相关联,而不是在两个表中使用ID_user进行ID_user,那该怎么办?

Username is UNIQUE and the Owner is the username of the creator of the post.

用户名是UNIQUE,所有者是帖子创建者的用户名。

Can it be done like that? There is something to correct or make better? Maybe I have a misconception.

可以这样做吗?有什么可以纠正或改善?也许我有一个误解。

I would appreciate detailed and understandable answers.

我会很感激详细和可理解的答案。

Thank you in advance.

先感谢您。

1 个解决方案

#1

The reason is primarily for data integrity. The argument concerning performance is a little misleading. While neither exhaustive, nor definitive, I hope this little example will shed some light on that fact:

原因主要是数据完整性。关于绩效的论点有点误导。虽然既不详尽也不确定,但我希望这个小例子能够揭示这一事实:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(i INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,s CHAR(12) NOT NULL UNIQUE
);

STEP1:

INSERT IGNORE INTO my_table (s) 
SELECT CONCAT(CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97)
             ,CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97)
             );

STEP2:

INSERT IGNORE INTO my_table (s) 
SELECT CONCAT(CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97)
             ,CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97),CHAR((RAND()*26)+97)
             )
  FROM my_table;

[REPEAT STEP 2 SEVERAL TIMES]

SELECT COUNT(*) FROM my_table;
  +----------+
  | COUNT(*) |
  +----------+
  |    16384 |
  +----------+
  1 row in set (0.01 sec)

 SELECT * FROM my_table ORDER BY i LIMIT 12;;
  +----+------------+
  | i  | s          |
  +----+------------+
  |  1 | kkxeehxsvy |
  |  2 | iuyhrk{vaq |
  |  3 | ngpedelooc |
  |  4 | irkbyqgkhc |
  |  6 | yqkcifcxdz |
  |  7 | sgezlgvjjq |
  |  8 | blavbvxbnl |
  |  9 | wdbtqvgvgt |
  | 13 | pakzpbnhxr |
  | 14 | vpoy{gdwyd |
  | 15 | ezlhz{drwg |
  | 16 | ncwcwbpudh |
  +----+------------+

  SELECT * FROM my_table x JOIN my_table y ON y.i < x.i ORDER BY x.i,y.i LIMIT 1;
  +---+------------+---+------------+
  | i | s          | i | s          |
  +---+------------+---+------------+
  | 2 | iuyhrk{vaq | 1 | kkxeehxsvy |
  +---+------------+---+------------+
  1 row in set (1 min 22.60 sec)

 SELECT * FROM my_table x JOIN my_table y ON y.s < x.s ORDER BY x.s,y.s LIMIT 1;
  +-------+------------+------+------------+
  | i     | s          | i    | s          |
  +-------+------------+------+------------+
  | 21452 | aabetdlvum | 6072 | aabdnegtav |
  +-------+------------+------+------------+
  1 row in set (1 min 13.59 sec)

So, we have two queries doing essentially the same thing (a comparison of 270 million values). The first joins the table to itself on an integer value. The second joins the table to itself on a string value. Both columns are indexed. As you can see, in this example, the string join actually performs better than the integer join - even though the hit on the CPU may actually be greater!

因此,我们有两个查询基本上做同样的事情(比较2.7亿个值)。第一个将表连接到整数值上。第二个将表连接到字符串值。两列都已编入索引。正如您所看到的,在此示例中,字符串连接实际上比整数连接执行得更好 - 即使CPU上的命中实际上可能更大!