对于sql来说，假设这是在两个主要实体之间创建连接表的正确方法，那么是否必须硬编码插入连接表的数据？如何从联接表查询？我正在使用sql fiddle，所以我不确定是否正确地生成了指向外键的链接。
CREATE TABLE Organization(
`Organization_id` int NOT NULL,
PRIMARY KEY(`Organization_id`)
);
CREATE TABLE QuestionBank(
`Question_id` int NOT NULL,
`Question_text` VARCHAR(255) NOT NULL,
PRIMARY KEY(`Question_id`)
);
CREATE TABLE OrganizationQuestion(
`OrganizationQuestion_id` int NOT NULL,
`Question_id` int NOT NULL,
`Organization_id` int NOT NULL,
PRIMARY KEY(`OrganizationQuestion_id`),
FOREIGN KEY(`Question_id`) REFERENCES QuestionBank(`Question_id`),
FOREIGN KEY(`Organization_id`) REFERENCES Organization(`Organization_id`)
);
INSERT INTO Organization(`Organization_id`) VALUES(1);
INSERT INTO QuestionBank(`Question_id`, `Question_text`) VALUES(1, 'How did he perform?');
INSERT INTO OrganizationQuestion(`OrganizationQuestion_id`, `Question_id`, `Organization_id`)
VALUES(1, 1, 1);
最佳答案
这是您的加入：
select oq.OrganizationQuestion_id,
oq.Question_id,
oq.Organization_id,
o.Organization_id,
qb.Question_id,
qb.Question_text
from OrganizationQuestion oq
join Organization o
on o.Organization_id = oq.Organization_id
join QuestionBank qb
on qb.Question_id = oq.Question_id
+-------------------------+-------------+-----------------+-----------------+-------------+---------------------+
| OrganizationQuestion_id | Question_id | Organization_id | Organization_id | Question_id | Question_text |
+-------------------------+-------------+-----------------+-----------------+-------------+---------------------+
| 1 | 1 | 1 | 1 | 1 | How did he perform? |
+-------------------------+-------------+-----------------+-----------------+-------------+---------------------+
这不是很有趣，因为你几乎所有的东西都是1。
输出和解释：
+----+-------------+-------+--------+-----------------------------+---------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------+---------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | oq | ALL | Question_id,Organization_id | NULL | NULL | NULL | 1 | NULL |
| 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 4 | so_gibberish.oq.Organization_id | 1 | Using index |
| 1 | SIMPLE | qb | eq_ref | PRIMARY | PRIMARY | 4 | so_gibberish.oq.Question_id | 1 | NULL |
+----+-------------+-------+--------+-----------------------------+---------+---------+---------------------------------+------+-------------+
请记住，键（索引）不用于带有小表的查询。使用这些键比仅仅扫描表要花更长的时间。
要查看表上的索引，请执行以下操作：
mysql> show indexes from OrganizationQuestion;
+----------------------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| organizationquestion | 0 | PRIMARY | 1 | OrganizationQuestion_id | A | 1 | NULL | NULL | | BTREE | | |
| organizationquestion | 1 | Question_id | 1 | Question_id | A | 1 | NULL | NULL | | BTREE | | |
| organizationquestion | 1 | Organization_id | 1 | Organization_id | A | 1 | NULL | NULL | | BTREE | | |
+----------------------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
有关
SHOW INDEX和EXPLAIN编辑2个完全不同的问题
在插入期间不允许内容（例如，一个表中的两个FK ID是相同的，例如邮件“发件人”和“收件人”）
有关生成
signal sqlstate '45000';
关于mysql - 如何从联接表中查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39337641/