在mysql中创建关系表

你有没有考虑过这样的事情？ （假设至少对词汇表有一个独特的约束。名称。）

    
      create table vocabulary_relations (
  vocabulary_category varchar(35) not null,  — references vocabulary (name),
  related_vocabulary varchar(35) not null,   — references vocabulary (name),
  primary key (vocabulary_category, related_vocabulary)
);
insert into vocabulary_relations values
(‘Interactive Design’,’Graphic Design’),
(‘Interactive Design’,’Design’),
(‘Farming’,’Agriculture’),
(‘Farming’,’Animal Husbandry’);
</code>
  

    
      select *
from vocabulary_relations
order by vocabulary_category, related_vocabulary;
vocabulary_category    related_vocabulary
Farming                Agriculture
Farming                Animal Husbandry
Interactive Design     Design
Interactive Design     Graphic Design
</code>
  

    
      INSERT INTO vocabulary_relations
    (vocabulary_id, related_id)
    SELECT v1.id, v2.id
        FROM RelatedItems ri
            INNER JOIN vocabulary v1
                ON ri.col1 = v1.name
            INNER JOIN vocabulary v2
                ON ri.col2 = v2.name
</code>