以下两个表不受任何类型的约束。
首先，我有一个名为subscription_plans的表，如下所示：
name | price | ID
-------------------
plan_A | 9.99 | 1
Plan_B | 19.99 | 2
plan_C | 29.99 | 3
我有第二个表，称为
pricing_offers。 subscription_plan_ID是SET类型的，并且只能包含与subscription_plans.ID的ID匹配的值（来自上表的列）。该表如下所示：p_o_name | subscription_plan_ID | ID
-----------------------------------------
free donuts | 1 | 1
extra sauce | 1,2,3 | 2
pony ride | 3 | 3
bus fare -50% | 1,2,3 | 4
我试图做一个查询，以选择第一个表中的所有内容（所有字段*）和第二个表中的所有名称，结果行应如下所示：
name | price | p_o_name | ID
-------------------------------------------------------------
plan_A | 9.99 | free donuts, extra sauce, bus fare -50% | 1
Plan_B | 19.99 | extra_sauce, bus fare -50% | 2
plan_C | 29.99 | extra_sauce, pony ride, bus fare -50% | 3
这样的想法是，对于
subscription_plans表中的每一行，都应查看ID字段。然后通过第二张表，查看上一行的subscription_plan_ID和ID中包含哪些行。将它们收集到字段调用器p_o_name中，并将其值插入匹配的响应行。我尝试这样做：
SELECT subscription_plans.*, pricing_offers.name
FROM subscription_plans INNER JOIN pricing_offers ON
FIND_IN_SET(subscription_plans.ID,subscription_plan_ID)
但是我得到的不是：
plan_A | 9.99 | free donuts, extra sauce, bus fare -50% | 1
这个：
plan_A | 9.99 | free donuts | 1
plan_A | 9.99 | extra sauce | 1
plan_A | 9.99 | bus fare -50% | 1
注意：我得到所有行的响应，但是我只是将第一个放在此处以举例说明差异。
现在，尽管我可以在PHP页面上的响应中进行处理，但是我想知道是否可以让数据库引擎输出所需的结果。
我需要在表之间创建约束类型吗？如果是这样，我该怎么办？我将不胜感激，希望能帮助我达到预期的输出结果（甚至是该问题的更好标题！）。
如果有任何不清楚的地方，请告诉我，我将予以澄清。
最佳答案
接合/相交表用法示例。
create table subscription_plans
(
id int not null auto_increment primary key, -- common practice
name varchar(40) not null,
description varchar(255) not null,
price decimal(12,2) not null
-- additional indexes:
);
create table pricing_offers
(
id int not null auto_increment primary key, -- common practice
name varchar(40) not null,
description varchar(255) not null
-- additional indexes:
);
create table so_junction
( -- intersects mapping subscription_plans and pricing_offers
id int not null auto_increment primary key, -- common practice
subId int not null,
offerId int not null,
-- row cannot be inserted/updated if subId does not exist in parent table
-- the fk name is completely made up
-- parent row cannot be deleted and thus orphaning children
CONSTRAINT fk_soj_subplans
FOREIGN KEY (subId)
REFERENCES subscription_plans(id),
-- row cannot be inserted/updated if offerId does not exist in parent table
-- the fk name is completely made up
-- parent row cannot be deleted and thus orphaning children
CONSTRAINT fk_soj_priceoffer
FOREIGN KEY (offerId)
REFERENCES pricing_offers(id),
-- the below allows for only ONE combo of subId,offerId
CONSTRAINT soj_unique_ids unique (subId,offerId)
-- additional indexes:
);
insert into subscription_plans (name,description,price) values ('plan_A','description',9.99);
insert into subscription_plans (name,description,price) values ('plan_B','description',19.99);
insert into subscription_plans (name,description,price) values ('plan_C','description',29.99);
select * from subscription_plans;
insert into pricing_offers (name,description) values ('free donuts','you get free donuts, limit 3');
insert into pricing_offers (name,description) values ('extra sauce','extra sauce');
insert into pricing_offers (name,description) values ('poney ride','Free ride on Wilbur');
insert into pricing_offers (name,description) values ('bus fare -50%','domestic less 50');
select * from pricing_offers;
insert so_junction(subId,offerId) values (1,1); -- free donuts to plans
insert so_junction(subId,offerId) values (1,2),(2,2),(3,2); -- extra sauce to plans
insert so_junction(subId,offerId) values (3,3); -- wilbur
insert so_junction(subId,offerId) values (1,4),(2,4),(3,4); -- bus to plans
select * from so_junction;
-- try to add another of like above to so_junction
-- Error Code 1062: Duplicate entry
-- show joins of all
select s.*,p.*
from subscription_plans s
join so_junction so
on so.subId=s.id
join pricing_offers p
on p.id=so.offerId
order by s.name,p.name
-- show extra sauce intersects
select s.*,p.*
from subscription_plans s
join so_junction so
on so.subId=s.id
join pricing_offers p
on p.id=so.offerId
where p.name='extra sauce'
order by s.name,p.name
基本上，您可以从联结表中插入和删除（在此示例中，确实没有很好的更新过）。
干净和快速的连接，而不必弄乱没有索引的缓慢而笨拙的集
没有人可以再骑Wilbur the Poney吗？然后
delete from so_junction
where offerId in (select id from pricing_offers where name='poney ride')
询问您是否有任何问题。
还有祝你好运！